Array From A ReadLn

[seott]

Hi everyone, I love this program...

How do I create an array from a comma separated line in a file?

I'd like to run a ReadLn on say... line 2 of a file that would ready

Red,Green,Blue,Yellow

And I want to be able to say

let>variable= array[4]

Which would equal yellow. Anyone done this?

[seott]

Ah, figured it out, needed to use separate.

Another question though...

If I'm using separate to go through and using a comma as a delimiter - I have a currency in one of the fields that has double quotes around it...

so like

red,green,blue,"555,000,000"

How would I get it to treat the currency as one unit instead of three? Is there a way I can instruct it to leave items in double quotes alone?

[JRL]

Off the top of my head I don't have a generic solution for you. But, assuming that the format you've presented is fixed, you might do 2 separates> The first one would use ," (comma quote) as the delimiter and would divide the line in to two variables. The first variable value would be red,green,blue and could then be separated using a comma as the delimiter.

The second variable from the first Separate> would be the currency value plus one quote. Run StringReplace> on the variable to remove the quote.

Hope this is helpful.

The following is untested:

Let>comma=, Let>line=red,green,blue,"555,000,000" Separate>line,%comma%",var StringReplace>var_2,",,var_2 Separate>var_1,comma,color MDL>%color_1% %color_2% %color_3% %var_2%

[jpuziano]

[jpuziano]

Hi seott,

This is quick and dirty... but it works:

/* - process each line separately - for a flag, create a variable like Let>inside_double_quotes=0 - inspect each character of a line - if the char is " then toggle inside_double_quotes (if its 1 make it 0, if its 0 make it 1) - if the char is , then if inside_double_quotes=0 then replace , with ; otherwise leave it unchanged - if the char is anything else, leave it unchanged */ Let>line=red,green,blue,"555,000,000" Let>converted_line= Length>line,line_length Let>inside_double_quotes=0 Let>char_position=0 Let>comma=, Label>Loop Let>char_position=char_position+1 MidStr>line,char_position,1,char If>char=",Toggle_Flag If>char=%comma% If>inside_double_quotes=0 Let>char=; ENDIF ENDIF ConCat>converted_line,char If>char_position<line_length,Loop MDL>Converted format for the line is:%CRLF%%converted_line% Separate>converted_line,;,item MDL>%item_1%%CRLF%%item_2%%CRLF%%item_3%%CRLF%%item_4% SRT>Toggle_Flag IF>inside_double_quotes=0 Let>inside_double_quotes=1 ELSE Let>inside_double_quotes=0 ENDIF END>Toggle_Flag

You could extend this to process more lines, etc.

I would also reset inside_double_quotes to 0 each time you started processing a new line... in case your data ever contained an odd number of double quote " characters.

Take care
_________________
jpuziano

Note: When posting code to the forum... please:
- highlight the code then click the "Code" button
- check the "Disable HTML in this post" checkbox
- check the "Disable Smilies in this post" checkbox

[Bob Hansen]

Three steps:

1. Use a RegEx to replace commas inside double quotes with a tilde.
2. Use Separate as normal
3. Use StringReplace to replace tildes with commas on each separated value
_________________
Hope this was helpful..................good luck,
Bob
A humble man and PROUD of it!

[jpuziano]

[jpuziano]

Hi Bob,

Here's what I came up with...

Let>MSG_WIDTH=300 Let>text=red,green,blue,"555,000,000",yellow,"22,000" //tried to make it work for "n,n,n" and "n,n" using one RegEx replacement Let>pattern=[mustBeginWith('"')][capture(zeroOrMore digit)][','][capture(zeroOrMore digit)][optional ','][optional capture(zeroOrMore digit)][mustEndWith('"')] RegEx>pattern,text,1,matches,num,1,$1~$2~$3,new_text MDL>num=%num% //doesn't work, note the trailing ~ //we have no way to specify that ~$3 is optional in the replacement string //so even when $3 isn't there, the ~ gets added MessageModal>%matches_1% %matches_2% %new_text% //to make it work, I had to use two RegEx> commands using different patterns //This pattern matches numbers within double quotes containing commas of the form: "n,n,n" where n is zero or more digits Let>pattern=[mustBeginWith('"')][capture(zeroOrMore digit)][','][capture(zeroOrMore digit)][','][capture(zeroOrMore digit)][mustEndWith('"')] RegEx>pattern,text,1,matches,num,1,$1~$2~$3,new_text MDL>num=%num% MessageModal>%matches_1% %matches_2% %new_text% //This pattern matches numbers within double quotes containing commas of the form: "n,n" where n is zero or more digits Let>pattern=[mustBeginWith('"')][capture(zeroOrMore digit)][','][capture(zeroOrMore digit)][mustEndWith('"')] RegEx>pattern,new_text,1,matches,num,1,$1~$2,new_text MDL>num=%num% MessageModal>%matches_1% %matches_2% %new_text%

From the above, it follows that if you wanted to be able to handle numbers containing one comma, two commas, three commas, i.e. of the form:

n,n
n,n,n
n,n,n,n

...where n = any number of digits... then you'd need a separate RegEx command to handle each pattern.

Unless Bob, you know a pattern that can handle numbers containing any number of commas... and replace them with tilde... in just one RegEx call. If you do or anyone does, please share.

I like your approach though, far less code... and its fine if the numbers are all in the same range, say might contain one or two commas tops.

