Reading an Excel Spreadsheet into an SQL database using ODBC



  • This is my first time to post...

    I downloaded for Windows the latest version of QT today:
    Qt Creator 3.1.1 (opensource)
    Based on Qt 5.2.1 (MSVC 2010, 32 bit)

    I have a windows application where I want to read an Excel spreadsheet into a SQL database (or create a database from a spreadsheet) using ODBC. However I am getting an error. I was wondering if anyone here has been around the block on this and can help me out. Here are my .pro file entries for accessing the ODBC plugin:

    @
    QT += core gui
    sql

    QTPLUGIN += gjpeg
    qgif
    qkrcodecs
    qsqlodbc
    @

    Here is my source code for the window I am trying to create for this:

    @
    #include "spreadsheettodatabase.h"
    #include "ui_spreadsheettodatabase.h"

    #include <QApplication>
    #include <QtPlugin>

    #define QT_STATICPLUGIN 1
    #define QT_DEBUG_PLUGINS 1

    SpreadsheetToDatabase::SpreadsheetToDatabase(QWidget *parent) :
    QMainWindow(parent),
    ui(new Ui::SpreadsheetToDatabase)
    {

    ui->setupUi(this);
    QString pathString = "c:\\Qt\\Tools\\QtCreator\\bin\\SpreadsheetToDatabase\\CCS_Fault_CodesY.xls";
    
    QSqlDatabase db = QSqlDatabase::addDatabase("QODBC");
    db.setDatabaseName("DRIVER={Microsoft Excel Driver(*.xls)};DBQ=" + QString("CCS_Fault_CodesY.xls"));
    if (db.open ())
    {
        QSqlQuery query;
        query.exec &#40;"select Component, Condition from [CCS_Fault_Codes]"&#41;;
        while (query.next (&#41;)
        {
            QString ComponentStr = query.value(0).toString ();
            QString ConditionStr = query.value(1).toString ();
            QMessageBox::critical (0, ComponentStr, ConditionStr);
        }
    
    }
    else
    {
        QMessageBox::critical (0, QObject :: tr ("Database Error"), db.lastError().text());
        return;
    }
    

    @

    My db.open() statement fails, and here is the error I receive:

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

    Any ideas on what I am doing wrong??? I have tried using a path to the exact location of the spreadsheet, and I still got this same error, see QString define: pathString ….

    [edit added missing coding tags @ SGaist]



  • I forgot to mention that I do see the qsqlodbc.dll file in the folder with all of the other plugin files, just where QT places them by default.

    Also, I was following a supposed working example of how to do this, from this forum entry:

    http://qt-project.org/wiki/Handling_Microsoft_Excel_file_format



  • One last comment, in my main.cpp file, I have these lines of code:

    #include <QApplication>
    #include <QtPlugin>

    #define QT_STATICPLUGIN 1
    #define QT_DEBUG_PLUGINS 1

    Q_IMPORT_PLUGIN(qjpeg)
    Q_IMPORT_PLUGIN(qgif)
    Q_IMPORT_PLUGIN(qkrcodecs)
    Q_IMPORT_PLUGIN(qsqlodbc)

    However, whenever I do not comment out the Q_IMPORT_PLUGIN statements, I get a compile error for each of these plugins similar to this one:

    Undefined reference to qt_static_plugin_qjpet()

    I think not being able to execute these statements and having to comment them out is what is causing my db.open() failure.



  • Hi just guessing but try adding an explicit path to you Excel file, i.e. instead of
    @
    db.setDatabaseName(“DRIVER={Microsoft Excel Driver(.xls)};DBQ=” + QString(“CCS_Fault_CodesY.xls”)); @
    try
    @db.setDatabaseName(“DRIVER={Microsoft Excel Driver(
    .xls)};DBQ=” + pathString); @



  • hskoglund,

    Thank you for replying. As I mentioned I have already tried using an explicit path to my spreadsheet file, and got the same error. There are really two things going on here: 1) The excel spreadsheet file cannot be found, 2) The ODBC database file cannot be opened because the ODBC3 driver QT is looking for cannot be found.

    Thanks again for commenting!!

    jbomkamp



  • Hi, a bit tricky this one, but I managed to get a small Qt widget test app going:

    First I created a simple Excel 2003 book1.xls file, having just an "A" column with some text in about 10 rows. I placed the file in C:\Temp.

    Then an empty Widget app, inserted sql in the .pro file, and then changed mainwindow.cpp into this:
    @
    #include "mainwindow.h"
    #include "ui_mainwindow.h"
    #include "QSqlDatabase"
    #include "QSqlQuery"
    #include "QDebug"

    MainWindow::MainWindow(QWidget *parent) :
    QMainWindow(parent),
    ui(new Ui::MainWindow)
    {
    ui->setupUi(this);

    QSqlDatabase db = QSqlDatabase::addDatabase("QODBC");
    db.setDatabaseName("Driver={Microsoft Excel Driver (*.xls)};dbq=C:\\Temp\\Book1.xls");
    if (db.open())
    {
        QSqlQuery query;
        query.exec &#40;"select * from [sheet1$]"&#41;;
        while (query.next (&#41;)
        {
            qDebug() << query.value(0).toString();
        }
    }
    

    }

    MainWindow::~MainWindow()
    {
    delete ui;
    }
    @

    The rows of text are seen :-)

    Note about the C:\Temp\ directory in .setDatabaseName: you can skip it and just type '...dbq=Book1.xls", then if you launch the app from QtCreator it will look for the book1.xls file in the main build directory (where release and debug are subdirectories), and if you launch the app from a CMD window, it will look for the book1.xls file in the same directory as the .exe file.

    (EDIT: forget to mention, the text in the first row isn't shown, because ODBC thinks it's a fieldname.)



  • hskoglund,

    Thank you for your effort reply. I noticed the only difference about the things that worked for you and the code that I have written and attempted to debug would be the case of the text of "Driver" and "dbq" in your setDatabaseName method. I tried making that change and my code received the same error.

    Yesterday, I took known working sqlite code created for Linux and attempted to get it to run from this Windows QT version, and it also failed to find the sqlite driver, and could not work. My conclusion after all of this is that the version of QT that I downloaded is faulty, either in the compiler itself, or the plugins. I have read notes from people saying that they had downloaded versions of QT in which the plugins that were included were built with a different version of the compiler than what they downloaded, and the plugins didn't work. That is most likely what is the problem, I surmise. Because this effort has taken so long for me already, yesterday we decided upon a different solution than this one here for our database needs at this moment. I already implemented that solution. So, for now this attempt is going to the scrap heap. Thanks again for your help!!

    Jbomkamp


Log in to reply
 

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