Read CSV contents per cell

[Wolfiesaxah]

$CurrentRecord[1]  --->>    New York,"The best place to be, if you have the money",Larry

$CurrentRecord[2]  --->>    California,"The Beach, sand, and water",Carla

I am using _FileReadToArray as shown below. It reads the entire CSV file which I can access starting from $CurrentRecord[1] which contains the sample bove. My problem is I am not just trying to read the whole line. I want to be able to get some of the sub-contents like "New York" or "Carla". How do I do that? I tried $CurrentRecord[1][1] to try and get New York but that is obviously a fail. I can't split $CurrentRecord[1] by comma as well as you can see some of the sub-contents have comma within them so splitting using comma would mess things up.

 Local $sFileOpenDialog = FileOpenDialog("Select the CSV file.", @WindowsDir & "", "All (*.*)", 1)

 Local $CurrentRecord

 _FileReadToArray($sFileOpenDialog, $CurrentRecord)

 

 

I tried creating an array and doing a _ArrayPush($avArrayTarget, $CurrentRecord[1]) to insert $CurrentRecord[1] contents into avArrayTarget and try to access the sub-contents like New York by doing a avArrayTarget[1] but that failed too.

 

Any ideas on how to do this? I think I will need to make $CurrentRecord[1] turn into an array so the data it contains can be accessed as an array easily.

Edited February 18, 2015 by Wolfiesaxah

[mikell]

contains .......... ?

[kylomas]

Wolfiesaxah,

This should get you started...

#include <array.au3>
#include <file.au3>

Local $CurrentRecord
_FileReadToArray(@scriptdir & '\array_test.txt', $CurrentRecord)

_arraydisplay($CurrentRecord, 'This is what the array looks like')

;
; regexp modified from example by weaponx
;

local $aLine
for $1 = 1 to $CurrentRecord[0]
    $aLine = stringregexp($CurrentRecord[$1],'(.*?)(?:\,|$)(?=(?:[^\"]*\"[^\"]*\")*(?![^\"]*\"))',3)
    _arraydisplay($aLine,'Each line split by non quoted commas')
next

kylomas

edit: spelling

Edited February 19, 2015 by kylomas

[mikell]

a slightly different way...

for $1 = 1 to $CurrentRecord[0]
    $sLine = StringRegExpReplace($CurrentRecord[$i], '((?<="),|,(?="))', ";")
    $aLine = StringSplit($sLine, ";", 3)
    _arraydisplay($aLine,'Each line split by non quoted commas')
next

[kylomas]

@mikell - Nice, I knew it could be done like that but gave up on it...

[mikell]

Hmm it's a workaround, to make it work in more cases this is better

#Include <Array.au3>

$sContent = 'New York,test,"The best place to be, if you have the money",other test,"quoted",Larry'

$aresult = StringRegExp($sContent,'(?:".*?")+|(?<=,|^)[^,]*(?=,|$)', 3)
_ArrayDisplay($aresult)

[Wolfiesaxah]

 

a slightly different way...

for $1 = 1 to $CurrentRecord[0]
    $sLine = StringRegExpReplace($CurrentRecord[$i], '((?<="),|,(?="))', ";")
    $aLine = StringSplit($sLine, ";", 3)
    _arraydisplay($aLine,'Each line split by non quoted commas')
next

This doesn't seem to work for me. Probably because $CurrentRecord[0] is a string and not a count? My code stops at the for loop statement because "for $1 = 1 to $CurrentRecord[0]" is already true so the for loop automatically stops

[kylomas]

If you are using _FileReadToArray then offset 0 is a count.

[jdelaney]

for $1 = 0 to UBound($CurrentRecord)-1

[Wolfiesaxah]

 

Hmm it's a workaround, to make it work in more cases this is better

#Include <Array.au3>

$sContent = 'New York,test,"The best place to be, if you have the money",other test,"quoted",Larry'

$aresult = StringRegExp($sContent,'(?:".*?")+|(?<=,|^)[^,]*(?=,|$)', 3)
_ArrayDisplay($aresult)

This one worked great! It's a tough request but may I ask explanation on the stringregexp syntax you used? I wish to understand how to come up with something like this

[mikell]

Not tough at all 

A bit simplified but still working :

$aresult = StringRegExp($sContent,'(".*?")|(?<=,|^)[^,]*(?=,|$)', 3)
 

request :

(".*?")  :  groups of 0 or more characters inside quotes (lazy)
|   : or
(?<=,|^)  : preceded by a comma or start of string (lookbehind)
[^,]*        : 0 or more non-comma characters  (negated character class)
(?=,|$)   : followed by a comma or end of string  (lookahead)


 

[Wolfiesaxah]

Awesome! I'll take a note of that. How is this replacing those characters though when the description of stringregexp is only to "Check if a string fits a given regular expression pattern" Anyways, this was awesome, thanks much!

[mikell]

Concerning  StringRegExp the definition is correct but  you must consider its extended sense 

It's a very powerful tool ^^