How write to a CSV from an array, but append each value to the END of the line, NOT the next line

New comment
 

Hi All, I want to add the contents of an array to a csv file, but I want to append each value to the end on the current line, not to the next line.

Here is the code which works perfectly, except for the positioning of the output in the CSV file.

#property strict

string _sym[] =  {"AUDCAD.a","AUDCHF.a","AUDJPY.a","AUDNZD.a","AUDSGD.a","AUDUSD.a","CADCHF.a",
                  "CADJPY.a","CHFJPY.a","EURAUD.a","EURCAD.a","EURCHF.a","EURGBP.a","EURJPY.a",
                  "EURNZD.a","EURUSD.a","GBPAUD.a","GBPCAD.a","GBPCHF.a","GBPJPY.a","GBPNZD.a",
                  "GBPUSD.a","NZDJPY.a","NZDUSD.a","USDCAD.a","USDCHF.a","USDJPY.a"};              // Symbols array 
 
int _myhandle,_count;
double _spreadarray[30][1];

void init()
{
   _myhandle=FileOpen("Spreads.csv",FILE_CSV|FILE_READ|FILE_WRITE|FILE_SHARE_READ|FILE_SHARE_WRITE,",");  // Write the headers to the file
   FileWrite(_myhandle,"BUY/SELL SPREAD IN POINTS"); 
   FileWrite(_myhandle,""); // leave a blank line         
   FileWrite(_myhandle,"AUDCAD.a","AUDCHF.a","AUDJPY.a","AUDNZD.a","AUDSGD.a","AUDUSD.a","CADCHF.a",
               "CADJPY.a","CHFJPY.a","EURAUD.a","EURCAD.a","EURCHF.a","EURGBP.a","EURJPY.a",
               "EURNZD.a","EURUSD.a","GBPAUD.a","GBPCAD.a","GBPCHF.a","GBPJPY.a","GBPNZD.a",
               "GBPUSD.a","NZDJPY.a","NZDUSD.a","USDCAD.a","USDCHF.a","USDJPY.a");
   FileWrite(_myhandle,""); // leave a blank line
   FileFlush(_myhandle); // Ensure HDD is written to .. 
   FileClose(_myhandle); // Close the file
}

void OnStart()
{
   for (_count=0; _count<ArraySize(_sym); _count++)
   {
   _spreadarray[_count][0]= MarketInfo(_sym[_count], MODE_SPREAD);   // populate array with each pairs current spread
   }
        
   _myhandle=FileOpen("Spreads.csv",FILE_CSV|FILE_READ|FILE_WRITE|FILE_SHARE_READ|FILE_SHARE_WRITE,",");    // open the CSV file   
   FileSeek(_myhandle,0,SEEK_END);     // go to the start of the line following the end of the file  
                
   for (_count=0; _count<ArraySize(_sym); _count++)
   { 
      FileWrite(_myhandle,_spreadarray[_count][0]); // write each cell of the array to the file

      // This makes the each output move down to the next line of the CSV file. I want to append the resulting output along the current line
   }  
   
   FileFlush(_myhandle);                                                         //Write to the HDD
   FileClose(_myhandle);                                                         //Close the file   
   Alert("END");
}

The aim is to record the spread of each of the pairs every five minutes and then draw a graph using the CSV file in Excell.

Hoping somebody can help .....

 

You don't have to open the csv file as FILE_CSV, try FILE_BIN and write the the values followed by "," like:  FileWrite(_myhandle,_spreadarray[_count][0],",");

 

Sorry, I can't make that work either.

