Need help with importing .csv file into sqlite database with qt c++

[rp123]

How to Import .csv file into sqlite database programmatically using qt c++ ? Can anyone share the code ??

[J.Hilk]

@rp123
take look at his wiki entry
https://wiki.qt.io/Handling_Microsoft_Excel_file_format

and this solution from stackoverlfow
https://stackoverflow.com/a/51721032

should get you working in the right direction.

If you don't have an excel file or don't want to go that way,
Use QFile to open the file and read the context and split(';')

to extract the data and the code from the stackoverflow link to insert it into your sqlite

[rp123]

Thanks JH, but I need to import a .csv or Excel file wherein i don't know the column names or the number of columns. Any idea.. ?

[JonB]

@rp123

wherein i don't know the column names or the number of columns

In https://wiki.qt.io/Handling_Microsoft_Excel_file_format, the very first example I look at using select * from [" + QString("Sheet1") + "$] clearly works where you don't know anything about the number, names or types of the columns, just as you ask for. You will also have noted it states and discusses there:

By default OBDC uses the first row as names for the columns

And similarly if you use @J-Hilk's

Use QFile to open the file and read the context and split(';')

That doesn't need to know number or names of columns either.

[rp123]

@JonB and @J-Hilk
Thanks to both of you, I am now able to successfully import .csv file into sqlite table/database using qt c++.
But now, I am facing another sub problem of importing .xls or .xlsx file. I am unable to get the first row for column names from the excel sheet ( I need to get the columns to create headers for sqlite table). My code shows the second row columns in the debug.
I am sharing my basic code here, please let me know modifications, if any::

    QSqlDatabase XL_db = QSqlDatabase::addDatabase("QODBC", "xlsx_connection");
    XL_db.setDatabaseName(QString("DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=%1"). arg(filepath));

    if(XL_db.open())
    {
        int i,j,k=0,cont;
        QString ex1, ex2;
        QSqlQuery *query = new QSqlQuery(XL_db);
        query->exec("select * from ["+QString("Sheet1")+"$]");
        while (query->first())
        {
            if(k == 0)
            {
                for(i = 0; i < query->record().count(); ++i)
                {
                    ex1.push_back(query->value(i).toString().simplified());
                    ex1.push_back(" TEXT,");
                    ex2.push_back('?');
                    ex2.push_back(',');
                }
                cont = query->record().count();
                ex1.chop(1);
                ex2.chop(1);
                qDebug() << ex1 << endl;
                qDebug() << ex2 << endl;
            }
     }
}

Also, I have to specify the sheet name in the .setDatabaseName method. Is there any way around this to get excelsheet name programmatically.
Thanks for support.

[JonB]

@rp123
From my limited understanding: as per the link I posted, if you want to deal with the first row yourself you have to set FirstRowHasNames=0 in the connection string. However, apparently ODBC driver has always incorrectly ignored this, so you can't switch that behaviour off, and there's nothing you can do about it. That's all I know. Obviously worth a try just in case it does work....

Is there any way around this to get excelsheet name programmatically.

To do anything like that you would need to talk to Excel. From Qt that would be via Active Qt. There may be examples out there for working with Excel. If you go that route, you can probably deal with your first row issue. Also that link shows a number of potential third-party C/C++ libraries for parsing/writing Excel files, some of which are specifically for use with Qt.

[rp123]

@JonB Thanks again to JonB and J-Hilk. As you said the QODBC driver is having issue with FirstRowHasNames, I tried the method and failed. Next, I tried importing through Active Qt and was successful in importing excel file. So, I temporarily saved the imported excel file into .csv format and used earlier developed "import csv to sqlite database "method to save the contents in sqlite database through Qt C++.