11 replies to this topic

[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

Plain Text        expandcollapse  popup

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.

Attached Files

•  CSV.au3   5.67KB   1158 downloads

[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 by ProgAndy, 14 May 2010 - 02:12 PM.

[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