Important: Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

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