Jump to content

Read CSV contents per cell

Go to solution Solved by mikell,

Recommended Posts

$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
Link to comment
Share on other sites


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')


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

Link to comment
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')

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

Link to comment
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)

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

Link to comment
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)


Link to comment
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

  • Recently Browsing   0 members

    • No registered users viewing this page.
  • Create New...