CSV reading problems

New comment
 

Hi coders,

Could someone help me out with a CSV fileread problem?

I need the EA to open a CSV file and check it's data. In this CSV file there are many lines with (almost) all 4 entries (columns). The goal is to take the average of these columns. Data looks like this:

1.2,1.254,1.86,1.8693,
1.2424,0,1.82562,1.83,
1.2,1.344,1.86, // !! here no dataentry for the 4th column

Mind that sometimes a line of data has no entry for the 4th colum

With info found on this site i've created an ea to calculate the averages of those columns, but something goes wrong. It seems that the EA mixes up the values (for example, a value for column 2 gets in column 4).

Can anyone tell me what i do wrong?

Here is the code:

double N1,N2,N3,N4;
double Sum1,Sum2,Sum3,Sum4;
double Average1,Average2,Average3,Average4;
int counter1,counter2,counter3,counter4;

int start()
  {
//----
   ReadValues();             // go to readvalues() void
   Average1 = Sum1/counter1; //calculate average 1th column
   Average2 = Sum2/counter2; //calculate average 2th column
   Average3 = Sum3/counter3; //calculate average 3th column
   Average4 = Sum4/counter4; //calculate average 4th column
//----
   return(0);
  }
//+------------------------------------------------------------------+

void ReadValues(){
   
int handle;
  
  
     handle=FileOpen("CSVData.csv",FILE_CSV|FILE_READ,",");    // File opening
   if(handle<0)                                         // File opening fails
   {
      if(GetLastError()==4103)                          // If the file does not exist,..
      {
         Print("No file named CSVData.csv");              //.. inform trader
      }
      else
      {
                                                         // If any other error occurs..
         Print("Error while opening file CSVData.csv");    //..this message
                      
         return;                                         // Exit ReadValues()

      }
   }
   
   while(FileIsEnding(handle)==false)                    // While the file pointer..     
   {                                                     // ..is not at the end of the file
             
              N1=FileReadNumber(handle);counter1++;     //read 1th column and add 1 to dataentry column 1 counter
              N2=FileReadNumber(handle);counter2++;     //read 2th column and add 1 to dataentry column 2 counter
              N3=FileReadNumber(handle);counter3++;     //read 3th column and add 1 to dataentry column 3 counter
              N4=FileReadNumber(handle);counter4++;     //read 4th column and add 1 to dataentry column 4 counter
              
              Sum1 = Sum1 + N1;                         // Add value 1th column to sum 1th column
              Sum2 = Sum2 + N2;                         // Add value 2th column to sum 2th column
              Sum3 = Sum3 + N3;                         // Add value 3th column to sum 3th column
              Sum4 = Sum4 + N4;                         // Add value 4th column to sum 4th column
               
   }
   FileClose( handle );   
  }

Thanks!

 

While(!FileISEnding) { READ, READ ... assumes that you have all 4 entries, you don't on the last line and the read fails. While(false == false) is redundant try while (!false) "while not false".

This fixes the last line:

while(true){
    if (FileIsEnding(handle)) break;
    Sum1 += FileReadNumber(handle); counter1++;     

    if (FileIsEnding(handle)) break;
    Sum2 += FileReadNumber(handle); counter2++;     

    if (FileIsEnding(handle)) break;
    Sum3 += FileReadNumber(handle); counter3++;     

    if (FileIsEnding(handle)) break;
    Sum4 += FileReadNumber(handle); counter4++;     
}

The extra comma on the first two lines means there are 5 values on that line, so you need to read the empty value to keep things straight.

Since you say some lines are missing the fourth value (not just the last line,) I doubt you're going to be able read the columns correctly. You're going to have to read the entire line (not csv mode) and parse the commas yourself. Or FIX the source.

 

Thanks, WHRoeder.

I've managed to get back the missing values, so that all rows are complete.

"The extra comma on the first two lines means there are 5 values on that line, so you need to read the empty value to keep things straight."

This was a very important clue.

New comment