CSV file to multidimensional array

[lamko]

I would like to import a CSV file to an array. The problem i'm facing is that I can't read a single value from a array only a whole line. If that is working then I would create a multidimensional array.

start file

test, test, test

test1, test2, test3

end file

#include <file.au3>
Dim $aRecords, $lines
If Not _FileReadToArray("error.txt",$aRecords) Then
   MsgBox(4096,"Error", " Error reading log to Array     error:" & @error)
   Exit
EndIf
For $x = 1 to $aRecords[0]
    Msgbox(0,'Record:' & $x, $aRecords[$x])
        $Columns = StringSplit($lines, ",")
        for $y = 1 to $Columns[0] 
            MsgBox(0,"Calculate # of Dimensions", "How many: " & $y)
            MsgBox(0,"Calculate # of Dimensions", "How many: " & $Columns[$y])
        next
Next

aRecords[$x] contains values but then I would like to split to a single values.

But my example array $Columns[$y] doesn't even contain a value.

Second question

And after that I want to create from the two arrays one multidimensional array

I've found some code on the internet and would like to integrate my code with it and then create a multidimensional array

For example something like this : $array[linenr in file][first value on line]

$array[1][1] put on clipboard and paste in another program

Dim $lines,$Display, $NumCols
$lines = "Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday"
;---------------------------------------------------------------------
;Lets convert a comma delimited string into a single dimension array
;---------------------------------------------------------------------
$Columns = StringSplit($lines, ",")
$NumCols = $Columns[0]
MsgBox(0,"Calculate # of Dimensions", "How many: " & $Columns[0])
;---------------------------------------------------------------------
;Lets build a multidimensional Array with enough columns
;---------------------------------------------------------------------
Dim $array[2][ $Columns[0] ]
For $i = 1 To $Columns[0]
    $array[1][$i-1] = $Columns[$i]
Next
;---------------------------------------------------------------------
;Display comma delimited string to a multidimensional Array
;---------------------------------------------------------------------
For $j = 1 To $NumCols
    $Display = $Display & "array[1]["&String($j-1)&"]" & Chr(9) & " = " & chr(9) & $array[1][$j-1] & @CRLF
Next
MsgBox(4096, "Your Multidimensional Array",$Display)

[Juvigy]

If you have excel you can open the CSV file with excel and then read the imported info to a 2 dimensional array.

All that is available in the excel udf.

[ProgAndy]

Here are my CSV-functions. they just could be a bit slow on larger files.

CSV.au3

[lamko]

Hmm, your code Andy is a bit complicated for me with regular expressions and other things. Sure I could use it as function and pass the parameters to it and don't understand a bit what's happening.

But for example could some code with _FileReadToArray or maybe filereadline and stringsplit also work with little coding effort ?

The result is maybe the same but I would like to see if it could be done with a little example so i have something more understandble where I could learn from.

[ProgAndy]

Then you should look at the CSV Editor by funkey. It contains the simpler function, too.

Edited May 14, 2010 by ProgAndy

[lamko]

Then you should look at the CSV Editor by funkey. It contains the simpler function, too.

Thx Andy, thats what I was looking for.

[GEOSoft]

Someday

Dim $array[2][ $Columns[0] ]

will come back to haunt you. It assumes 1 based arrays.

Dim $array[2][ Ubound($Columns)-1 ]

[GEOSoft]

If it's a comma delimited file then something like this should work as well.

$sStr = FileRead("somefile.csv")
;; Replace any group of vertical whitespace with commas
$sStr = StringRegExpReplace($sStr, "\v+", ",")
$aItems = StringSplit($sStr, ",")

If you want to do it on a line by line basis then

$sStr = StringStripCR(FileRead("somefile.csv"))
$aLines = StringSplit($sStr, @LF, 2);; returns a 0 based array where assumably the first row contains the column names

Then you can work however you want with the lines

If IsArray($aLines) Then
   StringRegExpReplace($aLines[0], ",", ",")
   If @Extended Then
      Dim $aItems[Ubound($aLines)][@extended]
      For $i = 0 To Ubound($aLines) -1
         ;; handle your array code here
      Next
   EndIf
EndIf

[JJC2]

This is with regards to the CSV.au3 include, which is totally awesome by the way.

