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:

Code: Select all

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]

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?

Well... if your data was the following...

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

...you could just use ; as your delimiter in the Separate> command and it would do what you want... yes?

So, just convert your data to this new format... then use Separate> on it.

Here's some pseudo code to do that:

- 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 us know how you do...

P.S. If your original data could possibly contain a ; character... use some other obscure character like vertical bar | etc.

[jpuziano]

Hi seott,

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

Code: Select all

/*
- 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

[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

[jpuziano]

Three steps:

1. Use a RegEx to replace commas inside double quotes with a tilde.

Hi Bob,

I see, you're modifying the commas inside the double quoted numbers... instead of the others.

Sounds good... but what if the data was:

Let>line=red,green,blue,"555,000,000",yellow,"22,000"

How would you code that regex pattern so it would target and replace the three commas inside the above numbers... without having it target the commas on either side of yellow?

Anyone?

[jpuziano]

Hi Bob,

Here's what I came up with...

Code: Select all

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:

Code: Select all

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

[Marcus Tettmar]

Why not use DBQuery to read a CSV file:

Code: Select all

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:

Code: Select all

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.

[Marcus Tettmar]

Here's the RegEx solution:

Code: Select all

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:

Code: Select all

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:

Code: Select all

//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

[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

[Marcus Tettmar]

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.

[jpuziano]

Thanks Marcus, great examples and blog post!