Jump to content
Sign in to follow this  
Chimaera

Manipulating a CSV after import to array

Recommended Posts

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

post-60350-0-94147000-1380096797_thumb.p

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?

 

Share this post


Link to post
Share on other sites
Chimp

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 by PincoPanco

small minds discuss people average minds discuss events great minds discuss ideas.... and use AutoIt....

Share this post


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

Share this post


Link to post
Share on other sites
Chimp

try with this

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

there was double Ubound typo

sorry


small minds discuss people average minds discuss events great minds discuss ideas.... and use AutoIt....

Share this post


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

Share this post


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

Share this post


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

If I posted any code, assume that code was written using the latest release version unless stated otherwise. Also, if it doesn't work on XP I can't help with that because I don't have access to XP, and I'm not going to.
Give a programmer the correct code and he can do his work for a day. Teach a programmer to debug and he can do his work for a lifetime - by Chirag Gude
How to ask questions the smart way!

I hereby grant any person the right to use any code I post, that I am the original author of, on the autoitscript.com forums, unless I've specifically stated otherwise in the code or the thread post. If you do use my code all I ask, as a courtesy, is to make note of where you got it from.

Back up and restore Windows user files _Array.au3 - Modified array functions that include support for 2D arrays.  -  ColorChooser - An add-on for SciTE that pops up a color dialog so you can select and paste a color code into a script.  -  Customizable Splashscreen GUI w/Progress Bar - Create a custom "splash screen" GUI with a progress bar and custom label.  -  _FileGetProperty - Retrieve the properties of a file  -  SciTE Toolbar - A toolbar demo for use with the SciTE editor  -  GUIRegisterMsg demo - Demo script to show how to use the Windows messages to interact with controls and your GUI.  -   Latin Square password generator

Share this post


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

Share this post


Link to post
Share on other sites
Chimp

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

 

 

small minds discuss people average minds discuss events great minds discuss ideas.... and use AutoIt....

Share this post


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

If I posted any code, assume that code was written using the latest release version unless stated otherwise. Also, if it doesn't work on XP I can't help with that because I don't have access to XP, and I'm not going to.
Give a programmer the correct code and he can do his work for a day. Teach a programmer to debug and he can do his work for a lifetime - by Chirag Gude
How to ask questions the smart way!

I hereby grant any person the right to use any code I post, that I am the original author of, on the autoitscript.com forums, unless I've specifically stated otherwise in the code or the thread post. If you do use my code all I ask, as a courtesy, is to make note of where you got it from.

Back up and restore Windows user files _Array.au3 - Modified array functions that include support for 2D arrays.  -  ColorChooser - An add-on for SciTE that pops up a color dialog so you can select and paste a color code into a script.  -  Customizable Splashscreen GUI w/Progress Bar - Create a custom "splash screen" GUI with a progress bar and custom label.  -  _FileGetProperty - Retrieve the properties of a file  -  SciTE Toolbar - A toolbar demo for use with the SciTE editor  -  GUIRegisterMsg demo - Demo script to show how to use the Windows messages to interact with controls and your GUI.  -   Latin Square password generator

Share this post


Link to post
Share on other sites
Chimp

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 by PincoPanco

small minds discuss people average minds discuss events great minds discuss ideas.... and use AutoIt....

Share this post


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

Share this post


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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


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

Share this post


Link to post
Share on other sites
water

Yes!


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


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

Share this post


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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


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

Share this post


Link to post
Share on other sites
water

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


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

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  

×