If I use FILE_BIN the file appears to be empty :(

 

Hello Graham from Brisbane .

Try writing this way 

FILE_WRITE|FILE_TXT

each line is collected and then written with a "\n" line break at the end

You probably want this to be opened and stay open as its called in intervals to log the spread , so this example is not exactly how you need it to be , i suppose.

If you want to write in MT5 you separate with "\t" instead of "," too . (if you are interested in using it in excel)

#property strict

string _sym[] =  {"AUDCAD.a","AUDCHF.a","AUDJPY.a","AUDNZD.a","AUDSGD.a","AUDUSD.a","CADCHF.a",
                  "CADJPY.a","CHFJPY.a","EURAUD.a","EURCAD.a","EURCHF.a","EURGBP.a","EURJPY.a",
                  "EURNZD.a","EURUSD.a","GBPAUD.a","GBPCAD.a","GBPCHF.a","GBPJPY.a","GBPNZD.a",
                  "GBPUSD.a","NZDJPY.a","NZDUSD.a","USDCAD.a","USDCHF.a","USDJPY.a"};              // Symbols array 

int _myhandle,_count;
double _spreadarray[30][1];

void init()
{

}

void OnStart()
{
//this will change lines in the csv 
string lineSeparator="\n";
//this is cell the separator of the csv 
string separator=",";//in MT5 this is \t (if you want to play with it in excel) 

   for (_count=0; _count<ArraySize(_sym); _count++)
   {
   _spreadarray[_count][0]= MarketInfo(_sym[_count], MODE_SPREAD);   // populate array with each pairs current spread
   }
        
   //there is definately a reason you want this as shared but for the example i'll skip it
   //_myhandle=FileOpen("Spreads.csv",FILE_CSV|FILE_READ|FILE_WRITE|FILE_SHARE_READ|FILE_SHARE_WRITE,",");    // open the CSV file   
   _myhandle=FileOpen("Spreads.csv",FILE_WRITE|FILE_TXT);
   if(_myhandle!=INVALID_HANDLE){
   //FileSeek(_myhandle,0,SEEK_END);     // go to the start of the line following the end of the file  
   //header 
     FileWriteString(_myhandle,"Header"+lineSeparator);
   //symbols 
     string line="";
     for(_count=0;_count<ArraySize(_sym);_count++){
     line+=_sym[_count];
     if(_count<ArraySize(_sym)-1){line+=separator;}
     }
     FileWriteString(_myhandle,line+lineSeparator);
   for (_count=0; _count<ArraySize(_sym); _count++)
   {
   _spreadarray[_count][0]= MarketInfo(_sym[_count], MODE_SPREAD);   // populate array with each pairs current spread
   }            
   line="";
   for (_count=0; _count<ArraySize(_sym); _count++)
   { 
   //FileWrite(_myhandle,_spreadarray[_count][0]); // write each cell of the array to the file
     line+=IntegerToString((int)_spreadarray[_count][0]);
     if(_count<ArraySize(_sym)-1){line+=separator;}
      // This makes the each output move down to the next line of the CSV file. I want to append the resulting output along the current line
   }  
   FileWriteString(_myhandle,line+lineSeparator);
   
   FileFlush(_myhandle);                                                         //Write to the HDD
   FileClose(_myhandle);
   }                                                         //Close the file   
   Alert("END");
}

Also , im attaching this in case you are interested in playing with cell formulas and functions in excel 

string vocabulary[]={"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"};
string number_to_colnumber(int i,string &vocabulary[]){
string result="";
int j=i;
double size=((double)ArraySize(vocabulary)),div=1.0;
//Print("Size ="+IntegerToString(ArraySize(vocabulary)));
double digs=((double)j)/((double)size);
double step_per_digit=1.00/((double)size);
bool   keep=true;
double previous=j;
double size_div=((double)size);
while(keep){
double penalty=0.0;
double modulo=MathMod(j,size_div);
double remnant=MathFloor(j/size_div);
int digit=((int)(modulo))-1;
if(modulo<=0){digit=ArraySize(vocabulary)-1;penalty=-1.0;}
result=vocabulary[digit]+result;
if(j<=size_div){keep=false;}
j=(int)(remnant+penalty);
}
//main loop ends here
return(result);
}
 

Thanks Lorentzos,

This is the sort of help that I needed. I would never have thought of formatting the text file so that it would be seen as a CSV file because the CSV format was available natively. I can soon sort out the need to keep the file running over a period of time.

I'll try to get my head (a very tired, old, head) around the excel example too :)

Cheers .....

 
Graham from Brisbane #:

Thanks Lorentzos,

This is the sort of help that I needed. I would never have thought of formatting the text file so that it would be seen as a CSV file because the CSV format was available natively. I can soon sort out the need to keep the file running over a period of time.

I'll try to get my head (a very tired, old, head) around the excel example too :)

Cheers .....

Excel has grown to be very neat , you can try playing around with its solver plugin (genetic algorithm , aeons ahead of the one metaquotes sports in the platforms) - well not aeons ahead but you can adjust its parameters unlike the mq one-, powerful stuff 

Cheers . 

New comment
Reason: