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!