Using string functions to read in the contents of a CSV file

Posted By: boatman

Using string functions to read in the contents of a CSV file - 08/24/15 07:46

I'm trying to read in the contents of a CSV file and use those contents to set up certain parameters in a script. I've modeled my script after the example on the Data Import/Export page in the manual - Data Import-Export - but am having trouble with the sscanf function. I would like to read the floating point numbers in the 4th and 6th columns, but my script seems to return values of 0 and results in Error 111: Crash in script. I have set the data type to float as per the manual.

Running Zorro in diagnostics mode doesn't reveal the reason for the crash,but when I comment out the sscanf lines, the crash doesn't happen.

Any suggestions? I have just started learning about string operations in C, so the error might be obvious to an advanced user.
Here is my script and CSV file:

Code:

string Name = "Data\\tradeList.csv";  // name of the CSV file

string readTrade(string csv,
	string*	tAsset,
	string*	tType,
	float*	tLots,
	float* tStop
	)
{
	string nextline = strstr(csv,"\n");
	if(nextline) nextline++;
	
	string separator = strstr(csv,",");
	if(separator) separator = ",";
	else separator = ";"; 
		
	*tAsset = strtok(csv,separator); //symbol
	if(!*tAsset) return nextline;
	strtok(0,separator); // expiry
	*tType = strtok(0,separator); // action: buy or sell
	sscanf(strtok(0,separator),"%f", &tLots); //quantity in standard lots
	strtok(0,separator); // order type
	sscanf(strtok(0,separator),"%f", &tStop); //aux price
	
	return nextline;
}
	
function run()
{
	set(LOGFILE);
//	StartDate = STARTDATE;
	LookBack = 0;
	if(is(LOOKBACK) || is(TRADING))
		return;
		
	if(!file_date(Name))
		quit("File not found!");
	string content = file_content(Name);
	string tAsset = "",tType = "";
	float tLots;
	float tStop;
		
	while(content) {
		content = readTrade(content,&tAsset,&tType,&tLots,&tStop);
		printf("\n%s %s %f %f", tAsset, tType, tLots, tStop);
		}

}

Attached File

tradeList.rar (7 downloads)

Posted By: boatman

Re: Using string functions to read in the contents of a CSV file - 08/25/15 12:11

Here's what the contents of the CSV looks like:

Symbol,Expiry,Action,Quantity,Order Type,Aux Price
AUDUSD,201508,BUY,0.07,STP,0.737
AUDUSD,201508,SELL,0.07,MKT CLOSE,
AUDUSD,201508,SELL,0.07,STP,0.7327
AUDUSD,201508,SELL,0.07,STP,0.7327
AUDUSD,201508,BUY,0.07,MKT CLOSE,
AUDUSD,201508,BUY,0.07,STP,0.737
EURUSD,201508,BUY,0.02,STP,1.114
EURUSD,201508,SELL,0.02,MKT CLOSE,
EURUSD,201508,SELL,0.02,STP,1.1032
EURUSD,201508,SELL,0.02,STP,1.1032

Posted By: jcl

Re: Using string functions to read in the contents of a CSV file - 08/25/15 15:02

I believe the correct syntax is

sscanf(strtok(0,separator),"%f", tLots);

and

sscanf(strtok(0,separator),"%f", tStop);

as tLots and tStop are already float pointers.

Posted By: boatman

Re: Using string functions to read in the contents of a CSV file - 08/25/15 15:29

Thanks for responding jcl. I corrected the syntax as per your suggestion, but got the same result as before making the change.

Would you mind trying to replicate this result?

Posted By: jcl

Re: Using string functions to read in the contents of a CSV file - 08/25/15 16:25

I haven't tried it yet, but see another problem in your CSV file: The "MKT CLOSE" lines contain no aux price, but the script tries to read a nonexistent price.

Don't attempt to program everything at once. Write a program step by step, and test every step. In this case, first write a simple version that only reads a single line. For this you can just comment out the while loop. If that works, extend it to reading multiple lines, then check the "MKT CLOSE" case.

Edit: the printf line has also a problem, you must cast float to (var) for printing it.

Posted By: boatman

Re: Using string functions to read in the contents of a CSV file - 08/27/15 10:39

Update:

I have almost got this working, but have hit the point where I need some more help. If anyone can suggest a solution or see where I'm going wrong, I'd greatly appreciate some advice.

