Convert CSV into two arrays

[starpc]

I know how to import a file into array however I have no clue how to read a single csv file into two different arrays below is my csv

bob,Bob

tim,Tim

neil*diamond,Neil Diamond

so array one should contain bob, tim, and neil*diamond

While array two contains Bob, Tim, and Neil Diamond

any ideas?

[SmOke_N]

Here's a structure you can look at to study (You'd uncomment the FileRead and remove the other $sString I used as an example):

#include <array.au3>

Global $sString, $aLineArray, $aSplit, $nCount
;$sString = FileRead("CSV.csv")
$sString = "bob,Bob" & @CRLF & "tim,Tim" & @CRLF & "neil*diamond,Neil Diamond"
;Create an array of each line
$aLineArray = StringSplit(StringStripCR($sString), @LF)

;Create a 2 dimensional array
Global $avArray[UBound($aLineArray)][2]
$avArray[0][0] = UBound($aLineArray) -1

;Parse our array and store in 2D array
For $iCC = 1 To $avArray[0][0]
    ;Split each line of file with delimeter
    If StringInStr($aLineArray[$iCC], ",") Then
        $nCount += 1
        $aSplit = StringSplit($aLineArray[$iCC], ",")
        $avArray[$nCount][0] = $aSplit[1]
        $avArray[$nCount][1] = $aSplit[2]
    EndIf
Next

;If a line was found with no delimeter, we Redim
If $nCount <> $avArray[0][0] Then 
    ReDim $avArray[$nCount + 1][2]
    $avArray[0][0] = $nCount
EndIf

;[number][0] = First part before delimeter
;[number][1] = Second part after delimeter
_ArrayDisplay($avArray, "Here is your 2 Dimensional array")

Edit:

Over thought something I think, re-coded a bit.

Edited October 25, 2007 by SmOke_N

[starpc]

thanks, worked like a charm

[Teldin]

You the man Smoke. You solved my problem also:

http://www.autoitscript.com/forum/index.php?showtopic=65453

Thanks!

[Teldin]

but one weird thing...

When I try to get rid of the first element, I get an error:

C:\PROGRA~1\AutoIt3\Include\array.au3 (144) : ==> Array variable has incorrect number of subscripts or subscript dimension range exceeded.:

$avNewArray[$iCntr - 1] = $avArray[$iCntr]

$avNewArray[$iCntr - 1] = ^ ERROR

->15:15:49 AutoIT3.exe ended.rc:1

Only thing I add is

$avArray2 = $avArray

_ArrayDelete($avArray2,0)

Without those two lines, it works great.

[SmOke_N]

but one weird thing...

When I try to get rid of the first element, I get an error:

C:\PROGRA~1\AutoIt3\Include\array.au3 (144) : ==> Array variable has incorrect number of subscripts or subscript dimension range exceeded.:

$avNewArray[$iCntr - 1] = $avArray[$iCntr]

$avNewArray[$iCntr - 1] = ^ ERROR

->15:15:49 AutoIT3.exe ended.rc:1

Only thing I add is

$avArray2 = $avArray

_ArrayDelete($avArray2,0)

Without those two lines, it works great.

No idea where you even have those lines... you probably should provide test code with your issue though.

[Teldin]

Using your example, I can't even delete the first element with _ArrayDelete (second line up from the bottom). I get the above error.

Test it on your system:

#include <array.au3>

Global $sString, $aLineArray, $aSplit, $nCount
;$sString = FileRead("CSV.csv")
$sString = "bob,Bob" & @CRLF & "tim,Tim" & @CRLF & "neil*diamond,Neil Diamond"
;Create an array of each line
$aLineArray = StringSplit(StringStripCR($sString), @LF)

;Create a 2 dimensional array
Global $avArray[UBound($aLineArray)][2]
$avArray[0][0] = UBound($aLineArray) -1

;Parse our array and store in 2D array
For $iCC = 1 To $avArray[0][0]
    ;Split each line of file with delimeter
    If StringInStr($aLineArray[$iCC], ",") Then
        $nCount += 1
        $aSplit = StringSplit($aLineArray[$iCC], ",")
        $avArray[$nCount][0] = $aSplit[1]
        $avArray[$nCount][1] = $aSplit[2]
    EndIf
Next

;If a line was found with no delimeter, we Redim
If $nCount <> $avArray[0][0] Then 
    ReDim $avArray[$nCount + 1][2]
    $avArray[0][0] = $nCount
EndIf

;[number][0] = First part before delimeter
;[number][1] = Second part after delimeter

_ArrayDelete($avArray,0)
_ArrayDisplay($avArray, "Here is your 2 Dimensional array")

