extract data from excel file using QODBC



  • 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 ?



  • 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



  • 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.


Log in to reply
 

Looks like your connection to Qt Forum was lost, please wait while we try to reconnect.