20 replies to this topic

[wvzuilen]

Hi all,

I got a question, but first I would like to thank all the posters of this forum. I've been coding AutoIT for a while now and I've used this forum a lot ! Thanks !
But now's I've got a question of wich I haven't find the answer so far.

I've got a folder with 50 CSV files and I would like to read those files to arrays. I would like to give the array the same name as the filename.
So filename1.csv to $filename1, filename2.csv to $filename2 etc.

My code so far:

#include <CSV.au3>
#include <File.au3>
#include <Array.au3>
Local $aCSV = _FileListToArray(@ScriptDir & "\CSV", "*", 1)
For $i = 1 To $aCSV[0]
$????? = _ParseCSV(@ScriptDir & "\CSV\" & $aCSV[$i])
Next

But how the declare / name the array within the loop?
"$" & StringTrimRight($aCSV[$i],4) doesn't work (of course).

[water]

Why do you want to have 50 arrays? Can't you just read a file to an array, process it and then read the next file to the same array, process it and so on ...

[wvzuilen]

Why do you want to have 50 arrays? Can't you just read a file to an array, process it and then read the next file to the same array, process it and so on ...

Nope, I need all the data to calculate a insurance premium.

[water]

Use function "Assign". Example:

#include <array.au3>
Global $array[3] = [1,2,3]
Global $array1
Assign("array1", $array)
_ArrayDisplay($array1)

[czardas]

Here's a good parser for csv. I also created a csv to array function. Here's the one I wrote: although I may have overlooked the case where a csv file does not contain the delimiter. I will check that now.

Edited by czardas, 01 June 2012 - 12:12 PM.

[water]

Hi czardas,
I think the OP got the CSV reading just right. What he needs is a way to dynamically name and create the receiving arrays.

[wvzuilen]

Use function "Assign". Example:

#include <array.au3>
Global $array[3] = [1,2,3]
Global $array1
Assign("array1", $array)
_ArrayDisplay($array1)

I tried this, but I cant get it to work:

#include <CSV.au3>
#include <File.au3>
#include <Array.au3>
Local $aCSV = _FileListToArray(@ScriptDir & "CSV", "*", 1)
For $i = 1 To $aCSV[0]
$filename = $aCSV[$i]
$aTemp = _ParseCSV(@ScriptDir & "CSV" & $filename) ;parse array to temp variable
$filename = StringTrimRight($filename,4) ; remove extension
Assign( $filename , $aTemp)
Next
_ArrayDisplay($aCSV)

[czardas]

Hi czardas,
I think the OP got the CSV reading just right. What he needs is a way to dynamically name and create the receiving arrays.

If it is working then fine. I posted some alternatives. I have tested them all. It turns out that I had not overlooked anything as I had previously thought.

Edit
As to the original question. How big are the files and could you use a 3D array?

Edited by czardas, 01 June 2012 - 12:06 PM.

[water]

#include <File.au3>
#include <Array.au3>
Local $aTemp[2] = [1, "Test"], $Test1, $Test2
Local $aCSV[3] = [2, "test1.csv", "test2.csv"]
For $i = 1 To $aCSV[0]
    $filename = $aCSV[$i]
    $filename = StringTrimRight($filename, 4) ; remove extension
    Assign($filename, $aTemp) ; Copy the array to the target array
    MsgBox(16, "Message", "Is $" & $filename & " an Array: " & IsArray(Eval($filename))) ; check if target is an array
Next
_ArrayDisplay($test1) ; Display first target array

You can't display one of the dynamically generated arrays because _ArrayDisplay uses ByRef.
This example "knows" that the resulting arrays are named test1 and test2.
The script tests the targets for being arrays and displays the first target array.

[wvzuilen]

#include <File.au3>
#include <Array.au3>
Local $aTemp[2] = [1, "Test"], $Test1, $Test2
Local $aCSV[3] = [2, "test1.csv", "test2.csv"]
For $i = 1 To $aCSV[0]
    $filename = $aCSV[$i]
    $filename = StringTrimRight($filename, 4) ; remove extension
    Assign($filename, $aTemp) ; Copy the array to the target array
    MsgBox(16, "Message", "Is $" & $filename & " an Array: " & IsArray(Eval($filename))) ; check if target is an array
Next
_ArrayDisplay($test1) ; Display first target array

You can't display one of the dynamically generated arrays because _ArrayDisplay uses ByRef.
This example "knows" that the resulting arrays are named test1 and test2.
The script tests the targets for being arrays and displays the first target array.

Thanks ! With this example I got it working !

AutoIt        expandcollapse  popup

#include <CSV.au3>
#include <File.au3>
#include <Array.au3>
Local $Cilinder_cat
Local $Clausule_reg
Local $Commercieel1
Local $CP_kanaal
Local $CP_leeft
Local $CP_Loy_Pntn
Local $CP_noclaim
Local $Dekking_OO
Local $Dichtheid
Local $Eigen_Risico_code
Local $Eigen_risico
Local $Heading
Local $Inhoudsopgave
Local $KM
Local $Land_code
Local $Leeftijd_Cat
Local $Merk
Local $Merk_clause
Local $No_Claim
Local $OAR
Local $PC_Dichtheid
Local $PC_Regio
Local $PF_Eigen_Risico
Local $Provincie
Local $Provisie
Local $Provisie_MAX
Local $Regio
Local $RF_Bouwjaar
Local $RF_Buitenl
Local $RF_Catalogus
Local $RF_Cilinder
Local $RF_Dichtheid
Local $RF_Eig_Risk
Local $RF_KM
Local $RF_Leeftijd
Local $RF_Merk
Local $RF_Provincie
Local $RF_Regio
Local $RF_Type_motor
Local $Risk
Local $Type
Local $Variabelen
Local $VB_Risk
Local $Voertuigsrt
Local $aCSV = _FileListToArray(@ScriptDir & "CSV", "*", 1)
For $i = 1 To $aCSV[0]
    $filename = $aCSV[$i]
Local $aTemp = _ParseCSV(@ScriptDir & "CSV" & $filename) ;parse array to temp variable
    $filename = StringTrimRight($filename, 4) ; remove extension
    Assign($filename, $aTemp) ; Copy the array to the target array
Next
_ArrayDisplay($Cilinder_cat) ; Display first target array

[water]

Glad you got it working

[Mechaflash]

Thanks ! With this example I got it working !

Spoiler

AutoIt        expandcollapse  popup

#include 
#include 
#include 
Local $Cilinder_cat
Local $Clausule_reg
Local $Commercieel1
Local $CP_kanaal
Local $CP_leeft
Local $CP_Loy_Pntn
Local $CP_noclaim
Local $Dekking_OO
Local $Dichtheid
Local $Eigen_Risico_code
Local $Eigen_risico
Local $Heading
Local $Inhoudsopgave
Local $KM
Local $Land_code
Local $Leeftijd_Cat
Local $Merk
Local $Merk_clause
Local $No_Claim
Local $OAR
Local $PC_Dichtheid
Local $PC_Regio
Local $PF_Eigen_Risico
Local $Provincie
Local $Provisie
Local $Provisie_MAX
Local $Regio
Local $RF_Bouwjaar
Local $RF_Buitenl
Local $RF_Catalogus
Local $RF_Cilinder
Local $RF_Dichtheid
Local $RF_Eig_Risk
Local $RF_KM
Local $RF_Leeftijd
Local $RF_Merk
Local $RF_Provincie
Local $RF_Regio
Local $RF_Type_motor
Local $Risk
Local $Type
Local $Variabelen
Local $VB_Risk
Local $Voertuigsrt
Local $aCSV = _FileListToArray(@ScriptDir & "CSV", "*", 1)
For $i = 1 To $aCSV[0]
    $filename = $aCSV[$i]
Local $aTemp = _ParseCSV(@ScriptDir & "CSV" & $filename) ;parse array to temp variable
    $filename = StringTrimRight($filename, 4) ; remove extension
    Assign($filename, $aTemp) ; Copy the array to the target array
Next
_ArrayDisplay($Cilinder_cat) ; Display first target array

[water]

The name of the Array is dynamically created from the filenames read by _FileListToArray and then filled by _ParseCSV.

[czardas]

water, if the function _ParseCSV is the one I'm thinking of, then it contains a bug. One that is easy to fix though.

Edited by czardas, 01 June 2012 - 06:15 PM.

[water]

czardas,
the one I have in mind is this one written by ProgAndy which I also use in my OutlookEX UDF.
If there is a bug I would surely like to hear about it.

[czardas]

I think the UDF is in many ways superior having Unicode support, and that ProgAndy is an awesome programmer. The following csv data taken from http://en.wikipedia.org/wiki/Comma-separated_values#Example returns 4 rows when it should return 5 rows. It also seems to return an extra column, I'm not sure if that is intentional.

Year,Make,Model,Description,Price
1997,Ford,E350,"ac, abs, moon",3000.00
1999,Chevy,"Venture ""Extended Edition""","",4900.00
1999,Chevy,"Venture ""Extended Edition, Very Large""","",5000.00
1996,Jeep,Grand Cherokee,"MUST SELL!
air, moon roof, loaded",4799.00

ProgAndy sent me an alternative function by PM earlier.:

AutoIt        expandcollapse  popup

Func _ParseCSV($sFile, $sDelimiters=',;', $sQuote='"', $iFormat=0)
    Local Static $aEncoding[6] = [0, 0, 32, 64, 128, 256]
    If $iFormat < -1 Or $iFormat > 6 Then
        Return SetError(3,0,0)
    ElseIf $iFormat > -1 Then
        Local $hFile = FileOpen($sFile, $aEncoding[$iFormat]), $sLine, $aTemp, $aCSV[1], $iReserved, $iCount
        If @error Then Return SetError(1,@error,0)
        $sFile = FileRead($hFile)
        FileClose($hFile)
    EndIf
    If $sDelimiters = "" Or IsKeyword($sDelimiters) Then $sDelimiters = ',;'
    If $sQuote = "" Or IsKeyword($sQuote) Then $sQuote = '"'
    $sQuote = StringLeft($sQuote, 1)
    Local $srDelimiters = StringRegExpReplace($sDelimiters, '[^-[]]', '0')
    Local $srQuote = StringRegExpReplace($sQuote, '[^-[]]', '0')
    Local $sPattern = StringReplace(StringReplace('(?m)(?:^|[,])h*(["](?:[^"]|["]{2})*["]|[^,rn]*)(v+)?',',', $srDelimiters, 0, 1),'"', $srQuote, 0, 1)
    Local $aREgex = StringRegExp($sFile, $sPattern, 3)
    If @error Then Return SetError(2,@error,0)
    $sFile = '' ; save memory
    Local $iBound = UBound($aREgex), $iIndex=0, $iSubBound = 1, $iSub = 0
    Local $aResult[$iBound][$iSubBound], $fAdded = False
    For $i = 0 To $iBound-1
        Select
            Case StringLen($aREgex[$i])<3 And StringInStr(@CRLF, $aREgex[$i])
                $fAdded = False
                $iIndex += 1
                $iSub = 0
                ContinueLoop
            Case StringLeft(StringStripWS($aREgex[$i], 1),1)=$sQuote
                $fAdded = True
                $aREgex[$i] = StringStripWS($aREgex[$i], 3)
                $aResult[$iIndex][$iSub] = StringReplace(StringMid($aREgex[$i], 2, StringLen($aREgex[$i])-2), $sQuote&$sQuote, $sQuote, 0, 1)
            Case Else
                $fAdded = True
                $aResult[$iIndex][$iSub] = $aREgex[$i]
        EndSelect
        $aREgex[$i]=0 ; save memory
        $iSub += 1
        If $iSub = $iSubBound Then
            $iSubBound += 1
            ReDim $aResult[$iBound][$iSubBound]
        EndIf
    Next
    If $iIndex = 0 Then $iIndex=1
    ReDim $aResult[$iIndex+$fAdded][$iSubBound]
    Return $aResult
EndFunc

Edited by czardas, 01 June 2012 - 06:29 PM.

[AdmiralAlkex]

Assign() is ugly. Use an array instead. It will be simpler too.

[Newb]

Assign rocks.... never knew about it and have been searching for it in a while

It's a lately implemented function or it exists since lot of time?

[wvzuilen]

I think the UDF is in many ways superior having Unicode support, and that ProgAndy is an awesome programmer. The following csv data taken from http://en.wikipedia.org/wiki/Comma-separated_values#Example returns 4 rows when it should return 5 rows. It also seems to return an extra column, I'm not sure if that is intentional.

I noticed that, I edited one of my CSV files and removed a trailing, useless linefeed. But the array created by ProgAndy's _ParseCSV then missed a row. So I added the linefeed again

[czardas]

Yeah probably best to leave the final Line Feed alone. I didn't realize it earlier, but that was the problem.

Edited by czardas, 01 June 2012 - 07:04 PM.