However, it does have a bug (which I haven't solved yet).

If you delimiter is a @TAB and you have the following structure in your CSV:

line1@TABdata@TABdata

line2@TAB@TABdata

The result that comes back is:

line1 data data

line2 data

instead of the correct:

line1 data data

line2 data

I'm suspecting it is because of the RegExp where @TAB character (0x09) should be translated to \t . (or a repeat + or * somewhere) But still trying to hunt it down exactly. Will post if/when I find.

Hope this helps anyone that uses this library.

Thanks,

James

[JJC2]

OK I fixed it for my situation, not sure it is always going to be correct or not, only progAndy can answer that.

On line 42 of the CSV.au3 library there is this line:

Local $sPattern = StringReplace(StringReplace('(?m)(?:^|[,])h*(["](?:[^"]|["]{2})*["]|[^,rn]*)(v+)?',',', $srDelimiters, 0, 1),'"', $srQuote, 0, 1)

It uses the h (zero-length string). However this doesn't seem to be working on my system (64 bit Win7 professional). There is a fix though:

Local $sPattern = StringReplace(StringReplace('(?m)(?:^|[,])>*(["](?:[^"]|["]{2})*["]|[^,rn]*)(v+)?',',', $srDelimiters, 0, 1),'"', $srQuote, 0, 1)

Use the > (also zero-length string). This works fine and exactly as I expected the output. Now I have a blank cell in between where it should be.

Hope this helps someone.

James

[MinDFirE]

This is a simple way:

#include <array.au3>

#include <file.au3>

Dim $oneDarray

_FileReadToArray(@ScriptDir & "" & "CSVfile.txt", $oneDarray)

$columnsCounter = stringsplit($oneDarray[1],",")

Dim $twoDarray[$oneDarray[0] + 1][$columnsCounter[0] + 1]

For $x = 1 to ($oneDarray[0])

$oneRow = stringsplit($oneDarray[$x],",")

For $y = 1 to ($columnsCounter[0])

$twoDarray[$x][$y] = $oneRow[$y]

Next

Next

[taurus905]

This is a simple way:

#include <array.au3>

#include <file.au3>

Dim $oneDarray

_FileReadToArray(@ScriptDir & "" & "CSVfile.txt", $oneDarray)

$columnsCounter = stringsplit($oneDarray[1],",")

Dim $twoDarray[$oneDarray[0] + 1][$columnsCounter[0] + 1]

For $x = 1 to ($oneDarray[0])

$oneRow = stringsplit($oneDarray[$x],",")

For $y = 1 to ($columnsCounter[0])

$twoDarray[$x][$y] = $oneRow[$y]

Next

Next

MinDFirE,

Thank you for a very simple and elegant example. As always, before I wrote my own, I was searching the forum for a snippet which would take a text file and convert it into a 2-dimensional array. I really didn't find anything which met my basic needs. But what you posted was almost exactly what I was going to write before I started my search. So thank you for sharing your code. It is a great example for people who want to learn and understand what their script is really doing and not rely on a "blackbox" type of UDF.

taurus905

[newbie1410]

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

Dim $oneDarray
_FileReadToArray(@ScriptDir & "" & "\test.txt", $oneDarray,$FRTA_NOCOUNT)
For $y =0 To UBound($oneDarray) -1
   $columnsCounter = stringsplit($oneDarray[$y],",")
   _ArrayDisplay($columnsCounter, "1D array - count", Default, 8)
   MsgBox(0,"",UBound($columnsCounter))
   For $x= 1 To UBound($columnsCounter)-1
      Dim $RowCounter=$columnsCounter[$x]
      MsgBox(0,"",$RowCounter)
      
   Next
Next

 

[Cobraa]

On 5/14/2010 at 3:21 PM, ProgAndy said:

Here are my CSV-functions. they just could be a bit slow on larger files.

CSV.au3

 

I using this script:

$aArray_2D = _ParseCSV("./scenario1.csv", ",", '"', 0)

 

The CSV:

"action","delay"
"action1","10"
"action2","20"

The problem that the array is with 3 columns, should be 2.

What do I do wrong?

scenario1.csv

[czardas]

This is an old thread. Perhaps it would have been better to start a new topic and insert a link to this thread. Have you tried my _CSVSplit() function?
https://www.autoitscript.com/forum/topic/155748-csvsplit/
I have been using it for a few years now. See the example (reading csv from a file) in post #8.

Edited March 19, 2017 by czardas

[Cobraa]

1 hour ago, czardas said:

This is an old thread. Perhaps it would have been better to start a new topic and insert a link to this thread. Have you tried my _CSVSplit() function?
https://www.autoitscript.com/forum/topic/155748-csvsplit/
I have been using it for a few years now. See the example (reading csv from a file) in post #8.

 

Thank a lot.

But I don't see there function for: from CSV to Array

[czardas]

Here's the code you need (you have to read the file first):

#include <CSVSplit.au3>

Local $sFilePath = @ScriptDir & "\test.csv" ; Change this to your own csv file (in the same directory as the script)

Local $hFile = FileOpen($sFilePath)
If $hFile = -1 Then
    MsgBox(0, "", "Unable to open file")
    Exit
EndIf

Local $sCSV = FileRead($hFile)
If @error Then
    MsgBox(0, "", "Unable to read file")
    FileClose($hFile)
    Exit
EndIf
FileClose($hFile)

Local $aCSV = _CSVSplit($sCSV) ; Create the main array

Replace this part: . . . @ScriptDir & "\test.csv" . . . with your own file path.

Edit : You also need to save the code in the first post with the file name CSVSplit.au3 and place it in the same folder as your script, otherwise you will get errors.

Edited March 19, 2017 by czardas

[Cobraa]

Thank you! Works like a charm!