Jump to content
Sign in to follow this  
lamko

CSV file to multidimensional array

Recommended Posts

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)

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
ProgAndy

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

CSV.au3


*GERMAN* [note: you are not allowed to remove author / modified info from my UDFs]My UDFs:[_SetImageBinaryToCtrl] [_TaskDialog] [AutoItObject] [Animated GIF (GDI+)] [ClipPut for Image] [FreeImage] [GDI32 UDFs] [GDIPlus Progressbar] [Hotkey-Selector] [Multiline Inputbox] [MySQL without ODBC] [RichEdit UDFs] [SpeechAPI Example] [WinHTTP]UDFs included in AutoIt: FTP_Ex (as FTPEx), _WinAPI_SetLayeredWindowAttributes

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
ProgAndy

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

Edited by ProgAndy

*GERMAN* [note: you are not allowed to remove author / modified info from my UDFs]My UDFs:[_SetImageBinaryToCtrl] [_TaskDialog] [AutoItObject] [Animated GIF (GDI+)] [ClipPut for Image] [FreeImage] [GDI32 UDFs] [GDIPlus Progressbar] [Hotkey-Selector] [Multiline Inputbox] [MySQL without ODBC] [RichEdit UDFs] [SpeechAPI Example] [WinHTTP]UDFs included in AutoIt: FTP_Ex (as FTPEx), _WinAPI_SetLayeredWindowAttributes

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
GEOSoft

Someday

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

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

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


George

Question about decompiling code? Read the decompiling FAQ and don't bother posting the question in the forums.

Be sure to read and follow the forum rules. -AKA the AutoIt Reading and Comprehension Skills test.***

The PCRE (Regular Expression) ToolKit for AutoIT - (Updated Oct 20, 2011 ver:3.0.1.13) - Please update your current version before filing any bug reports. The installer now includes both 32 and 64 bit versions. No change in version number.

Visit my Blog .. currently not active but it will soon be resplendent with news and views. Also please remove any links you may have to my website. it is soon to be closed and replaced with something else.

"Old age and treachery will always overcome youth and skill!"

Share this post


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

George

Question about decompiling code? Read the decompiling FAQ and don't bother posting the question in the forums.

Be sure to read and follow the forum rules. -AKA the AutoIt Reading and Comprehension Skills test.***

The PCRE (Regular Expression) ToolKit for AutoIT - (Updated Oct 20, 2011 ver:3.0.1.13) - Please update your current version before filing any bug reports. The installer now includes both 32 and 64 bit versions. No change in version number.

Visit my Blog .. currently not active but it will soon be resplendent with news and views. Also please remove any links you may have to my website. it is soon to be closed and replaced with something else.

"Old age and treachery will always overcome youth and skill!"

Share this post


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

Share this post


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

Share this post


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

Share this post


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


"Never mistake kindness for weakness."-- Author Unknown --"The highest point to which a weak but experienced mind can rise is detecting the weakness of better men."-- Georg Lichtenberg --Simple Obfuscator (Beta not needed.), Random names for Vars and Funcs

Share this post


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

 

Share this post


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

Share this post


Link to post
Share on other sites
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 by czardas

Share this post


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

Share this post


Link to post
Share on other sites
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 by czardas

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  

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.