Sign in to follow this  
Followers 0
Wolfiesaxah

Read CSV contents per cell

13 posts in this topic

#1 ·  Posted (edited)

$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 by Wolfiesaxah

Share this post


Link to post
Share on other sites



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

Share this post


Link to post
Share on other sites

#3 ·  Posted (edited)

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 by kylomas

Forum Rules         Procedure for posting code

"I like pigs.  Dogs look up to us.  Cats look down on us.  Pigs treat us as equals."

- Sir Winston Churchill

Share this post


Link to post
Share on other sites

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


Forum Rules         Procedure for posting code

"I like pigs.  Dogs look up to us.  Cats look down on us.  Pigs treat us as equals."

- Sir Winston Churchill

Share this post


Link to post
Share on other sites

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)

Share this post


Link to post
Share on other sites

 

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

Share this post


Link to post
Share on other sites

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


Forum Rules         Procedure for posting code

"I like pigs.  Dogs look up to us.  Cats look down on us.  Pigs treat us as equals."

- Sir Winston Churchill

Share this post


Link to post
Share on other sites

 

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 :D

Share this post


Link to post
Share on other sites

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)


 

Share this post


Link to post
Share on other sites

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" :D Anyways, this was awesome, thanks much!

Share this post


Link to post
Share on other sites

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

It's a very powerful tool ^^

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0