However the RegEx approach would fail if the data contained a number with more commas in it than you supplied patterns for... while my code should work on numbers of any size... try the following:

Let>MSG_WIDTH=500 Let>line=red,green,blue,"555,000,000",yellow,"111,222,333,444,555,666,777,888,999,000" Let>converted_line= Length>line,line_length Let>inside_double_quotes=0 Let>char_position=0 Let>comma=, Label>Loop Let>char_position=char_position+1 MidStr>line,char_position,1,char If>char=",Toggle_Flag If>char=%comma% If>inside_double_quotes=0 Let>char=; ENDIF ENDIF ConCat>converted_line,char If>char_position<line_length,Loop MDL>Converted format for the line is:%CRLF%%converted_line% Separate>converted_line,;,item Let>display_string= Let>ds_element=0 //build display string Label>DS_Loop Let>ds_element=ds_element+1 ConCat>display_string,% ConCat>display_string,item_ ConCat>display_string,%ds_element% ConCat>display_string,% ConCat>display_string,% ConCat>display_string,C ConCat>display_string,R ConCat>display_string,L ConCat>display_string,F ConCat>display_string,% If>ds_element<item_count,DS_Loop //MDL>%item_1%%CRLF%%item_2%%CRLF%%item_3%%CRLF%%item_4%%CRLF%%item_5%%CRLF%%item_6% MDL>%display_string% SRT>Toggle_Flag IF>inside_double_quotes=0 Let>inside_double_quotes=1 ELSE Let>inside_double_quotes=0 ENDIF END>Toggle_Flag

_________________
jpuziano

Note: When posting code to the forum... please:
- highlight the code then click the "Code" button
- check the "Disable HTML in this post" checkbox
- check the "Disable Smilies in this post" checkbox

[mtettmar]

Why not use DBQuery to read a CSV file:

Let>ConStr=Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\My Documents;Extended Properties='text;HDR=NO;FMT=Delimited' DBConnect>ConStr,dbH DBQuery>dbH,select * from test.csv,fields,num_recs,num_fields

That takes care of everything. Handles commas inside quotes etc.

Set Data Source to the path of the folder containing the CSV file. In DBQuery set the select statement to point to the CSV file in that folder. Set HDR to Yes if the first line of the CSV file is a header line and you don't want to return those values.

The following code loops through each field in each record:

Let>rec=0 Repeat>rec Let>rec=rec+1 Let>field=0 Repeat>field Let>field=field+1 Let>this_field=fields_%rec%_%field% MessageModal>this_field Until>field=num_fields Until>rec=num_recs

I'm tempted to try and find a RegEx solution as that seems a nice challenge. But it's probably rather pointless when DBQuery does the job for you in so few lines of code.
_________________
Regards,
Marcus Tettmar
http://mjtnet.com/blog/ | http://twitter.com/marcustettmar
Please do not email/PM me for private support - post to the forum so that everyone benefits. For private support please send email via our web site.

[mtettmar]

Here's the RegEx solution:

Let>text=sally,"1,2500",fred Let>pattern="[^"\r\n]*"|[^,\r\n]* RegEx>pattern,text,0,fields,num,0

You'll note that this returns:

sally
"1,2500"
fred

So we just need another RegEx to remove any starting and ending quotes from each field. So our loop would be:

Let>k=0 Repeat>k Let>k=k+1 Let>the_field=fields_%k% //RegEx to remove starting and ending quotes if present RegEx>^"|"$,the_field,0,matches,n,1,,the_field //Display the field MessageModal>the_field Until>k=num

So full code to create an array from a line of CSV and loop through each field:

//Example CSV line Let>text=sally,"1,2500",fred //RegEx to extract each field to an array Let>pattern="[^"\r\n]*"|[^,\r\n]* RegEx>pattern,text,0,fields,num,0 //Loop through each field Let>k=0 Repeat>k Let>k=k+1 Let>the_field=fields_%k% //RegEx to remove starting and ending quotes if present RegEx>^"|"$,the_field,0,matches,n,1,,the_field //Display the field MessageModal>the_field Until>k=num

_________________
Regards,
Marcus Tettmar
http://mjtnet.com/blog/ | http://twitter.com/marcustettmar
Please do not email/PM me for private support - post to the forum so that everyone benefits. For private support please send email via our web site.

[seott]

Wow! Thanks everyone! I'm glad to see everyone is so helpful on this forum. Those were a lot of great solutions. I'm going to brainstorm which is the best for this particular situation or might use a mixture of them but thank you it's been a big help and I know I can get there now.

[seott]

Marcus, BTW, Great Blog. I feel famous now

[seott]

Are any of these commands exclusive to version 11? I have version 10

[mtettmar]

RegEx was added in v11.
DBQuery is supported by v10 but the ability to return field names in DBQuery was added in v11.

So the first DBQuery example above will work in v10.
_________________
Regards,
Marcus Tettmar
http://mjtnet.com/blog/ | http://twitter.com/marcustettmar
Please do not email/PM me for private support - post to the forum so that everyone benefits. For private support please send email via our web site.

[jpuziano]

Thanks Marcus, great examples and blog post!
_________________
jpuziano

Note: When posting code to the forum... please:
- highlight the code then click the "Code" button
- check the "Disable HTML in this post" checkbox
- check the "Disable Smilies in this post" checkbox