Qt Forum

    • Login
    • Search
    • Categories
    • Recent
    • Tags
    • Popular
    • Users
    • Groups
    • Search
    • Unsolved

    Solved How to insert Qxlsx data into sqlite database table?

    QML and Qt Quick
    qml qxlsx dat database sqlite database qsqldatabase
    3
    16
    678
    Loading More Posts
    • Oldest to Newest
    • Newest to Oldest
    • Most Votes
    Reply
    • Reply as topic
    Log in to reply
    This topic has been deleted. Only users with topic management privileges can see it.
    • J
      Joshika_Namani last edited by

      #include "XlsxDAO.h"
      #include "mainwindow.h"
      #include "DatabaseDAO.h"
      #include <vector>
      using namespace QXlsx;
      void XlsxDAO::read()
      {

      QXlsx::Document xlsx;
      
      qInfo()<<"hai";
      QSqlDatabase sqlite;
      if(QSqlDatabase::contains("qt_sql_default_connection"))
        sqlite = QSqlDatabase::database("qt_sql_default_connection");
      else
        sqlite = QSqlDatabase::addDatabase("QSQLITE");
      
      sqlite.setDatabaseName("/home/acuvate/database/users");
      Cell* cell;
      int col=1;
      

      std::vector<QVariant> vec;

      if(xlsx.load())
      {
      qInfo()<<"hai-1";
      for(int row=1;row<=1000;row++)
      {
      qInfo()<<"hai-2";

          cell=xlsx.cellAt(row,col);
        // Storing cell value into vector
          QVariant var=cell->readValue();
          vec.push_back(var);
         }
      for(int i=0;i<=1000;i++)
        {
          //displaying vector values
              qInfo()<<vec[i];
         }
      if(sqlite.open())
          {
          QSqlQuery query;
          for(int i=0;i<vec.size();i++)
          {
              query.prepare("insert into Dataset(AT) values(?)");
              query.addBindValue(vec[1]);
              if(!query.exec())
              {
                  qInfo()<<"inserted";
              }
          }
      
      
          }
      

      }
      }

      jsulm JonB 2 Replies Last reply Reply Quote 0
      • jsulm
        jsulm Lifetime Qt Champion @Joshika_Namani last edited by jsulm

        @Joshika_Namani Please format your code properly.
        What exactly does not work?
        You do not have any error handling (hint: QSqlQuery::exec returns a bool and there is https://doc.qt.io/qt-5.15/qsqlquery.html#lastError).

        https://forum.qt.io/topic/113070/qt-code-of-conduct

        1 Reply Last reply Reply Quote 0
        • J
          Joshika_Namani last edited by

          Thanks for your reply, It's executing with out any errors but table is empty.

          jsulm 1 Reply Last reply Reply Quote 0
          • jsulm
            jsulm Lifetime Qt Champion @Joshika_Namani last edited by

            @Joshika_Namani said in How to insert Qxlsx data into sqlite database table?:

            It's executing with out any errors

            So, you see "inserted" printed (from qInfo()<<"inserted")?

            https://forum.qt.io/topic/113070/qt-code-of-conduct

            J 1 Reply Last reply Reply Quote 0
            • J
              Joshika_Namani @jsulm last edited by

              @jsulm
              yes

              jsulm 1 Reply Last reply Reply Quote 0
              • jsulm
                jsulm Lifetime Qt Champion @Joshika_Namani last edited by

                @Joshika_Namani How did you check that the table is empty?
                I also don't see where you're closing your database.

                https://forum.qt.io/topic/113070/qt-code-of-conduct

                1 Reply Last reply Reply Quote 0
                • J
                  Joshika_Namani last edited by

                  I have used sqlite browser its gui database.
                  Yes, i didn't close the database.

                  1 Reply Last reply Reply Quote 0
                  • JonB
                    JonB @Joshika_Namani last edited by JonB

                    @Joshika_Namani said in How to insert Qxlsx data into sqlite database table?:

                        if(!query.exec())
                        {
                            qInfo()<<"inserted";
                        }
                    

                    Somebody here is crazy. You said

                    Thanks for your reply, It's executing with out any errors but table is empty.

                    So, you see "inserted" printed (from qInfo()<<"inserted")?

                    yes

                    You are printing inserted if query.exec() fails. Why??

                    insert into Dataset(AT) values(?)

                    I know nothing about SQLite. Dataset(AT) is a (syntactically valid) table to insert into, is it? In any case you have QSqlQuery::lastError(). Also QSqlQuery::prepare() returns a bool for success/failure, which you should be checking.

                    Separate issue when you have that working:

                    query.addBindValue(vec[1]);

                    You always want to re-insert vec[1], not vec[i], right?

                    jsulm J 2 Replies Last reply Reply Quote 3
                    • jsulm
                      jsulm Lifetime Qt Champion @JonB last edited by

                      @JonB said in How to insert Qxlsx data into sqlite database table?:

                      Somebody here is crazy

                      Oh, I guess I'm blind or crazy or both :-D

                      https://forum.qt.io/topic/113070/qt-code-of-conduct

                      JonB 1 Reply Last reply Reply Quote 0
                      • JonB
                        JonB @jsulm last edited by

                        @jsulm I didn't mean you... :)

                        jsulm 1 Reply Last reply Reply Quote 0
                        • jsulm
                          jsulm Lifetime Qt Champion @JonB last edited by

                          @JonB I know :-) But I really should have seen the wrong if condition.

                          https://forum.qt.io/topic/113070/qt-code-of-conduct

                          1 Reply Last reply Reply Quote 0
                          • J
                            Joshika_Namani @JonB last edited by

                            @JonB
                            Sorry for that i modified everything here I'm getting an error like QSqlError("", "", "") by using lastError() method.

                            JonB 1 Reply Last reply Reply Quote 0
                            • JonB
                              JonB @Joshika_Namani last edited by

                              @Joshika_Namani said in How to insert Qxlsx data into sqlite database table?:

                              i modified everything here

                              In that case show new code! :)

                              J 1 Reply Last reply Reply Quote 0
                              • J
                                Joshika_Namani @JonB last edited by

                                @JonB
                                QXlsx::Document xlsx("Test.xlsx");
                                QSqlDatabase sqlite;
                                sqlite = QSqlDatabase::addDatabase("QSQLITE");

                                sqlite.setDatabaseName("/home/Qt Program/DB/Database");
                                Cell* cell;
                                int col=1;
                                

                                std::vector<QVariant> vec;

                                if(xlsx.load())
                                {
                                for(int row=1;row<=20;row++)
                                {

                                    cell=xlsx.cellAt(row,col);
                                    QVariant var=cell->readValue();
                                    vec.push_back(var);
                                   }
                                for(int i=0;i<=20;i++)
                                  {
                                        qInfo()<<vec[i];
                                   }
                                if(sqlite.open())
                                    {
                                 
                                    QSqlQuery query;
                                    for(int i=0;i<vec.size();i++)
                                    {
                                        
                                        query.prepare("insert into Data(AT) values(?)");
                                        query.addBindValue(vec[i]);
                                        if(query.exec())
                                        {
                                            qInfo()<<"inserted";
                                        }
                                        else
                                        {
                                            qInfo()<<sqlite.lastError();
                                        }
                                    }
                                
                                
                                    }
                                else
                                {
                                    qInfo()<<"not opened";
                                }
                                

                                }
                                }

                                JonB 1 Reply Last reply Reply Quote 0
                                • JonB
                                  JonB @Joshika_Namani last edited by

                                  @Joshika_Namani
                                  You have not answered or acted on what I already wrote. Please don't ignore suggestions and make responders type the same thing again. I already wrote:

                                  • insert into Dataset(AT) values(?): I know nothing about SQLite. Dataset(AT) is a (syntactically valid) table to insert into, is it?

                                  • Also QSqlQuery::prepare() returns a bool for success/failure, which you should be checking.

                                  • Plus, you check QSqlDatabase::lastError() after QSqlQuery::exec() fails, but that says

                                  Failures that occur in conjunction with an individual query are reported by QSqlQuery::lastError().

                                  which you should be checking instead/as well.

                                  Please read the documentation for the functions you call, and check all errors before asking us, it saves you & us time.

                                  J 1 Reply Last reply Reply Quote 2
                                  • J
                                    Joshika_Namani @JonB last edited by

                                    @JonB
                                    sorry for that,

                                    • insert into Dataset(AT) values(?): I know nothing about SQLite. Dataset(AT) is a (syntactically valid) table to insert into, is it?
                                      Yes, it is valid statement in sqlite.

                                    • Also QSqlQuery::prepare() returns a bool for success/failure, which you should be checking.
                                      Returns true if the query is prepared successfully; otherwise returns false.

                                    • Plus, you check QSqlDatabase::lastError() after QSqlQuery::exec() fails, but that says
                                      yes, I got error. like QSqlError("","","");

                                    I checked again there was a problem in database but it solved thanks alot.
                                    Now, I'm able to insert data into database from QXlsx file.

                                    1 Reply Last reply Reply Quote 0
                                    • First post
                                      Last post