[SmOke_N]

If memory serves me right... _ArrayDelete() only deletes 1 dimensional arrays... as you can see with that example I posted, Mine are 2 dimensional.

You'll have to find another way to do it, maybe look to see if someone wrote an _ArrayDelete2D or something...

Just checked my snippets, I have this I wrote a while back, hadn't tested it to see if it's working properly or not:

Func _ArrayDelete2D(ByRef $avArray, $iElement, $iIndex = 1)
    If $iIndex = 1 Then
        Local $aNewArray[UBound($avArray, 1) -1][UBound($avArray, 2)]
        For $iCC = 0 To $iElement - 1
            For $xCC = 0 To UBound($avArray, 2) - 1
                $aNewArray[$iCC][$xCC] = $avArray[$iCC][$xCC]
            Next
        Next
        For $iCC = $iElement + 1 To UBound($avArray, 1) -1
            For $xCC = 0 To UBound($avArray, 2) - 1
                $aNewArray[$iCC -1][$xCC] = $avArray[$iCC][$xCC]
            Next
        Next
    Else
        Local $aNewArray[UBound($avArray, 1)][UBound($avArray, 2) - 1]
        For $xCC = 0 To $iElement - 1
            For $iCC = 0 To UBound($avArray, 1) - 1
                $aNewArray[$iCC][$xCC] = $avArray[$iCC][$xCC]
            Next
        Next
        For $xCC = $iElement + 1 To UBound($avArray, 2) -1
            For $iCC = 0 To UBound($avArray, 1) - 1
                $aNewArray[$iCC][$xCC -1] = $avArray[$iCC][$xCC]
            Next
        Next
    EndIf
    $avArray = $aNewArray
    Return ''
EndFunc

Edited March 2, 2008 by SmOke_N

[Teldin]

That would make sense. Iirc, help file says nothing about not supporting 2d arrays.

I'll check yours out.

Thanks again Smoke!

[gazzer82]

Hi,

Sorry to resurrect an old thread. I am completely new to Autoit, and have been trying to modify this script to fit my needs with no success,

I am trying to get the script to read a CSV file into two arrays, the file is formatted as such.

22222222,

22222222,1

88888888,1

55555555,10

What i need it to do it read the two fields from each row and place them in an array. But i need it to detect when a field is empty such as in the first row above and insert a 1 by default.

I then need to be able to output the numbers on the left using keystrokes the number of time stipulated on the right column.

I hope that makes sense, i have spent quite a while playing with this, but i am lacking some of the fundamental knowledge of how to access certain fields in the script as an integer to allow it to be compared in an if statement.

If you need any more info then let me know.

Thanks in advance.

Gareth

[PsaltyDS]

Sorry to resurrect an old thread. I am completely new to Autoit, and have been trying to modify this script to fit my needs with no success,

I am trying to get the script to read a CSV file into two arrays, the file is formatted as such.

22222222,

22222222,1

88888888,1

55555555,10

What i need it to do it read the two fields from each row and place them in an array. But i need it to detect when a field is empty such as in the first row above and insert a 1 by default.

Start with _FileReadToArray() to get all the lines in a 1D array.

Then create a new 1D of the same size.

Use a For/Next loop to walk through the first array, and on each element:

- String split the text on ","

- Save the first part back to the first array

- Save the second part (or 1) to the second array

Code that and post your script if you need more help.

[gazzer82]

Start with _FileReadToArray() to get all the lines in a 1D array.

Then create a new 1D of the same size.

Use a For/Next loop to walk through the first array, and on each element:

- String split the text on ","

- Save the first part back to the first array

- Save the second part (or 1) to the second array

Code that and post your script if you need more help.

Thanks for your reply, i have been trying to do as you suggested but with little success, i am able to successfully split everything up, but i can't seem to load it into the arrays correctly. I keep getting an error saying "Error = Subscript used with non-Array Variable".

Here is what i have so far.

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

Global $aFirst, $aLineArray, $aSecond, $aThird, $sString, $aSplit, $nCount, $nNunber, $avArray
;Read the File into a String
$sString = FileRead("C:\Barcode_Data\Data1.txt")

;Split each line of the string into the array
$aFirst = StringSplit(StringStripCR($sString), @LF)

For $iCC = 1 To $aFirst[0]
    ;Split each line of file with delimeter into two arrays
    If StringInStr($aFirst[$iCC], ",") Then
        $nCount += 1
        $aSplit = StringSplit($aFirst[$iCC], ",")
        $aSecond[$nCount] = $aSplit[1]
        $aThird[$nCount] = $aSplit[2]
    EndIf
