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?
Forum Updated to NodeBB v4.3 + New Features

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.1k 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 Offline
    J Offline
    Joshika_Namani
    wrote on last edited by
    #1

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

    }
    }

    jsulmJ JonBJ 2 Replies Last reply
    0
    • J Joshika_Namani

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

      }
      }

      jsulmJ Offline
      jsulmJ Offline
      jsulm
      Lifetime Qt Champion
      wrote on last edited by jsulm
      #2

      @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
      0
      • J Offline
        J Offline
        Joshika_Namani
        wrote on last edited by
        #3

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

        jsulmJ 1 Reply Last reply
        0
        • J Joshika_Namani

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

          jsulmJ Offline
          jsulmJ Offline
          jsulm
          Lifetime Qt Champion
          wrote on 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
          0
          • jsulmJ jsulm

            @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 last edited by
            #5

            @jsulm
            yes

            jsulmJ 1 Reply Last reply
            0
            • J Joshika_Namani

              @jsulm
              yes

              jsulmJ Offline
              jsulmJ Offline
              jsulm
              Lifetime Qt Champion
              wrote on 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 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

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

                  }
                  }

                  JonBJ Offline
                  JonBJ Offline
                  JonB
                  wrote on 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?

                  jsulmJ J 2 Replies Last reply
                  3
                  • JonBJ 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?

                    jsulmJ Offline
                    jsulmJ Offline
                    jsulm
                    Lifetime Qt Champion
                    wrote on 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

                    JonBJ 1 Reply Last reply
                    0
                    • jsulmJ jsulm

                      @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

                      JonBJ Offline
                      JonBJ Offline
                      JonB
                      wrote on last edited by
                      #10

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

                      jsulmJ 1 Reply Last reply
                      0
                      • JonBJ JonB

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

                        jsulmJ Offline
                        jsulmJ Offline
                        jsulm
                        Lifetime Qt Champion
                        wrote on 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
                        • JonBJ 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?

                          J Offline
                          J Offline
                          Joshika_Namani
                          wrote on last edited by
                          #12

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

                          JonBJ 1 Reply Last reply
                          0
                          • J Joshika_Namani

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

                            JonBJ Offline
                            JonBJ Offline
                            JonB
                            wrote on 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
                            0
                            • JonBJ JonB

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

                              }
                              }

                              JonBJ 1 Reply Last reply
                              0
                              • J Joshika_Namani

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

                                }
                                }

                                JonBJ Offline
                                JonBJ Offline
                                JonB
                                wrote on 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
                                2
                                • JonBJ JonB

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

                                  • Login

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