Read content from CSV file and store it in a MySQL db

[Marcus Barnet]

Hi to all,

I have a CSV file with 13 fields like this:

DATE 	 ID 	 FILE PATH 	 START TIME 	 END TIME 	 QUALITY 	 DPI 	 REPEAT 	 PASS 	 PRINT LENGTH 	 PRINT AERA 	 INK CONSUMPTION 	 STATUS
17	5199	 C:\Users\Utente\Desktop\lavori plotter\tree whi 38x75.prt 	 08:26:32 	 08:26:54 	 Gradient Small 	 720x1200 	1	 2/167 	 0.00 mm 	 0.000 sq.m 	 0.00 ml 	 finished
17	5200	 C:\Users\Utente\Desktop\lavori plotter\KANDINSKY  - 38x75 (43x80) (47x84).prt 	 08:27:45 	 08:28:13 	 Gradient Small 	 720x1200 	1	 3/167 	 0.00 mm 	 0.000 sq.m 	 0.01 ml 	 finished
17	5202	 C:\Users\Utente\Desktop\lavori plotter\KANDINSKY  - 38x75 (43x80) (47x84).prt 	 09:14:02 	 09:15:14 	 Gradient Small 	 720x1200 	1	 15/167 	 0.05 mm 	 0.073 sq.m 	 1.54 ml 	 abort
17	5203	 C:\Users\Utente\Desktop\lavori plotter\SUNFLOWER 38X75.prt 	 09:28:48 	 09:38:41 	 Gradient Small 	 720x1200 	1	 167/167 	 1.67 mm 	 2.341 sq.m 	 30.08 ml 	 finished
17	5204	 C:\Users\Utente\Desktop\lavori plotter\SUNFLOWER 38X75.prt 	 09:39:14 	 09:49:07 	 Gradient Small 	 720x1200 	1	 167/167 	 1.67 mm 	 2.341 sq.m 	 30.08 ml 	 finished
17	5205	 C:\Users\Utente\Desktop\lavori plotter\SUNFLOWER 38X75.prt 	 09:49:26 	 09:59:17 	 Gradient Small 	 720x1200 	1	 167/167 	 1.67 mm 	 2.341 sq.m 	 30.08 ml 	 finished
17	5206	 C:\Users\Utente\Desktop\lavori plotter\SUNFLOWER 38X75.prt 	 10:00:44 	 10:10:37 	 Gradient Small 	 720x1200 	1	 167/167 	 1.67 mm 	 2.341 sq.m 	 30.08 ml 	 finished

and I able to read it and display all the values in a tableView:

#include "mainwindow.h"
#include "ui_mainwindow.h"
#include <QFile>
#include <QTextStream>
#include <QDebug>

MainWindow::MainWindow(QWidget *parent) :
    QMainWindow(parent),
    ui(new Ui::MainWindow)
{
    ui->setupUi(this);
    csvModel = new QStandardItemModel(this);
    csvModel->setColumnCount(13);
    csvModel->setHorizontalHeaderLabels(QStringList());
    ui->tableView->setModel(csvModel);


    QFile file(":/test.csv");
    if ( !file.open(QFile::ReadOnly | QFile::Text) ) {
        qDebug() << "File not exists";
    } else {
        QTextStream in(&file);
        while (!in.atEnd())
        {

            QString line = in.readLine();
            QList<QStandardItem *> standardItemsList;
            for (QString item : line.split(",")) {
                standardItemsList.append(new QStandardItem(item));

            }
            qDebug()<< (line.split(','));
            csvModel->insertRow(csvModel->rowCount(), standardItemsList);

        }
        file.close();
    }
}

MainWindow::~MainWindow()
{
    delete ui;
    delete csvModel;
}

with qDebug()<< (line.split(','));, I can read each whole line.
The problem is that I need to save each single value for each line into a MySQL database.

What is the best and smart way to split each whole line in 13 sub-strings and save them to the database?

[Christian Ehrlicher]

@Marcus-Barnet said in Read content from CSV file and store it in a MySQL db:

What is the best and smart way to split each whole line in 13 sub-strings and save them to the database?

Since you already split the line, create a QSqlQuery and insert the values in your table.

[Marcus Barnet]

But, how can I run the query on all the splitted values in once for each line?
If I have 1000 lines, then I need to call the query 1000 times for each file, is it still fine or will it require too much memory?

[Christian Ehrlicher]

@Marcus-Barnet said in Read content from CSV file and store it in a MySQL db:

is it still fine or will it require too much memory?

Why should it require more memory than running a single QSqlQuery once? You don't create 3000 queries though...

[Marcus Barnet]

I should add the query in this loop, I guess:

for (QString item : line.split(",")) {               
standardItemsList.append(new QStandardItem(item));
// query to store values to MySQL database
           }

However, I can't understand how I can run a query to save everything in the database since the for loop just read a value at each step.

[SGaist]

Hi,

Don't loop on the split result. Store it and then use that to prepare your database query.

[Marcus Barnet]

Thank you for your advice and tip!

I think I'll give up since I can't figure out how to do it without using multiple variables to store each split sequence.

[SGaist]

split_list = line.split(",")

# prepare query

query.bind(":field1", split_list[0])
query.bind(":field2", split_list[1])
# etc

if not query.exec():
    print(query.lastError()

[Marcus Barnet]

Thank you, it worked very well!