Need help to solve the CSV to array issue

Hi,
I am trying to import data from CSV file to an array. I am using the below code. but unfortunately, the fgets is not giving a correct results.
CSV data sample -
nifsm_2015,01/01/15 9:00,0,0,5323.86,,,,,8,9,10,11,0,0,0,8311
nifsm_2015,01/01/15 9:30,0,0,5323.86,,,,,12,13,14,15,0,0,0,8314

I need to extract the 9,10,11 and 12th data value and store in the array. I later want to use the array along with system-arrays(OHLC) and make some trades.

Below code works but exploration shows that it is skipping a line.

Ticker Date/Time Close Date ipivsh1 ipivsl1 ipivlh1 ipivll1
nifsm_2015 01/01/15 9:00 8311 01/01/15 9:00 8 9 10 11
nifsm_2015 01/01/15 9:30 8314 01/01/15 9:30 0 0 0 0
nifsm_2015 01/01/15 10:00 8315 01/01/15 10:00 12 13 14 15

Please help to resolve this issue.


file1 = "C:\\2015\\var_db.csv"; 
dt = DateTime();
//
// Initialize variables
ipivsh1=0;
ipivsl1=0;
ipivlh1=0;
ipivll1=0;
//
fh = fopen( file1, "r" );
//
if( fh )
 {
     for(i=0;i<BarCount;i++ )
     {
         line1 = fgets( fh );
             ipivsh1[i] = StrToNum(StrExtract( line1, 9 ));
             ipivsl1[i] = StrToNum(StrExtract( line1, 10 ));
             ipivlh1[i] = StrToNum(StrExtract( line1, 11));
             ipivll1[i] = StrToNum(StrExtract( line1, 12));
             i=i+1;
     }
     //
     fclose( fh );
 }
 else
 {
     printf("ERROR: file can not be open");
 }
 Filter=1;
 AddColumn( C, "Close" );
 AddColumn( DateTime(), "Date", formatDateTime );
 AddColumn(ipivsh1,"ipivsh1");
AddColumn(ipivsl1,"ipivsl1");
AddColumn(ipivlh1,"ipivlh1");
AddColumn(ipivll1,"ipivll1");

You are incrementing i twice: First in the "for" construct, and then at the end of your block inside the loop.

Got it. Thanks for the quick reply.

Is there a better way to achieve the same result with varset or staticvarset? Please let me know how can staticvarset can be used to convert csv file into a array.

You don't need any static variables and all that. Also no loops as in first post.

All you need to do is applying ASCII import and then calling your (extra) data via Foreign() or SetForeign() functions.

You have four variables in your example. So simply import them to OHLC fields of separate DB symbol such as nifsm_2015_data.

So your CSV data file name is nifsm_2015_data.csv.
Some of your file's columns (AFAICS) are

Date/Time,ipivsh1,ipivsl1,ipivlh1,ipivll1

So what you need to import is those 5 columns. The rest of the columns of your CSV file are skipped.

How to create format file you can read here (besides that article links to other ASCII relevant infos).

Your format file content would be something like the following one:

# nifsm_2015,01/01/15 9:00,0,0,5323.86,,,,,8,9,10,11,0,0,0,8311
$FORMAT Skip, Date_YMD, Time, Skip, Skip, Skip, Skip, Skip, Skip, Skip, Open, High, Low, Close, Skip, Skip, Skip, Skip
$SKIPLINES 0
$SEPARATOR ", "
$CONT 1
$AUTOADD 1
$DEBUG 1
$BREAKONERR 1

Then once data is imported you select symbol nifsm_2015 and via AFL you call your (extra) data by using this simple AFL.

dataSymbol = Name() + "_data";

SetForeign(dataSymbol, 1);
    ipivsh1=Open;
    ipivsl1=High;
    ipivlh1=Low;
    ipivll1=Close;
RestorePriceArrays();

Filter=1;
AddColumn( C, "Close" );
AddColumn( DateTime(), "Date", formatDateTime );
AddColumn(ipivsh1,"ipivsh1");
AddColumn(ipivsl1,"ipivsl1");
AddColumn(ipivlh1,"ipivlh1");
AddColumn(ipivll1,"ipivll1");

And that's it.

2 Likes

Awesome. That was an elegant solution.