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