My CSV data's format has changed slightly:
AUD/USD,Buy 0.04 Lots at 0.7213 Stop.,- IF DONE -,Sell 0.04 Lots at 0.7113 Stop - OCO - MOC.
AUD/USD,Sell 0.04 Lots at 0.7023 Stop.,- IF DONE -,Buy 0.04 Lots at 0.7123 Stop - OCO - MOC.
EUR/USD,Buy 0.02 Lots at 1.1625 Stop.,- IF DONE -,Sell 0.02 Lots at 1.1444 Stop - OCO - MOC.
EUR/USD,Sell 0.02 Lots at 1.1220 Stop.,- IF DONE -,Buy 0.02 Lots at 1.1401 Stop - OCO - MOC.
USD/JPY,Buy 0.03 Lots at 121.6742 Stop.,- IF DONE -,Sell 0.03 Lots at 120.0531 Stop - OCO - MOC.
USD/JPY,Sell 0.03 Lots at 116.9908 Stop.,- IF DONE -,Buy 0.03 Lots at 118.6119 Stop - OCO - MOC.

I am now able to grab all of the values I need with the exception of the stop value in every second row (that is, the value before the phrase "Stop - OCO - MOC." in every second row).

I've got to say, I am at my wits end with this! I just can't work out why my code parses all the values except every other row's stop. I think it must be something to do with the data itself, but I can't see what. Here's my code:

Code:

string Name = "Data\\systematic.csv";  // name of the CSV file
string readTrade(string csv,
	string*	tAsset,
	string*	tType,
	float*	tLots,
	float* tEntry,
	float* tStop
	)
{
	string nextline = strstr(csv,"\n");
	if(nextline) nextline++;
//	else return nextline;
	
	string separator = strstr(csv,",");
	if(separator) separator = ",";
	else separator = ";"; 
		
	string s = strtok(csv,separator); //symbol
//		printf("\n%s", s);

	*tAsset = s; 
	*tType = strtok(NULL, " ");
//	printf("\n%s", *tType);
	sscanf(strtok(NULL," "), "%f", tLots);
//	printf("\n%f", *tLots);
	strtok(NULL, " "); //"Lots"
	strtok(NULL, " "); //"at"
	
	sscanf(strtok(NULL," "), "%f", tEntry); //Entry
	
	strtok(NULL,"separator"); //End of second column
	
	strtok(NULL,"separator"); //Third column
	
	strtok(NULL," "); //"Sell/Buy"
	strtok(NULL, " "); //"lots to close"
	strtok(NULL, " "); //"Lots"
	strtok(0, " "); //"at"
	
	sscanf(strtok(NULL," "), "%f", tStop); //Stop

	return nextline;
}
	
function run()
{
	set(LOGFILE);
	Verbose = 30;
	LookBack = 0;
	Hedge = 2;
//	if(is(LOOKBACK) || is(TRADING))
//		return;
		
	if(!file_date(Name))
		quit("File not found!");
	string content = file_content(Name);
	string tAsset = "", tType = "";
	float tLots;
	float tEntry;
	float tStop;
			
	while(content) { 
		content = readTrade(content,&tAsset,&tType,&tLots,&tEntry,&tStop);
printf("\n%s, %f, %f", tAsset, (var)tEntry, (var)tStop);
		}
		

}

And the output of the printf - each row should reflect a row in the original CSV, but the second number is not updated.
[1: Mon 04.01.10 00:00] 83.150-83.120-83.230-83.170
AUD/USD, 0.721300, 0.711300
AUD/USD, 0.702300, 0.711300
EUR/USD, 1.162500, 1.144400
EUR/USD, 1.122000, 1.144400
USD/JPY, 121.674202, 120.053101
USD/JPY, 116.990799, 120.053101
GBP/USD, 1.563300, 1.552500
GBP/USD, 1.548500, 1.552500
USD/CAD, 1.341800, 1.328500
USD/CAD, 1.321800, 1.328500
EUR/JPY, 137.117203, 135.667801
EUR/JPY, 135.667801, 135.667801

Posted By: jcl

Re: Using string functions to read in the contents of a CSV file - 08/27/15 14:07

These lines are wrong:

strtok(NULL,"separator");

strtok is not looking for the separator, but for the text "separator", and of course does not find it. This causes your line parsing becoming out of sync at every other line.

You can find out such issues by looking at the parameters, f.i.

string s = strtok(0, " "); //"at"
printf("\n%s",s);

If it does not print "at", something went wrong before.

Posted By: boatman

Re: Using string functions to read in the contents of a CSV file - 08/27/15 14:56

Thanks for taking a look jcl. I applied your fix and the script is now extracting the values perfectly.

Thanks also for the tip about the printf statement. I actually did do this but still couldn't figure what the error was. I'm such a noob.