extract data from excel file using QODBC

AmrCoder

I use this code to extract data from excel file

void MainWindow::connectToExcel()
{
    QString excelFilePath = "D:/a.xls";

    dp = QSqlDatabase::addDatabase("QODBC");
    // set the dsn string
    QString dsn = QString("DRIVER={Microsoft Excel Driver (*.xls)};DSN='';FIRSTROWHASNAMES=1;READONLY=TRUE;DBQ=%1").
                  arg(excelFilePath);
    dp.setDatabaseName(dsn);
    if(!dp.open())
    {
        qDebug() << "Error " << dp.lastError().text() << endl;
        return;
    }
    QSqlQuery q;
    q.prepare("SELECT * FROM [test$a1:B]");
    if (!q.exec()){
        qDebug() << "Error = " << q.lastError().text();
        return;
    }
    while (q.next()){
        qDebug() << q.value(0).toString() << " " << q.value(1).toString();
    }
}

it works fine with .xls but if i change it to use it on an excel file with this extension .xlsx it didn't work and give me this error

"[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified QODBC3: Unable to connect"

this there is a solution for this problem ?

hskoglund

Perhaps you need to update Microsoft's ODBC driver for Excel, the old one only supports .xls files, you can check if you start ODBC Admin. More discussion here

kenchan

your driver string should probably be (Microsoft Excel Driver (*.xls , *.xlsx, *.xlsm, *.xlsb) or what ever you see in your windows ODBC driver gui when you run odbcad32.exe for the architecture you want to use.