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

jsulm

@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

@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

@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

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

@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

@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

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

JonB

@Raphawel
OK, so although you have not said I guess ui->customplot->graph(0) must be a QCPGraph?

Given the error message tells you what the issue is I don't know what further to say. You are passing QCPGraph::setData() two QStringLists, list of strings, and not surprisingly it does not accept those. So look at QCPGraph::setData() documentation to see what it does accept.

Raphawel

@jsulm Yes the message tells me that I need QVectors inside setData() :

void QCPGraph::setData ( const QVector< double > &  keys, const QVector< double > &  values, bool  alreadySorted = false  )

So I readapted:

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

            QVector<double> xData2 = lineList;
            QVector<double> yData2 = columnList;
            ui->customplot->graph(0)->setData(xData2, yData2);
            ui->customplot->replot();

But I still have errors due to a conversion problem:

C:\Users\46053500\Documents\Graph_QT\graphConso\graphconso.cpp:123: erreur : conversion from 'QStringList' to non-scalar type 'QVector<double>' requested
             QVector<double> xData2 = lineList;
                                      ^

C:\Users\46053500\Documents\Graph_QT\graphConso\graphconso.cpp:124: erreur : conversion from 'QStringList' to non-scalar type 'QVector<double>' requested
             QVector<double> yData2 = columnList;
                                      ^

I'm sorry if the errors are really glaring but I really don't see how to solve them

SimonSchroeder

@Raphawel You need to transform all entries inside the lists individually. For example, you could use lineList[0].toDouble() to convert a single QString to a double value. There is not automatic conversion from strings to numbers. In imperative programming a for loop comes to mind. However, here is a more functional style approach to "copy" the data over using standard C++:

QVector<double> xData2;
std::transform(lineList.begin(), lineList.end(), std::back_inserter(xData2), [](const QString &str) { return str.toDouble(); });

Raphawel

@SimonSchroeder

        QStringList lineList = {};
        QStringList columnList = {};
        QVector<double> xData2;
        QVector<double> yData2;

        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]);

            xData2.append(champs[0].toDouble());
            yData2.append(champs[1].toDouble());
            
            ui->customplot->graph(0)->setData(xData2, yData2);
            ui->customplot->replot();

I tried to do a QString to double conversion my way. And the program compiles well. And I have the impression that it did the conversion well. Except that I have an error that appears after compilation :

Already if the help you gave me doesn't correspond at all to what I just did, can you tell me please ?

And what does this message mean : The inferior stopped because it received a signal from the operating system

jsulm

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

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

You're not checking how many elements the list actually contains!
Also, take a look at the stack trace: it tells where in your code it crashes.

JonB

@Raphawel

Make sure that the ligne.split() always returns (at least) 2 elements.
Check the result from champs[0].toDouble() (hint: it takes an optional bool *ok = nullptr parameter). Since I see your column #0 seems to contain date string how do you expect them to convert to a double?
I don't think this is required, but does it make any difference if you make x/yData2 vectors have lifetime scope outside the code you show, e.g. as class member variables? [Having said that I suspect the setData(xData2, yData2) takes a copy anyway?
Make sure your graph(0) does point to a valid QCPGraph and that starts out empty.
Try a clearData() before calling setData() each time.

The first thing to check is #2 (and of course #1).

Raphawel

@JonB Thank you very much for detailing the steps to follow :) Considering my level, I tried to reproduce what is asked:

For lineindex = 0 :

QStringList champs = ligne.split(";", QString::KeepEmptyParts); return 4 éléments :
champs[0] = "09/21/2022 02:30 PM CEST"
champs[1] = ""
champs[2] = ""
champs[3] = "576"

For lineindex = 0 :
lineList.append(champs[0]); -> return "09/21/2022 02:30 ..." -> type QString
columnList.append(champs[1]); -> return "" -> type QString
xData2.append(champs[0].toDouble()); -> return "0" -> type double
yData2.append(champs[1].toDouble()); -> return "0" - > type double

xData2 and yData2-> Type QVector <double>

For lineindex = 1 :
lineList.append(champs[0]); -> return "09/21/2022 02:45 ..." -> type QString
columnList.append(champs[1]); -> return "2.712" -> type QString
xData2.append(champs[0].toDouble()); -> return "0" -> type double
yData2.append(champs[1].toDouble()); -> return "0" - > type double

But I see that yData2 doesn't understand float values when it is a double when for example :

columnlist[1] = 2.712 then yData2[1] = 0

But that it understands the integer values:

columnlist[3] = 240 then yData[3] = 240.

On the other hand, I have to find a way to do the conversion of a QString for xData