Next
_ArrayDisplay($aSecond, "Barcodes Read")
Exit

Any help greatly appreciated!!

Cheers

Gareth

Edited November 3, 2009 by gazzer82

[PsaltyDS]

Thanks for your reply, i have been trying to do as you suggested but with little success, i am able to successfully split everything up, but i can't seem to load it into the arrays correctly. I keep getting an error saying "Error = Subscript used with non-Array Variable".

Here is what i have so far.

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

Global $aFirst, $aLineArray, $aSecond, $aThird, $sString, $aSplit, $nCount, $nNunber, $avArray
;Read the File into a String
$sString = FileRead("C:\Barcode_Data\Data1.txt")

;Split each line of the string into the array
$aFirst = StringSplit(StringStripCR($sString), @LF)

For $iCC = 1 To $aFirst[0]
    ;Split each line of file with delimeter into two arrays
    If StringInStr($aFirst[$iCC], ",") Then
        $nCount += 1
        $aSplit = StringSplit($aFirst[$iCC], ",")
        $aSecond[$nCount] = $aSplit[1]
        $aThird[$nCount] = $aSplit[2]
    EndIf
Next
_ArrayDisplay($aSecond, "Barcodes Read")
Exit

Any help greatly appreciated!!

Cheers

Gareth

I meant more like this:

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

Global $sFile = "C:\Barcode_Data\Data1.txt"
Global $aLineArray[1], $aSecond[1], $aThird[1], $aSplit[1]

;Read the File into an array
_FileReadToArray($sFile, $aLineArray)
If @error = 0 Then
    _ArrayDisplay($aLineArray, "Debug: Read File To Array")
Else
    MsgBox(16, "Error", "Error reading file: " & $sFile)
    Exit
EndIf

ReDim $aSecond[$aLineArray[0] + 1] ; Make array size match
$aSecond[0] = $aLineArray[0]
ReDim $aThird[$aLineArray[0] + 1] ; Make array size match
$aThird[0] = $aLineArray[0]

For $n = 1 To $aLineArray[0]
    ;Split each line of the file into an array
    $aSplit = StringSplit($aLineArray[$n], ",")
    
    ; Put parts in arrays
    If IsArray($aSplit) And $aSplit[0] >= 2 Then
        $aSecond[$n] = $aSplit[1]
        $aThird[$n] = $aSplit[2]
    EndIf
Next

_ArrayDisplay($aSecond, "Debug: $aSecond")
_ArrayDisplay($aThird, "Debug: $aThird")

[gazzer82]

I meant more like this:

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

Global $sFile = "C:\Barcode_Data\Data1.txt"
Global $aLineArray[1], $aSecond[1], $aThird[1], $aSplit[1]

;Read the File into an array
_FileReadToArray($sFile, $aLineArray)
If @error = 0 Then
    _ArrayDisplay($aLineArray, "Debug: Read File To Array")
Else
    MsgBox(16, "Error", "Error reading file: " & $sFile)
    Exit
EndIf

ReDim $aSecond[$aLineArray[0] + 1] ; Make array size match
$aSecond[0] = $aLineArray[0]
ReDim $aThird[$aLineArray[0] + 1] ; Make array size match
$aThird[0] = $aLineArray[0]

For $n = 1 To $aLineArray[0]
    ;Split each line of the file into an array
    $aSplit = StringSplit($aLineArray[$n], ",")
    
    ; Put parts in arrays
    If IsArray($aSplit) And $aSplit[0] >= 2 Then
        $aSecond[$n] = $aSplit[1]
        $aThird[$n] = $aSplit[2]
    EndIf
Next

_ArrayDisplay($aSecond, "Debug: $aSecond")
_ArrayDisplay($aThird, "Debug: $aThird")

Ah i see, sorry i am very new at this and having issues getting my head around the syntax. That seems to work, i have adjusted the script to look for blank fields and fill them in with a "0" does this look like the best way of doing it?

expandcollapse popup

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

Global $sFile = "C:\Barcode_Data\Data1.txt"
Global $aLineArray[1], $aSecond[1], $aThird[1], $aSplit[1]

;Read the File into an array
_FileReadToArray($sFile, $aLineArray)
If @error = 0 Then
    _ArrayDisplay($aLineArray, "Debug: Read File To Array")
Else
    MsgBox(16, "Error", "Error reading file: " & $sFile)
    Exit
EndIf

ReDim $aSecond[$aLineArray[0] + 1] ; Make array size match
$aSecond[0] = $aLineArray[0]
ReDim $aThird[$aLineArray[0] + 1] ; Make array size match
$aThird[0] = $aLineArray[0]

