How to retrieve data from an excel .csv file, and put it in a graph with QCustomPlot

Raphawel · 27 Feb 2023, 13:08

Hello, I would like to get the data of 2 columns from an excel .csv file, and make the points appear on a graph with QCustomPlot.

First of all, which library to use to process the data from a .csv file. I saw that I had to use QtXlsxWriter, could you confirm if it is this library ?

Correction: Should I use QFile to read and retrieve a .csv file?

Then for QCustomPlot I already know how to use it so normally this part will be good.

Thanks for your help

jsulm · wrote on 27 Feb 2023, 13:08

@Raphawel said in How to retrieve data from an excel .csv file, and put it in a graph with QCustomPlot:

First of all, which library to use to process the data from a .csv file

No need for any libraries. CSV stands for "Comma-separated values" and is a simple text file format. Simply read line by line and split each line using comma or whatever character is used as separator (https://doc.qt.io/qt-6/qstring.html#split-1).

"Correction: Should I use QFile to read and retrieve a .csv file?" - yes (https://doc.qt.io/qt-6/qiodevice.html#readLine).

SimonSchroeder · 28 Feb 2023, 10:21

.csv files are just simple text files. If you have simple data in them, writing a parser is quite simple. (Just as @jsulm said: splitting is enough.) It gets a little bit more complicated if not all of your users are using the English setting for exporting .csv files. In Germany, for example, we use a comma for the decimal point and hence a semicolon to separate columns. If you have text in some cells it can even get a little bit more tricky because text might contain commas as well (text will then be escaped in double quotes).

So, as long as you just have numbers in your .csv file, go with the simple route mentioned by @jsulm.

Raphawel · 28 Feb 2023, 10:25

@SimonSchroeder For example I have this in my .csv file:

Date/Time;Power Consumed (W);Energy Consumed (Wh);Cumulative Energy Consumed (Wh)
09/21/2022 02:45 PM CEST;2,712;674;1,25
09/21/2022 03:00 PM CEST;2,652;-866;384
09/21/2022 03:15 PM CEST;240;1,088;1,472
09/21/2022 03:30 PM CEST;120;38;1,51
09/21/2022 03:45 PM CEST;132;33;1,543
09/21/2022 04:00 PM CEST;156;52;1,595
09/21/2022 04:15 PM CEST;132;33;1,628
09/21/2022 04:30 PM CEST;228;46;1,674
09/21/2022 04:45 PM CEST;144;38;1,712
09/21/2022 05:00 PM CEST;168;36;1,748
09/21/2022 05:15 PM CEST;144;50;1,798
09/21/2022 05:30 PM CEST;144;35;1,833
09/21/2022 05:45 PM CEST;240;50;1,883
09/21/2022 06:00 PM CEST;132;35;1,918
09/21/2022 06:15 PM CEST;168;31;1,949
09/21/2022 06:30 PM CEST;84;33;1,982
09/21/2022 06:45 PM CEST;96;23;2,005
09/21/2022 07:00 PM CEST;132;38;2,043
09/21/2022 07:15 PM CEST;84;22;2,065
09/21/2022 07:30 PM CEST;192;48;2,113
09/21/2022 07:45 PM CEST;132;32;2,145
09/21/2022 08:00 PM CEST;1,08;156;2,301
09/21/2022 08:15 PM CEST;168;102;2,403
09/21/2022 08:30 PM CEST;264;50;2,453
09/21/2022 08:45 PM CEST;156;45;2,498
09/21/2022 09:00 PM CEST;216;45;2,543
09/21/2022 09:15 PM CEST;156;48;2,591
09/21/2022 09:30 PM CEST;216;44;2,635
09/21/2022 09:45 PM CEST;156;51;2,686
09/21/2022 10:00 PM CEST;180;40;2,726

So it's the same for me, i use comma for the decimal and a semicolon to separate columns

JonB · 28 Feb 2023, 10:28

@Raphawel
Since you don't have to worry about any " (double quote) around your items you can just use QString::split() with the ; character to split into fields/columns. Your , for floating point numbers can be dealt with by a suitable "to number/float" which allows for your locale, either in QString or QLocale classes, have a look through.

Raphawel · 28 Feb 2023, 10:29

@JonB Ok thanks :) , and just to be sure it applies for the QT 5.6.3 version? Because I need to use this one for a project

JonB · 28 Feb 2023, 15:28

@Raphawel This is quite independent of Qt version.

Raphawel · 28 Feb 2023, 15:46

@JonB I'm trying to display items from a test.csv file in a QTableView. But I have a problem, my code works but I have the impression that it does'nt recognize the test.csv file even though it is in the project folder of the code.

This is obvious because I don't receive any value in my QTableView. And even when I do a debug

This is my test.csv:

And my program :

void MainWindow::LireFichierCSV()
{

    QStandardItemModel *model= new QStandardItemModel();
    QFile file("test.csv");    //My CSV

    if(file.open(QFile::ReadOnly) | QIODevice::Text)
    {
    int lineindex = 0;
    QTextStream flux(&file);

    while(!flux.atEnd())
    {
        QString ligne = flux.readLine();
        QStringList champs = ligne.split(";", QString::SkipEmptyParts);

        for(int j=0;j < champs.size();j++)
        {
            QString value = champs.at(j);
            QStandardItem *item = new QStandardItem(value);
            model->setItem(lineindex, j, item);
        }
        lineindex++;

    }
        file.close();
        ui->tableView->setModel(model);

    }  
}

And this is my result with debug :

SimonSchroeder · wrote on 28 Feb 2023, 15:46

@Raphawel Got to "Projects" on the left pane and check the run configuration. Is your file test.csv inside the folder that is set as working directory? You can adapt the working directory to your needs if necessary.

JonB · 1 Mar 2023, 08:22

@Raphawel said in How to retrieve data from an excel .csv file, and put it in a graph with QCustomPlot:

QFile file("test.csv"); //My CSV

Do not use a relative path like this. It means relative to whatever the current working directory is, and you really don't know what that will be at runtime. Use an absolute path, or build a path from QStandardPaths. Your "project folder" does not "exist" at runtime, it's purely something that is used by Creator at design time.

SGaist · 1 Mar 2023, 08:36

Hi,

In addition to what my fellows wrote: add menu and or button to allow your user to browse their system to the .CSV file so you don't have to hard code any value.

You can use QFileDialog for that.

Raphawel · J JonB 28 Feb 2023, 16:04

@JonB

I followed your advice and did this:

QStringList location = QStandardPaths::standardLocations(QStandardPaths::DocumentsLocation);
QString result = location[0];
result = result + QString("/test.csv");
QFile file(result);

I don't know if this is exactly what you are thinking about but if I understand correctly, here we come to look at the /Document directory and come to read all the files that are in it. And it works

Raphawel · 1 Mar 2023, 09:10

@SGaist Excuse me, I'm not sure I understand what you're telling me.

Right now I am practicing retrieving values from a .CSV. This is not the actual .csv file that I will eventually use.

And normally, in the real application, the user will only be able to see the values from a graph that will display the values in real time.

Does this apply to what you mean?

jsulm · 1 Mar 2023, 09:50

@Raphawel @SGaist suggested to provide the user the posibility to open a CSV file, just like in any other application using, for example, "File/Open" menu. But this is of course only needed if you want to give the users of your application a possibility to open a CSV file.

Raphawel · 3 Jan 2023, 09:53

@jsulm Oh right, so I don't think it will be useful for my application.

Just to show my result:
CSV used:

Date/Time;Power Consumed (W);Energy Consumed (Wh);Cumulative Energy Consumed (Wh)
09/21/2022 02:30 PM CEST;;;576
09/21/2022 02:45 PM CEST;2,712;674;1,25
09/21/2022 03:00 PM CEST;2,652;-866;384
09/21/2022 03:15 PM CEST;240;1,088;1,472
09/21/2022 03:30 PM CEST;120;38;1,51
09/21/2022 03:45 PM CEST;132;33;1,543
09/21/2022 04:00 PM CEST;156;52;1,595
09/21/2022 04:15 PM CEST;132;33;1,628
09/21/2022 04:30 PM CEST;228;46;1,674
09/21/2022 04:45 PM CEST;144;38;1,712
09/21/2022 05:00 PM CEST;168;36;1,748
09/21/2022 05:15 PM CEST;144;50;1,798
09/21/2022 05:30 PM CEST;144;35;1,833
09/21/2022 05:45 PM CEST;240;50;1,883
09/21/2022 06:00 PM CEST;132;35;1,918
09/21/2022 06:15 PM CEST;168;31;1,949
09/21/2022 06:30 PM CEST;84;33;1,982
09/21/2022 06:45 PM CEST;96;23;2,005
09/21/2022 07:00 PM CEST;132;38;2,043
09/21/2022 07:15 PM CEST;84;22;2,065
09/21/2022 07:30 PM CEST;192;48;2,113
09/21/2022 07:45 PM CEST;132;32;2,145
09/21/2022 08:00 PM CEST;1,08;156;2,301
09/21/2022 08:15 PM CEST;168;102;2,403
09/21/2022 08:30 PM CEST;264;50;2,453
09/21/2022 08:45 PM CEST;156;45;2,498
09/21/2022 09:00 PM CEST;216;45;2,543
09/21/2022 09:15 PM CEST;156;48;2,591
09/21/2022 09:30 PM CEST;216;44;2,635
09/21/2022 09:45 PM CEST;156;51;2,686
09/21/2022 10:00 PM CEST;180;40;2,726
09/21/2022 10:15 PM CEST;144;49;2,775
09/21/2022 10:30 PM CEST;132;30;2,805
09/21/2022 10:45 PM CEST;108;42;2,847
09/21/2022 11:00 PM CEST;108;25;2,872
09/21/2022 11:15 PM CEST;108;43;2,915

Code (To identify the empty fields, I replaced :

QStringList champs = ligne.split(";",QString::SkipEmptyParts);

by :

QStringList champs = ligne.split(";", QString::KeepEmptyParts);

Code :

void MainWindow::LireFichierCSV()
{

    QStandardItemModel *model= new QStandardItemModel();

    QStringList location = QStandardPaths::standardLocations(QStandardPaths::DocumentsLocation);  // Viens lire tout les fichiers du répertoire Document, mettre ::DesktopLocation pour le bureau
    QString result = location[0];
    result = result + QString("/system_meter_conso.csv");
    QFile file(result);

    if(file.open(QFile::ReadOnly) | QIODevice::Text)
    {

    model->setHorizontalHeaderLabels(QStringList() << "Date/Time" << "PowerConsumed (W)");

    int lineindex = 0;
    QTextStream flux(&file);

     flux.readLineInto(nullptr); // Passe la 1ère ligne

    while(!flux.atEnd())
    {
        QString ligne = flux.readLine();
        QStringList champs = ligne.split(";", QString::KeepEmptyParts); //::KeepEmptyParts : Permet de voir les zones vides et de noter les résultats que quand il y en a

        for(int j=0;j < champs.size();j++)
        {
            QString value = champs.at(j);
            QStandardItem *item = new QStandardItem(value);
            model->setItem(lineindex, j, item);
        }
        lineindex++;
    }

    file.close();

        ui->tableView->setModel(model);
    }
}

Result:

Now my goal is to recover only the first two columns

Raphawel · 3 Jan 2023, 13:46

@Raphawel To recover the first two colums here is the program:

void MainWindow::LireFichierCSV()
{

    QStandardItemModel *model= new QStandardItemModel();
    //QFile file("test.csv");

    QStringList location = QStandardPaths::standardLocations(QStandardPaths::DocumentsLocation);  // Viens lire tout les fichiers du répertoire Document, mettre ::DesktopLocation pour le bureau
    QString result = location[0];
    result = result + QString("/system_meter_conso.csv");
    QFile file(result);

    if(file.open(QFile::ReadOnly) | QIODevice::Text)
    {

    model->setHorizontalHeaderLabels(QStringList() << "Date/Time" << "PowerConsumed (W)");

    int lineindex = 0;
    QTextStream flux(&file);



     flux.readLineInto(nullptr); // Passe la 1ère ligne

     QStringList lineList = {};
     QStringList columnList = {};



    while(!flux.atEnd())
    {
           QString ligne = flux.readLine();
           QStringList champs = ligne.split(";", QString::KeepEmptyParts); //::KeepEmptyParts : Permet de voir les zones vides et de noter les résultats que quand il y en a

           lineList.append(champs[0]);
           columnList.append(champs[1]);


            QString valueL = lineList[lineindex];
            QString valueC = columnList[lineindex];
            QStandardItem *itemL = new QStandardItem(valueL);
            QStandardItem *itemC = new QStandardItem(valueC);
            model->setItem(lineindex, 0, itemL );
            model->setItem(lineindex, 1,  itemC);
           lineindex++;

        /*for(int j=0;j < champs.size();j++)
        {
            QString value = champs.at(j);
            QStandardItem *item = new QStandardItem(value);
            model->setItem(lineindex, j, item);
        }
        lineindex++;*/
    }

    file.close();

        ui->tableView->setModel(model);
    }

}

Result:

Raphawel · 3 Jan 2023, 14:54

Could someone help me please ? :(

void GraphConso::LireFichierCSV()
{
    QStandardItemModel *model= new QStandardItemModel();
    //QFile file("test.csv");

    QStringList location = QStandardPaths::standardLocations(QStandardPaths::DocumentsLocation);  // Viens lire tout les fichiers du répertoire Document, mettre ::DesktopLocation pour le bureau
    QString result = location[0];
    result = result + QString("/system_meter_conso.csv");
    QFile file(result);

    if(file.open(QFile::ReadOnly) | QIODevice::Text)
    {

        model->setHorizontalHeaderLabels(QStringList() << "Date/Time" << "PowerConsumed (W)");

        int lineindex = 0;
        QTextStream flux(&file);



        flux.readLineInto(nullptr); // Passe la 1ère ligne

        QStringList lineList = {};
        QStringList columnList = {};



        while(!flux.atEnd())
        {
            QString ligne = flux.readLine();
            QStringList champs = ligne.split(";", QString::KeepEmptyParts); //::KeepEmptyParts : Permet de voir les zones vides et de noter les résultats que quand il y en a

            lineList.append(champs[0]);
            columnList.append(champs[1]);
            ui->customplot->graph(0)->setData(lineList, columnList);
            ui->customplot->replot();


            QString valueL = lineList[lineindex];
            QString valueC = columnList[lineindex];
            QStandardItem *itemL = new QStandardItem(valueL);
            QStandardItem *itemC = new QStandardItem(valueC);
            model->setItem(lineindex, 0,  itemL);
            model->setItem(lineindex, 1,  itemC);

            lineindex++;

        }

        file.close();

        ui->tableView->setModel(model);

    }

}

I would like to display on my graph, the values of the two columns with x -> Date/Time and y -> PowerConsumed.

Here I have used the rows :

ui->customplot->graph(0)->QCPGraph::setData(lineList, columnList);
           ui->customplot->replot();

I still don't know if this technique works but I get this as an error:
Can you help me please ?

C:\Users\46053500\Documents\Graph_QT\graphConso\graphconso.cpp:122: erreur : 'QCPGraph' is not a base of 'QCustomPlot'
             ui->customplot->QCPGraph::setData(lineList, columnList);

I don't know what this means

JonB · 2 Mar 2023, 07:48

@Raphawel
Your code shows

ui->customplot->graph(0)->QCPGraph::setData

but the error message shows

ui->customplot->QCPGraph::setData

so which do you actually have? Do you/are you supposed to have a QCPGraph object you are trying to call setData() on? I don't even know what your "if this technique works" refers to.

Raphawel · 2 Mar 2023, 07:51

@JonB Ohh sorry, the real mistake is this:

With the line :

lineList.append(champs[0]);
            columnList.append(champs[1]);

            //QVector<const char*> xData2 = lineList;
            //QVector<const char*> yData2 = columnList;
            ui->customplot->graph(0)->setData(lineList, columnList);
            ui->customplot->replot();

And the error :

C:\Users\46053500\Documents\Graph_QT\graphConso\graphconso.cpp:125: erreur : no matching function for call to 'QCPGraph::setData(QStringList&, QStringList&)'
             ui->customplot->graph(0)->setData(lineList, columnList);
                                                                   ^

My IDE :

When I say if this technique works, I mean using:

ui->customplot->graph(0)->setData(lineList, columnList);

To display on the graph (custom plot object) the points from my csv file

jsulm · 2 Mar 2023, 07:57

@Raphawel The error message is very clear.
Please check documentation what parameters QCPGraph::setData expects (https://www.qcustomplot.com/documentation/classQCPGraph.html#a73578d786532132310a926c3cd529b29).