Skip to content
  • Categories
  • Recent
  • Tags
  • Popular
  • Users
  • Groups
  • Search
  • Get Qt Extensions
  • Unsolved
Collapse
Brand Logo
  1. Home
  2. Qt Development
  3. QML and Qt Quick
  4. How to insert Qxlsx data into sqlite database table?
QtWS25 Last Chance

How to insert Qxlsx data into sqlite database table?

Scheduled Pinned Locked Moved Solved QML and Qt Quick
qml qxlsx datdatabasesqlite databaseqsqldatabase
16 Posts 3 Posters 2.0k Views
  • 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
    14 Oct 2021, 06:11

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

    J Offline
    J Offline
    jsulm
    Lifetime Qt Champion
    wrote on 14 Oct 2021, 06:46 last edited by
    #4

    @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 14 Oct 2021, 06:52
    0
    • J jsulm
      14 Oct 2021, 06:46

      @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")?

      J Offline
      J Offline
      Joshika_Namani
      wrote on 14 Oct 2021, 06:52 last edited by
      #5

      @jsulm
      yes

      J 1 Reply Last reply 14 Oct 2021, 06:55
      0
      • J Joshika_Namani
        14 Oct 2021, 06:52

        @jsulm
        yes

        J Offline
        J Offline
        jsulm
        Lifetime Qt Champion
        wrote on 14 Oct 2021, 06:55 last edited by
        #6

        @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
        0
        • J Offline
          J Offline
          Joshika_Namani
          wrote on 14 Oct 2021, 07:17 last edited by
          #7

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

          1 Reply Last reply
          0
          • J Joshika_Namani
            14 Oct 2021, 04:13

            #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";
                    }
                }
            
            
                }
            

            }
            }

            J Offline
            J Offline
            JonB
            wrote on 14 Oct 2021, 07:47 last edited by JonB
            #8

            @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?

            J J 2 Replies Last reply 14 Oct 2021, 07:53
            3
            • J JonB
              14 Oct 2021, 07:47

              @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?

              J Offline
              J Offline
              jsulm
              Lifetime Qt Champion
              wrote on 14 Oct 2021, 07:53 last edited by
              #9

              @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

              J 1 Reply Last reply 14 Oct 2021, 07:58
              0
              • J jsulm
                14 Oct 2021, 07:53

                @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

                J Offline
                J Offline
                JonB
                wrote on 14 Oct 2021, 07:58 last edited by
                #10

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

                J 1 Reply Last reply 14 Oct 2021, 08:00
                0
                • J JonB
                  14 Oct 2021, 07:58

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

                  J Offline
                  J Offline
                  jsulm
                  Lifetime Qt Champion
                  wrote on 14 Oct 2021, 08:00 last edited by
                  #11

                  @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
                  0
                  • J JonB
                    14 Oct 2021, 07:47

                    @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?

                    J Offline
                    J Offline
                    Joshika_Namani
                    wrote on 18 Oct 2021, 07:07 last edited by
                    #12

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

                    J 1 Reply Last reply 18 Oct 2021, 07:13
                    0
                    • J Joshika_Namani
                      18 Oct 2021, 07:07

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

                      J Offline
                      J Offline
                      JonB
                      wrote on 18 Oct 2021, 07:13 last edited by
                      #13

                      @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 18 Oct 2021, 08:32
                      0
                      • J JonB
                        18 Oct 2021, 07:13

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

                        i modified everything here

                        In that case show new code! :)

                        J Offline
                        J Offline
                        Joshika_Namani
                        wrote on 18 Oct 2021, 08:32 last edited by
                        #14

                        @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";
                        }
                        

                        }
                        }

                        J 1 Reply Last reply 18 Oct 2021, 10:32
                        0
                        • J Joshika_Namani
                          18 Oct 2021, 08:32

                          @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";
                          }
                          

                          }
                          }

                          J Offline
                          J Offline
                          JonB
                          wrote on 18 Oct 2021, 10:32 last edited by
                          #15

                          @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 19 Oct 2021, 05:05
                          2
                          • J JonB
                            18 Oct 2021, 10:32

                            @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 Offline
                            J Offline
                            Joshika_Namani
                            wrote on 19 Oct 2021, 05:05 last edited by
                            #16

                            @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
                            0

                            13/16

                            18 Oct 2021, 07:13

                            • Login

                            • Login or register to search.
                            13 out of 16
                            • First post
                              13/16
                              Last post
                            0
                            • Categories
                            • Recent
                            • Tags
                            • Popular
                            • Users
                            • Groups
                            • Search
                            • Get Qt Extensions
                            • Unsolved