For $n = 1 To $aLineArray[0]
    ;Split each line of the file into an array
    $aSplit = StringSplit($aLineArray[$n], ",")

    ; Put parts in arrays
    If IsArray($aSplit) And $aSplit[0] >= 2 Then
        $aSecond[$n] = $aSplit[1]
        if $aSplit[2] = "  " then
            $aThird[$n] = "100"
        else
            $aThird[$n] = $aSplit[2]
        EndIf
    EndIf
Next

_ArrayDisplay($aSecond, "Debug: $aSecond")
_ArrayDisplay($aThird, "Debug: $aThird")

[gazzer82]

Ok thanks, it seems to be ok with the sample data i am using, now onto the next problem (would it be better if i started a new thread?) i am trying to print the list to screen, i have tried using the following code but i am getting the same error message as previously again. I know i am probably doing something very obviously wrong but i just can't work it out.

expandcollapse popup

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

Global $sFile = "C:\Barcode_Data\Data1.txt"
Global $aLineArray[1], $aSecond[1], $aThird[1], $aSplit[1], $sBarcode, $sQuantity

;Read the File into an array
_FileReadToArray($sFile, $aLineArray)
If @error = 0 Then
    ;_ArrayDisplay($aLineArray, "Debug: Read File To Array")
Else
    MsgBox(16, "Error", "Error reading file: " & $sFile)
    Exit
EndIf

ReDim $aSecond[$aLineArray[0] + 1] ; Make array size match
$aSecond[0] = $aLineArray[0]
ReDim $aThird[$aLineArray[0] + 1] ; Make array size match
$aThird[0] = $aLineArray[0]

For $n = 1 To $aLineArray[0]
    ;Split each line of the file into an array
    $aSplit = StringSplit($aLineArray[$n], ",")

    ; Put parts in arrays
    If IsArray($aSplit) And $aSplit[0] >= 2 Then
        $aSecond[$n] = $aSplit[1]
        if $aSplit[2] = "  " then
            $aThird[$n] = "100"
        else
            $aThird[$n] = $aSplit[2]
        EndIf
    EndIf
Next

;_ArrayDisplay($aSecond, "Debug: $aSecond")
;_ArrayDisplay($aThird, "Debug: $aThird")

If Not WinActive("Untitled","") Then WinActivate("Untitled","")

For $n = 1 To $aLineArray[0]
$sBarcode = $aSecond[$n]
$sQuantity = $aThird[$n]
Send ($sBarcode[1])
Send ($sQuantity[1])

Next
Exit

Thanks

Gareth

[PsaltyDS]

Ok thanks, it seems to be ok with the sample data i am using, now onto the next problem (would it be better if i started a new thread?) i am trying to print the list to screen, i have tried using the following code but i am getting the same error message as previously again. I know i am probably doing something very obviously wrong but i just can't work it out.

expandcollapse popup

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

Global $sFile = "C:\Barcode_Data\Data1.txt"
Global $aLineArray[1], $aSecond[1], $aThird[1], $aSplit[1], $sBarcode, $sQuantity

;Read the File into an array
_FileReadToArray($sFile, $aLineArray)
If @error = 0 Then
    ;_ArrayDisplay($aLineArray, "Debug: Read File To Array")
Else
    MsgBox(16, "Error", "Error reading file: " & $sFile)
    Exit
EndIf

ReDim $aSecond[$aLineArray[0] + 1] ; Make array size match
$aSecond[0] = $aLineArray[0]
ReDim $aThird[$aLineArray[0] + 1] ; Make array size match
$aThird[0] = $aLineArray[0]

For $n = 1 To $aLineArray[0]
    ;Split each line of the file into an array
    $aSplit = StringSplit($aLineArray[$n], ",")

    ; Put parts in arrays
    If IsArray($aSplit) And $aSplit[0] >= 2 Then
        $aSecond[$n] = $aSplit[1]
        if $aSplit[2] = "  " then
            $aThird[$n] = "100"
        else
            $aThird[$n] = $aSplit[2]
        EndIf
    EndIf
Next

;_ArrayDisplay($aSecond, "Debug: $aSecond")
;_ArrayDisplay($aThird, "Debug: $aThird")

If Not WinActive("Untitled","") Then WinActivate("Untitled","")

For $n = 1 To $aLineArray[0]
$sBarcode = $aSecond[$n]
$sQuantity = $aThird[$n]
Send ($sBarcode[1])
Send ($sQuantity[1])

Next
Exit

Thanks

Gareth