It does not change anything
I am blind, I forgot to add :

ui->customplot->addGraph();

I added it

So, this time I removed this error : https://forum.qt.io/post/749704 as I can access my graph.

jsulm

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

lineList.append(champs[0]); -> return "09/21/2022 02:30 ..." -> type QString
columnList.append(champs[1]); -> return "" -> type QString
xData2.append(champs[0].toDouble()); -> return "0" -> type double
yData2.append(champs[1].toDouble()); -> return "0" - > type double

Neither "09/21/2022 02:30 ..." nor "" empty string can be converted to a double...

SimonSchroeder

Most likely the reason for your crash is that there is an extra empty line at the end of the file. You are trying to convert that as well. Hence why you need to check if there are enough items.

It is not surprising that a date cannot be converted to a double. Does QCustomPlot support dates? Otherwise you need to use QDateTime to convert the string to a date and use one of its functions to convert it to milliseconds from a specific date (most likely the first date you have) and assign the milliseconds to the double vector (maybe convert them to days instead).

Concerning the second column: Do you get numbers like 2.712 or 2,712? Which is the string you read from the .csv file? The screenshot is telling a different story than your text. Most likely this is related to the locale that is used.

Raphawel

@SimonSchroeder The string I changed the number of lines to look at (30).
So the values of the 1st column are only in the form: "MM/dd/yyyy hh:mm AP".

The string I get back is : "MM/dd/yyyy hh:mm AP" for lineList

For xData2:
Before my while it is defined as a

QVector<double> xData2;

In the while I convert it with

xData2.append(QDateTime::fromString(fields[0], "hh:mm").toTime_t())

I understood that we can't convert a string to a double but the problem is that setData only accepts QVector<double>, why ???

And whatever the type I put (not double) I have all the time the same errors

C:\Users\46053500\Documents\Graph_QT\graphConso\graphconso.cpp:140: error : no matching function for call to 'QCPGraph::setData(QVector<char>&, QVector<double>&)'
         ui->customplot->graph(0)->setData(xData2, yData2);
                                                         ^

My questions: is there a simple way to display a string like mine "MM/dd/yyyy hh:mm AP" on the x axis and an integer(power) on the Y axis.

So by having : setData(QString, double). Despite the fact that setData doesn't accept anything aside doubles

So the conversion method you just proposed @SimonSChroeder. I tried to do it:
```
QStringList lineList = {};
QStringList columnList = {};
QVector<double> xData2;
QVector<double> yData2;
QDateTime referenceDateTime;

    while(lineindex<30)
    {
        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]);

        if(lineindex==0)
        {
            referenceDateTime = QDateTime::fromString(lineList,"MM/dd/yyyy hh:mm AP");
        }

        QDateTime dateTime = QDateTime::fromString(champs[0],"MM/dd/yyyy hh:mm AP");

        qint64 milliseconds = dateTime.toMSecsSinceEpoch() - referenceDateTime.toMSecsSinceEpoch();
        xData2.append(milliseconds); // "MM/dd/yyyy hh:mm AP"
        yData2.append(champs[1].toDouble());

        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->customplot->addGraph();
    ui->customplot->graph(0)->data()->clear();
    ui->customplot->graph(0)->setData(xData2, yData2);

But it doesn't work because of the same problem:

C:\Users\46053500\Documents\Graph_QT\graphConso\graphconso.cpp:125: error : no matching function for call to 'QDateTime::fromString(QStringList&, const char [20])'
                 referenceDateTime = QDateTime::fromString(lineList, "MM/dd/yyyy hh:mm AP");
                                                                                         ^

"Do you get numbers like 2.712 or 2,712?" For the 2nd column, I get values 2,712

jsulm

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

setData only accepts QVector<double>, why ???

Because it plots vectors of doubles.

"I understood that we can't convert a string to a double" - this is wrong. You can convert a string to double if the string contains valid text representation of a number.
""09/21/2022 02:30" - this is NOT a double!

Raphawel

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

Because it plots vectors of doubles.
"I understood that we can't convert a string to a double" - this is wrong. You can convert a string to double if the string contains valid text representation of a number.
""09/21/2022 02:30" - this is NOT a double!

"09/21/2022 02:30" I get that. So I have to convert it using @SimonSChroeder's method to hope to display it.
Why is it not as easy as in Excel to display the x axis with dates like this....

And also aren't there vector plots of QString?

jsulm

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

And also aren't there vector plots of QString?

Why don't you read QCustomPlot documentation? It is not part of Qt.
So, if you read their documentation you will find this: https://www.qcustomplot.com/index.php/tutorials/basicplotting scroll down to "Plotting date and time data"...