How do you work with CSV files?

skylarwalker 2022.08.29 21:28

I can't figure out how to put data from a CSV file into an array or a structure array. Once the file is downloaded, how do you work with it? Here is my file download:

void      WebData()
 {
   string cookie=NULL, headers;
   string reqheaders="User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/70.0.3538.102 Safari/537.36 Edge/18.19042\r\n";
   char post[],result[];
   double r[];
   
   int res;
   string url="

https://data.bitcoinity.org/export_data.csv?c=e&amp;data_type=volume&r=hour&t=b&timespan=7d";
   ResetLastError();
   int timeout=5000;
   res=WebRequest("GET",url,reqheaders,timeout,post,result,headers);
   if(res==-1)
     {
      Print("Error in WebRequest. Error code  =",GetLastError());
      //--- Perhaps the URL is not listed, display a message about the necessity to add the address
      MessageBox("Add the address '"+url+"' in the list of allowed URLs on tab 'Expert Advisors'","Error",MB_ICONINFORMATION);
     }
   else
     {
      //--- Load successfully
      PrintFormat("The file has been successfully loaded, File size =%d bytes.",ArraySize(result));
      //--- Save the data to a file
      int filehandle=FileOpen("test.csv",FILE_WRITE|FILE_BIN);
      //--- Checking errors
      if(filehandle!=INVALID_HANDLE)
        {
         //--- Save the contents of the result[] array to a file
         FileWriteArray(filehandle,result,0,ArraySize(result));
         //--- Close the file
         FileClose(filehandle);
        }
      else Print("Error in FileOpen. Error code=",GetLastError());
     }

}

This code works, I can open the file in notepad and see all the data. Now how do I extract the data into a structure or even just an array, but I have no idea how to do this. I've searched the forum and read many examples.

int file = FileOpen("test.csv",FILE_READ|FILE_TXT|FILE_COMMON);

   if(file != INVALID_HANDLE)
   {
      while(!FileIsEnding(file) && !_StopFlag)
       { FileReadString(file); }

      FileClose(file);
   }

This code above doesn't work. I tried it with FILE_CSV open flag. I've tried FILE_BIN flag with FileReadArray. I'm obviously not understanding how to work with files once they're downloaded. Any help would be appreciated.

William Roeder 2022.08.29 21:58 #1

skylarwalker: I can't figure out how to put data from a CSV file into an array or a structure array. Once the file is downloaded, how do you work with it? Here is my file download:

What you posted is not a CSV file. It is code that reads a URL. That could be anything. You did not post a downloaded file.

Do you really expect an answer? There are no mind readers here and our crystal balls are cracked. Always post all relevant code (using Code button) or attach the source file.
How To Ask Questions The Smart Way. (2004)
Be precise and informative about your problem

Error script FileSeek error 5035 Exported csv file from

skylarwalker 2022.08.29 22:50 #2

William Roeder #:

What you posted is not a CSV file. It is code that reads a URL. That could be anything. You did not post a downloaded file.

It's not? I see the CSV file on my pc though, so it is being downloaded from the link. I thought the fileopen part of the code is writing what it reads to a file.

int filehandle=FileOpen("test.csv",FILE_WRITE|FILE_BIN);
      //--- Checking errors
      if(filehandle!=INVALID_HANDLE)
        {
         //--- Save the contents of the result[] array to a file
         FileWriteArray(filehandle,result,0,ArraySize(result));
         //--- Close the file
         FileClose(filehandle);
        }

William Roeder 2022.08.29 22:58 #3

skylarwalker #: It's not? I see the CSV file on my pc though, so it is being downloaded from the link.

You just posted more code. We have no access to your machine. You haven't attached any files here.

skylarwalker 2022.08.29 23:05 #4

The file is at the link in the code... I attached the file.

Files:

test.csv 26 kb

William Roeder 2022.08.30 11:38 #5

skylarwalker #: It's not? I see the CSV file on my pc though

int filehandle=FileOpen("test.csv",FILE_WRITE|FILE_BIN);

So why are you trying to write it? As binary? It's CSV.

It is ANSI not UNICODE, but you didn't specify either.

Time,bitfinex,bitso,bitstamp,btcmarkets,coinbase,exmo,gemini,korbit,kraken,others
2022-08-22 18:00:00 UTC,753.411434689989,10.88726352,142.67348607,2.60915712,1476.08998657986,…

You haven't specified the comma separator.

The first line is seven (7) fields (headers). You have to read and toss them.
The subsequent lines contain a timestamp and six (6) doubles. The doubles are easy (FileReadNumber). The timestamp is not formatted for FileReadDatetime
You will have to read it into a string and use StringToTime to convert it to a datetime.
Second, the time is UTC, you have to adjust it to your brokers timezone, by adding: ( TimeCurrent()-TimeGMT() +900)/1800*1800

