How To transform Excel, CSV ,Sqlite



  • What I want to do is that modify the data by Excel and use data in QT by sqlite.
    I didn't find the way transform excel to sqlite(or save excel as sqlite).
    and now I have transformed Excel to CSV,but I didn't find the way to transform CSV to sqlite.
    Has anyone have some tips about this QT program. Any tips will help.
    Thanks a lot .



  • For an analogy:

    • Excel = car
    • CSV = bike
    • sqlite = truck

    They all have wheels but they are not straight convertable from one to the other. What exactly are you trying to do? putting an excel table as a new table in the sqlite db?



  • @pyuxing said in How To transform Excel, CSV ,Sqlite:

    Has anyone have some tips about this QT program

    Although I'm a big fan of people using Qt everywhere, there would be some use cases where it won't be the ideal tool. Are you sure you cannot do the Excel -> sqlite conversion/data handling in other (more direct) way not using a Qt application? What about using SQLite ODBC driver?



  • Yes you can.
    Please remember that csv can manage only one sheet (one table). If you want you can write some lines of code to read a csv file and to create a sqlite table with the data.
    To manage a xls file you can use QSqlDatabase::addDatabase("QODBC");
    or ADO interface.



  • @VRonin
    Thanks for your reply.
    Yeah,I just want import the excel table into sqlite db,so any tips for this?



  • You have to write code.
    If you need professional support please send me an email.
    You can find my email in the signature company website.



  • @mrdebug
    So I have to try to use "ODBC"?


  • Qt Champions 2017

    Hi
    If you need to export multiple sheets in one go, CSV is not a good export format as it has
    no sheet concept. So minimum is you would have to export the sheets to individual files
    and import each one to the database system.



  • @pyuxing if you have the CSV file at hand you can import it directly to an sqlite table using sqlite3 tool, see this tutorial. As stated before, if your Excel file has several sheets you need to repeat the process per worksheet (you'll end up with several tables in sqlite db).

    Other option is to connect the Excel worksheet directly to the sqlite db via an ODBC driver.

    Last option, and more complex and laborious is to create an Qt application that read the CSV file, and assuming it has first line as header names, creates an sqlite table and then reading line by line inserts the data as rows in the sqlite table

    And as summary of my post, this issue you have seems to be not related to Qt framework at all.



  • @Pablo-J.-Rogina
    connecting the Excel worksheet directly to the sqlite db via an ODBC driver maybe the best choice ,I`ll have a try.Thanks!



  • @pyuxing You'll probably find this wiki interesting: https://wiki.qt.io/Handling_Microsoft_Excel_file_format


Log in to reply
 

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