Manipulating a CSV after import to array

[Chimaera]

Hi

Im been asked to do a csv job where i import a csv to an array then separate by supplier then create an excel sheet of the separated data for each supplier

Ive used _ParseCSV() by ProgAndy as it seemed to work the best from the many i tried that are available on the forum

So the data is in the array as you can see from the pic

But every time i try and separate ADAMS for example it loses all the other data relating to their name, it might be because its a 2D array, i really dont know

The supplier reference is always in Col 0

Basically there could be 25 suppliers with 10-1000 products each

How do i separate their data and make each into a new array to send to excel?

 

[Gianni]

Hi Chimaera

if I understand the question, you may proceed as follows:

$Arra1 = _ParseCSV("filename.csv",",") ; here you import csv file in $Array1

$FoundRecords _ArrayFindAll($Arra1, "ADAMS") ; here you find all records with "ADAMS" in the first column
; _arrayFindAll returns an array with the indexes of found records not with record data

Local $NewArray[UBound($FoundRecords)][UBound(UBound($Arra1, 2)] ; create a new 2D array with adeguate number of records and fields

For $x = 0 To UBound($FoundRecords) - 1 ; loop all found record

    For $y = 0 To UBound($Arra1, 2) -1  ; loop all field within the record

        $NewArray[$x][$y] = $Arra1[$FoundRecords[$x]][$y] ; and populate the new array

    Next
Next

_ArrayDisplay($NewArray)

(not tested with real data)

edit added -1 to ubound in listing

Edited September 25, 2013 by PincoPanco

[Chimaera]

Thx for that ive tried that and modified it as well but i cannot get it to work at all

I even tried Beta run as well

Error parsing function call.:

Local $NewArray[uBound($FoundRecords)][uBound(UBound($Arra1, 2)]

Local $NewArray[uBound($FoundRecords)][uBound(UBound($Arra1, 2)^ ERROR

 

seems to be the major issue

Edited September 25, 2013 by Chimaera

[Gianni]

try with this

Local $NewArray[UBound($FoundRecords)][UBound($Arra1, 2)]

there was double Ubound typo

sorry

[Chimaera]

Aaah now we are cooking on gas thx, i had tried all sorts never thought about the ubound

That gives me the correct list so ill play with the methods and see whats feasible

[Chimaera]

Ended up with this but im still getting an error

Global $SupplierList = "Adams|Ass|CPC|Dabs|Dac|Fpi|Fred|Gil|jds|Kook|mdp|MJB|pc4U|pww|srus|"
Global $aSuppliers = StringSplit($SupplierList, "|")

For $i = 1 To $aSuppliers[0]
    If @error = 0 Then
            $FindSupplier = _IsolateSupplier()
            Sleep(100)
    EndIf
Next

Func _IsolateSupplier()
    Local $FoundRecords = _ArrayFindAll($MainArray, $aSuppliers[$i]) ; here you find all records with "ADAMS" in the first column
    ; _arrayFindAll returns an array with the indexes of found records not with record data
    Local $NewArray[UBound($FoundRecords)][UBound($MainArray, 2)] ; create a new 2D array with adeguate number of records and fields

    For $x = 0 To UBound($FoundRecords) - 1 ; loop all found record
        For $y = 0 To UBound($MainArray, 2) - 1 ; loop all field within the record
            $NewArray[$x][$y] = $MainArray[$FoundRecords[$x]][$y] ; and populate the new array
        Next
;~      _ArrayDisplay($NewArray)
        _WriteCSV( @ScriptDir & "\" & $aSuppliers[$i] & ".csv", $NewArray, ",")
    Next
EndFunc   ;==>_IsolateSupplier;

expandcollapse popup

; #FUNCTION# ====================================================================================================================
; Name...........: _ParseCSV
; Description ...: Reads a CSV-file
; Syntax.........: _ParseCSV($sFile, $sDelimiters=',', $sQuote='"', $iFormat=0)
; Parameters ....: $sFile       - File to read or string to parse
;                  $sDelimiters - [optional] Fieldseparators of CSV, mulitple are allowed (default: ,;)
;                  $sQuote      - [optional] Character to quote strings (default: ")
;                  $iFormat     - [optional] Encoding of the file (default: 0):
;                  |-1     - No file, plain data given
;                  |0 or 1 - automatic (ASCII)
;                  |2      - Unicode UTF16 Little Endian reading
;                  |3      - Unicode UTF16 Big Endian reading
;                  |4 or 5 - Unicode UTF8 reading
; Return values .: Success - 2D-Array with CSV data (0-based)
;                  Failure - 0, sets @error to:
;                  |1 - could not open file
;                  |2 - error on parsing data
;                  |3 - wrong format chosen
; Author ........: ProgAndy
; Modified.......:
; Remarks .......:
; Related .......: _WriteCSV
; Link ..........:
; Example .......:
; ===============================================================================================================================
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})*["]|[^,\r\n]*)(\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]
    For $i = 0 To $iBound - 1
        Select
            Case StringLen($aREgex[$i]) < 3 And StringInStr(@CRLF, $aREgex[$i])
                $iIndex += 1
                $iSub = 0
                ContinueLoop
            Case StringLeft(StringStripWS($aREgex[$i], 1), 1) = $sQuote
                $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
                $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][$iSubBound]
    Return $aResult
EndFunc   ;==>_ParseCSV

; #FUNCTION# ====================================================================================================================
; Name...........: _WriteCSV
; Description ...: Writes a CSV-file
; Syntax.........: _WriteCSV($sFile, Const ByRef $aData, $sDelimiter, $sQuote, $iFormat=0)
; Parameters ....: $sFile      - Destination file
;                  $aData      - [Const ByRef] 0-based 2D-Array with data
;                  $sDelimiter - [optional] Fieldseparator (default: ,)
;                  $sQuote     - [optional] Quote character (default: ")
;                  $iFormat    - [optional] character encoding of file (default: 0)
;                  |0 or 1 - ASCII writing
;                  |2      - Unicode UTF16 Little Endian writing (with BOM)
;                  |3      - Unicode UTF16 Big Endian writing (with BOM)
;                  |4      - Unicode UTF8 writing (with BOM)
;                  |5      - Unicode UTF8 writing (without BOM)
; Return values .: Success - True
;                  Failure - 0, sets @error to:
;                  |1 - No valid 2D-Array
;                  |2 - Could not open file
; Author ........: ProgAndy
; Modified.......:
; Remarks .......:
; Related .......: _ParseCSV
; Link ..........:
; Example .......:
; ===============================================================================================================================
Func _WriteCSV($sFile, Const ByRef $aData, $sDelimiter = ',', $sQuote = '"', $iFormat = 0)
    Local Static $aEncoding[6] = [2, 2, 34, 66, 130, 258]
    If $sDelimiter = "" Or IsKeyword($sDelimiter) Then $sDelimiter = ','
    If $sQuote = "" Or IsKeyword($sQuote) Then $sQuote = '"'
    Local $iBound = UBound($aData, 1), $iSubBound = UBound($aData, 2)
    If Not $iSubBound Then Return SetError(2, 0, 0)
    Local $hFile = FileOpen($sFile, $aEncoding[$iFormat])
    If @error Then Return SetError(2, @error, 0)
    For $i = 0 To $iBound - 1
        For $j = 0 To $iSubBound - 1
            FileWrite($hFile, $sQuote & StringReplace($aData[$i][$j], $sQuote, $sQuote & $sQuote, 0, 1) & $sQuote)
            If $j < $iSubBound - 1 Then FileWrite($hFile, $sDelimiter)
        Next
        FileWrite($hFile, @CRLF)
    Next
    FileClose($hFile)
    Return True
EndFunc   ;==>_WriteCSV

 

==> Array variable subscript badly formatted.:

Local $NewArray[uBound($FoundRecords)][uBound($MainArray, 2)]

Local $NewArray[^ ERROR

 

It writes the .csv files correctly then shows the error right at the end

Any idea whats causing it?

Edited September 25, 2013 by Chimaera

[BrewManNH]

Where is $MainArray declared or populated?

EDIT: Also, you're using $aSppliers[$i], but you're using it wrong. You shouldn't be using the $i variable from the loop like that, you should pass it to the function _IsolateSupplier, otherwise if you ever put that part of the script into a function, it won't be a Global any longer.

Edited September 25, 2013 by BrewManNH

[Chimaera]

Sorry i missed copying that bit

Mainarray is populated like this

Local $MainArray = _ParseCSV("Test Stock Export.csv", ",")

As for your second part i dont understand what you mean

[Gianni]

At first sight it seems to me that there are 3 issues:

1) remove the last | from this line

Global $SupplierList = "Adams|Ass|CPC|Dabs|Dac|Fpi|Fred|Gil|jds|Kook|mdp|MJB|pc4U|pww|srus|" ; remove last |

2) pass the $i variable to the func:

$FindSupplier = _IsolateSupplier($i) ; <---- pass $i

3)  adeguate the func to receive the $i variable

Func _IsolateSupplier($i) ; <---- add $i

 

 

[BrewManNH]

Sorry i missed copying that bit

 

Mainarray is populated like this

Local $MainArray = _ParseCSV("Test Stock Export.csv", ",")

You're splitting the array using a comma, but your script uses the pipe "|" character. 

 

As for your second part i dont understand what you mean

Global $SupplierList = "Adams|Ass|CPC|Dabs|Dac|Fpi|Fred|Gil|jds|Kook|mdp|MJB|pc4U|pww|srus|"
Global $aSuppliers = StringSplit($SupplierList, "|")

For $i = 1 To $aSuppliers[0]
    If @error = 0 Then ; <<<<<<<<<<<<< Where are you expecting an error to come from at this point?
        $FindSupplier = _IsolateSupplier($i) ; pass the count to the function as a parameter
        Sleep(100)
    EndIf
Next

Func _IsolateSupplier($count) ; pass the loop counter here
    Local $FoundRecords = _ArrayFindAll($MainArray, $aSuppliers[$count]) ; You never declare $MainArray
    ; _arrayFindAll returns an array with the indexes of found records not with record data
    Local $NewArray[UBound($FoundRecords)][UBound($MainArray, 2)] ; create a new 2D array with adeguate number of records and fields

    For $x = 0 To UBound($FoundRecords) - 1 ; loop all found record
        For $y = 0 To UBound($MainArray, 2) - 1 ; loop all field within the record
            $NewArray[$x][$y] = $MainArray[$FoundRecords[$x]][$y] ; and populate the new array
        Next
;~      _ArrayDisplay($NewArray)
        _WriteCSV(@ScriptDir & "\" & $aSuppliers[$count] & ".csv", $NewArray, ",")
    Next
EndFunc   ;==>_IsolateSupplier

EDIT: I really hate the editor on this forum software.

Edited September 25, 2013 by BrewManNH

[Gianni]

also, another suggestion:
instead of compiling "manually" th $SupplierList, you could use this statement to take the list automatically from the first column of the $MainArray:

Global $aSuppliers = _ArrayUnique($MainArray)

Edited September 25, 2013 by PincoPanco

[Chimaera]

Yes im splitting with the comma the namelist is seperate from the csv so i used the pipe char

It could probably be done with it pulling the names from the csv at runtime i guess, or ill grab the list from a text file time will tell

Updated version

Global $MainArray = _ParseCSV("Test Supplier Stock Export.csv", ",")
;~ _ArrayDisplay($MainArray)
Global $SupplierList = "Adams|Ass|CPC|Dabs|Dac|Fpi|Fred|Gil|jds|Kook|mdp|MJB|pc4U|pww|srus"
Global $aSuppliers = StringSplit($SupplierList, "|")

For $i = 1 To $aSuppliers[0]
    If IsArray($aSuppliers) Then
        $FindSupplier = _IsolateSupplier($i)
        Sleep(100)
    EndIf
Next

Func _IsolateSupplier($i)
    Local $FoundRecords = _ArrayFindAll($MainArray, $aSuppliers[$i])
    Local $NewArray[UBound($FoundRecords)][UBound($MainArray, 2)]

    For $x = 0 To UBound($FoundRecords) - 1
        For $y = 0 To UBound($MainArray, 2) - 1
            $NewArray[$x][$y] = $MainArray[$FoundRecords[$x]][$y]
        Next
;~      _ArrayDisplay($NewArray)
        _WriteCSV(@ScriptDir & "\" & $aSuppliers[$i] & ".csv", $NewArray, ",")
    Next
EndFunc   ;==>_IsolateSupplier

Thanks for the help

Edited September 25, 2013 by Chimaera

[water]

As you have to work with Excel anyway you could try my rewrite fo the Excel UDF. It allows to import/export CSV files.

[Chimaera]

that may come in later this was just a test of what could be done and i need to speak with the person involved now i know a bit more about it

does the new excel allow saving as xlsx format?

[water]

Yes!

[Chimaera]

I assume its the ExcelEX udf, ive had a look but even when i try and run _Excel_Open  it always opens the Excel_Rewrite file and gives this error

 

Excel Rewrite Beta 2Excel Rewrite.au3(168,56) : ERROR: ObjGet() [built-in] called with wrong number of args.

$oWorkbook = ObjGet("", $sCLSID_Workbook, $iCount + 1)

 

Nvm i didnt think to run as beta.

Edited September 25, 2013 by Chimaera

[water]

The new Excel UDF needs to be run with the latest beta of AutoIt. This version supports a 3rd parameter for ObjGet (the instance).

[Chimaera]

It looks good and seems to work fine with Excel 2013

I thought the export to pdf might be usefull for something else im doing

I couldnt find the export to csv?

But ill have a look another day

[water]

Export to CSV is simply calling _Excel_BookSaveAs and setting $iType to $xlCSV.

[Chimaera]

also, another suggestion:

instead of compiling "manually" th $SupplierList, you could use this statement to take the list automatically from the first column of the $MainArray:

Global $aSuppliers = _ArrayUnique($MainArray)

Thanks that came in handy.

Another question

If i wanted to allow the search on any of the columns how would i access the array to give me that data?

Obviously im going to do a gui with probably a dropdown for the choice part but howto populate the choice with just the columns no rows 

To the best of my knowledge there is no headers from where it was exported from the mysql dbase, so looking at row 1 wont give me the info but if i could maybe show Col1 Col2 etc along the top and then pass that param to the split in the above posts.

I've looked through the array options in the helpfile but there doesn't seem to be options for column numbers

Any hints?

current code below less udfs

#include <array.au3>

Global $MainArray = _ParseCSV("Test Supplier Stock Export.csv", ",")
_ArrayDisplay($MainArray)

Global $aSuppliers = _ArrayUnique($MainArray)


For $i = 1 To $aSuppliers[0]
    If IsArray($aSuppliers) Then
        $FindSupplier = _IsolateRecord($i)
        Sleep(100)
    EndIf

Next
    MsgBox(64, "All Done", "Splitting Finished")

Func _IsolateRecord($i)
    Local $FoundRecords = _ArrayFindAll($MainArray, $aSuppliers[$i])
    Local $NewArray[UBound($FoundRecords)][UBound($MainArray, 2)]

    For $x = 0 To UBound($FoundRecords) - 1
        For $y = 0 To UBound($MainArray, 2) - 1
            $NewArray[$x][$y] = $MainArray[$FoundRecords[$x]][$y]
        Next
;~      _ArrayDisplay($NewArray)
        _WriteCSV(@ScriptDir & "\" & $aSuppliers[$i] & ".csv", $NewArray, ",")
    Next
EndFunc   ;==>_IsolateSupplier

Edited October 15, 2013 by Chimaera