Is this a bug? Draw a trend from CSV Signals how to

skylarwalker 2022.08.30 14:16 #6

William Roeder #:

So why are you trying to write it? As binary? It's CSV.
It is ANSI not UNICODE, but you didn't specify either.
You haven't specified the comma separator.
The first line is seven (7) fields (headers). You have to read and toss them.
The sequent lines contain a timestamp and six (6) doubles. The doubles are easy (FileReadNumber). The timestamp is not formatted for FileReadDatetime
You will have to read it into a string and use StringToTime to convert it to a datetime.
Second, the time is UTC, you have to adjust it to your brokers timezone, by adding: ( TimeCurrent()-TimeGMT() +900)/1800*1800

Thank you so much. I really appreciate the help. The mql5 documentation is so confusing sometimes for a noob like me.

skylarwalker 2022.09.08 17:50 #7

void      WebData()
 {
   string cookie=NULL, headers;
   string reqheaders="User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/70.0.3538.102 Safari/537.36 Edge/18.19042\r\n";
   char post[],result[];
   double r[];
   string url;
   if(Period()>=PERIOD_H1)
    { url=url60; }
   else 
    { url=url1; }
//  url60= https://data.bitcoinity.org/export_data.csv?c=e&amp;data_type=volume&r=hour&t=b&timespan=7d
// url1=https://data.bitcoinity.org/export_data.csv?c=e&amp;data_type=volume&r=minute&t=b&timespan=24h
   int res;
   
   ResetLastError();
   int timeout=5000;
   res=WebRequest("GET",url,reqheaders,timeout,post,result,headers);
   if(res==-1)
     {
      Print("Error in WebRequest. Error code  =",GetLastError());
      //--- Perhaps the URL is not listed, display a message about the necessity to add the address
      MessageBox("Add the address '"+url+"' in the list of allowed URLs on tab 'Expert  
                  Advisors'","Error",MB_ICONINFORMATION);
     }
   else
     //{
      
      //--- Load successfully
      PrintFormat("The file has been successfully downloaded, File size =%d bytes.",ArraySize(result));
      //--- Save the data to a file
      string test=CharArrayToString(result,0,-1,CP_ACP);
      
      int filehandle=FileOpen("BTC_volume.csv",FILE_WRITE|FILE_CSV|FILE_ANSI,",",CP_ACP);
      //--- Checking errors
      if(filehandle!=INVALID_HANDLE)
        {
         //--- Save the contents of the result[] array to a file
         FileWriteString(filehandle,test);
         //--- Close the file
         FileClose(filehandle);
        }
      else Print("Error in FileOpen. Error code=",GetLastError());
      

      
   int h_file=FileOpen("BTC_volume.csv",FILE_READ|FILE_CSV|FILE_ANSI,",",CP_ACP);
   if(h_file!=INVALID_HANDLE)
    {
       int v=0;
       double value[];
       datetime time[];
       double val=0;
       string minute_bar_time;
       
       
       for(int k=0; k<11; k++)    //ignore the headers
         {
           FileReadString(h_file);
          }
         
      while(!FileIsEnding(h_file) && !_StopFlag)
       {
        minute_bar_time = FileReadString(h_file);   
        
        ArrayResize(time,v+1);
        time[v]=StringToTime(minute_bar_time)+(TimeCurrent()-TimeGMT()+900)/1800*1800; //format and save date to buffer
        while(!FileIsLineEnding(h_file)) { 
                                           double num = FileReadNumber(h_file);
                                           if(num>0)
                                           val += num;           //total volumes from all exchanges each line (each bar)
                                           } 
         ArrayResize(value,v+1);
         value[v]=val;
         v++;
         val=0;
        }
       ArrayResize(btcvl,ArraySize(value)-1);   //don't include "current" bar with incomplete data, hence arraysize-1
       for(int i=0; i<ArraySize(btcvl); i++)       //copy buffers to structure.
        {
         btcvl[i].vol_tot=value[i];
         btcvl[i].voltime=time[i];
         }
      //Print("last value  ",value[ArraySize(value)-1]);
      FileClose(h_file);
     }

Since I am not certain of the amount of data being downloaded, I have kept the arrays dynamic. However, I wonder if I am using arrayresize too often and if there is a more economical way to do it.

It is working beautifully though so thank you again for the help.

array function get "array Minimum to dynamic array? Sorting Arrays

William Roeder 2022.09.08 18:11 #8

skylarwalker #: However, I wonder if I am using arrayresize too often and if there is a more economical way to do it.

Use the third parameter.

skylarwalker 2022.09.11 17:35 #9

William Roeder #:
Use the third parameter.

understood

New comment