Send() is a fall-back for when all else fails. If anything else takes focus on your desktop, it all goes wrong. You seem to be trying to simulate typing the data into Notepad. Don't bother. Write it directly to a file, then you use Notepad just to view it in the end:

Global $sOutputFile = @ScriptDir & "\OuputFile.txt"

; Rest of script...

    ; Put parts in arrays
    If IsArray($aSplit) And $aSplit[0] >= 2 Then
        $aSecond[$n] = $aSplit[1]
        if $aSplit[2] = "  " then
            $aThird[$n] = "100"
        else
            $aThird[$n] = $aSplit[2]
        EndIf
        
        ; Add to output
        FileWriteLine($sOutputFile, $aSecond[$n] & ", " & $aThird)
    EndIf

; Rest of script...

Run('Notepad.exe "' & $sOutputFile & '"')

[gazzer82]

Send() is a fall-back for when all else fails. If anything else takes focus on your desktop, it all goes wrong. You seem to be trying to simulate typing the data into Notepad. Don't bother. Write it directly to a file, then you use Notepad just to view it in the end:

Global $sOutputFile = @ScriptDir & "\OuputFile.txt"

; Rest of script...

    ; Put parts in arrays
    If IsArray($aSplit) And $aSplit[0] >= 2 Then
        $aSecond[$n] = $aSplit[1]
        if $aSplit[2] = "  " then
            $aThird[$n] = "100"
        else
            $aThird[$n] = $aSplit[2]
        EndIf
        
        ; Add to output
        FileWriteLine($sOutputFile, $aSecond[$n] & ", " & $aThird)
    EndIf

; Rest of script...

Run('Notepad.exe "' & $sOutputFile & '"')

Hi,

Thanks for that, actually Notepad is just a testing app, the send() will eventually go to our rental system, it only accepts keystrokes as in input method so i don't think writing to a file is going to work. Loosing window focus i don't think will be a problem as this script will be run in specific situations when booking out a job.

Could you explain why i am getting the error i mentioned above in my script, as far as i can tell the syntax for writing the array variable into the string variable is correct, but i keep getting the error.

Error: Array variable has incorrect number of subscripts or subscript dimension range exceeded.

Here is the script as it stands.

expandcollapse popup

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

Global $sFile = "C:\Barcode_Data\Data1.txt"
Global $aLineArray[1], $aSecond[1], $aThird[1], $aSplit[1], $sBarcode, $sQuantity

;Read the File into an array
_FileReadToArray($sFile, $aLineArray)
If @error = 0 Then
    ;_ArrayDisplay($aLineArray, "Debug: Read File To Array")
Else
    MsgBox(16, "Error", "Error reading file: " & $sFile)
    Exit
EndIf

ReDim $aSecond[$aLineArray[0] + 1] ; Make array size match
$aSecond[0] = $aLineArray[0]
ReDim $aThird[$aLineArray[0] + 1] ; Make array size match
$aThird[0] = $aLineArray[0]

For $n = 1 To $aLineArray[0]
    ;Split each line of the file into an array
    $aSplit = StringSplit($aLineArray[$n], ",")

    ; Put parts in arrays
    If IsArray($aSplit) And $aSplit[0] >= 2 Then
        $aSecond[$n] = $aSplit[1]
        if $aSplit[2] = "  " then
            $aThird[$n] = "100"
        else
            $aThird[$n] = $aSplit[2]
        EndIf
    EndIf
Next

;_ArrayDisplay($aSecond, "Debug: $aSecond")
;_ArrayDisplay($aThird, "Debug: $aThird")

If Not WinActive("RentalPoint","") Then WinActivate("RentalPoint","")

Thanks

Gareth

For $n2 = 1 To $aLineArray[0]
$sBarcode = $aSecond[$n2]
$sQuantity = $aThird[$n2]
Send ($sBarcode[1])
Send ($sQuantity[1])
Next
Exit

[Authenticity]

For $n2 = 1 To $aLineArray[0]
$sBarcode = $aSecond[$n2]
$sQuantity = $aThird[$n2]
Send ($sBarcode[1])
Send ($sQuantity[1])
Next
Exit

It seems that neither $aSecond, nor $aThird contain an array element which is an array type. $sBarcode and $sQuantity should be a string type. Can you post the error as it's logged into to console? (i.e. the offending line, etc..)

[PsaltyDS]

Could you explain why i am getting the error i mentioned above in my script, as far as i can tell the syntax for writing the array variable into the string variable is correct, but i keep getting the error.

Error: Array variable has incorrect number of subscripts or subscript dimension range exceeded.

As Authenticity said, should be (without array indexes):

Send ($sBarcode)
Send ($sQuantity)