Read content from CSV file and store it in a MySQL db
-
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 finishedand 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?
-
@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.
-
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? -
@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...
-
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.
-
Hi,
Don't loop on the split result. Store it and then use that to prepare your database query.
-
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.
-
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() -
Thank you, it worked very well!
