Jump to content
fopetesl

Reading CSV with mixed delimiters?

Recommended Posts

fopetesl

I have a csv file with delimiters "," and @CRLF

After trying several different examples (simple ones!) I still haven't resolved an answer

#include <Array.au3>
#include <File.au3>
;#include "ArrayMultiColSort.au3"

Global $BatchDir = "C:\ncat\"
FileChangeDir($BatchDir)
$sFile = "mod1.csv"

; Read file into a 2D array
Global $aArray

_FileReadToArray($sFile, $aArray, $FRTA_NOCOUNT, ",")

; And here it is
_ArrayDisplay($aArray, "Original", Default, 8)

Firstly it throws a MsgBox error "No array variable passed to function" _ArrayDisplay(), so no displayed data

Second and probably more important how do I get _FileReadToArray() to split the imported array[][] into rows and columns?

I tried "," & @CRLF without success.


The most powerful number in the Universe.  Zero.

Share this post


Link to post
Share on other sites
fopetesl

I have a csv file with delimiters "," and @CRLF

After trying several different examples (simple ones!) I still haven't resolved an answer

#include <Array.au3>
#include <File.au3>
;#include "ArrayMultiColSort.au3"

Global $BatchDir = "C:\ncat\"
FileChangeDir($BatchDir)
$sFile = "mod1.csv"

; Read file into a 2D array
Global $aArray

_FileReadToArray($sFile, $aArray, $FRTA_NOCOUNT, ",")

; And here it is
_ArrayDisplay($aArray, "Original", Default, 8)

Firstly it throws a MsgBox error "No array variable passed to function" _ArrayDisplay(), so no displayed data

Second and probably more important how do I get _FileReadToArray() to split the imported array[][] into rows and columns?

I tried "," & @CRLF without success.


The most powerful number in the Universe.  Zero.

Share this post


Link to post
Share on other sites
Jfish

The regex guys probably have a more elegant solution but for me I would just read it into a string first.  Then I would string replace the @crlf with the comma and the do a string split on the new string using a comma as the delimiter.  That function also returns an array.


Build your own poker game with AutoIt: pokerlogic.au3 | Learn To Program Using FREE Tools with AutoIt

Share this post


Link to post
Share on other sites
BugFix
#include <File.au3>

Global $BatchDir = "C:\ncat\"
FileChangeDir($BatchDir)
$sFile = "mod1.csv"

; Read file into a array
Global $aArray

_FileReadToArray($sFile, $aArray, $FRTA_NOCOUNT)  ; param: "," was wrong, linebreak is: @CRLF, the default delimiter

; now create an 2D-Array to hold row/column
; 1st count the fields in line
StringReplace($aArray[0], ',', ',')
Local $iCountField = @extended +1
Local $aArrayRowCol[UBound($aArray)][$iCountField]

Local $aSplit
; iterate through the line array and split every line in field-values
For $i = 0 To UBound($aArray) -1
    $aSplit = StringSplit($aArray[$i], ',')
    For $j = 1 To $aSplit[0]
        $aArrayRowCol[$i][$j] = $aSplit[$j]
    Next
Next

 ; The result
_ArrayDisplay($aArrayRowCol)

 


Best Regards BugFix  

Share this post


Link to post
Share on other sites
Melba23

Threads merged.

M23


Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind._______My UDFs:

Spoiler

ArrayMultiColSort ---- Sort arrays on multiple columns
ChooseFileFolder ---- Single and multiple selections from specified path treeview listing
Date_Time_Convert -- Easily convert date/time formats, including the language used
ExtMsgBox --------- A highly customisable replacement for MsgBox
GUIExtender -------- Extend and retract multiple sections within a GUI
GUIFrame ---------- Subdivide GUIs into many adjustable frames
GUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView items
GUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeView
Marquee ----------- Scrolling tickertape GUIs
NoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxes
Notify ------------- Small notifications on the edge of the display
Scrollbars ----------Automatically sized scrollbars with a single command
StringSize ---------- Automatically size controls to fit text
Toast -------------- Small GUIs which pop out of the notification area

 

Share this post


Link to post
Share on other sites
Skysnake

Would it be possible to provide some sample code? How can "@CRLF" be used as a CSV delimiter?


Skysnake

Why is the snake in the sky?

Share this post


Link to post
Share on other sites
BugFix
7 minutes ago, Skysnake said:

Would it be possible to provide some sample code? How can "@CRLF" be used as a CSV delimiter?

I think, fopetesl means:  line has fields with delimiter "," and ends with @CRLF.


Best Regards BugFix  

Share this post


Link to post
Share on other sites
Skysnake

Try this

1. Open with Notepad++ and change line endings.  If ending with @CRLF really should not be a problem...

2. Try this CSVSplit :)

Skysnake


Skysnake

Why is the snake in the sky?

Share this post


Link to post
Share on other sites
Subz

If you have Excel you can read it directly into a 2d Array by using the Excel UDF.

Share this post


Link to post
Share on other sites
Melba23

fopetesl,

I can read a "comma and @CRLF" delimited file with no problem:

#include <File.au3>
#include <Array.au3>

$sFilePath = "Test.csv"

If not FileExists($sFilePath) Then

    $sData = "A1,A2,A3" & @CRLF & "B1,B2,B3" & @CRLF & "C1,C2,C3"

    $hFile = FileOpen($sFilePath, $FO_OVERWRITE)

    FileWrite($hFile, $sData)

    FileClose($hFile)

EndIf

Local $aRetArray

_FileReadToArray($sFilePath, $aRetArray, Default, ",")

_ArrayDisplay($aRetArray, "", Default, 8)

But I wonder if your file has the same number of comma-separated elements on each row. Because if it does not then _FileReadToArray will fail with @error 3 (as explained in the Help file) and you do not get an array returned - which would explain why you get the MsgBox telling you just that.

As always, it helps to do some debugging yourself before posting - or at the very least post an example of the file you are using so that we can test with the same data.

M23


Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind._______My UDFs:

Spoiler

ArrayMultiColSort ---- Sort arrays on multiple columns
ChooseFileFolder ---- Single and multiple selections from specified path treeview listing
Date_Time_Convert -- Easily convert date/time formats, including the language used
ExtMsgBox --------- A highly customisable replacement for MsgBox
GUIExtender -------- Extend and retract multiple sections within a GUI
GUIFrame ---------- Subdivide GUIs into many adjustable frames
GUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView items
GUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeView
Marquee ----------- Scrolling tickertape GUIs
NoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxes
Notify ------------- Small notifications on the edge of the display
Scrollbars ----------Automatically sized scrollbars with a single command
StringSize ---------- Automatically size controls to fit text
Toast -------------- Small GUIs which pop out of the notification area

 

Share this post


Link to post
Share on other sites
Bowmore

One issue that can often cause a failure when attempting to read a delimited file, is a blank line in the file, especially the last line.


"Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the universe trying to build bigger and better idiots. So far, the universe is winning."- Rick Cook

Share this post


Link to post
Share on other sites
fopetesl
18 hours ago, Melba23 said:

fopetesl,

I can read a "comma and @CRLF" delimited file with no problem:

#include <File.au3>
#include <Array.au3>

$sFilePath = "Test.csv"

If not FileExists($sFilePath) Then

    $sData = "A1,A2,A3" & @CRLF & "B1,B2,B3" & @CRLF & "C1,C2,C3"

    $hFile = FileOpen($sFilePath, $FO_OVERWRITE)

    FileWrite($hFile, $sData)

    FileClose($hFile)

EndIf

Local $aRetArray

_FileReadToArray($sFilePath, $aRetArray, Default, ",")

_ArrayDisplay($aRetArray, "", Default, 8)

But I wonder if your file has the same number of comma-separated elements on each row. Because if it does not then _FileReadToArray will fail with @error 3 (as explained in the Help file) and you do not get an array returned - which would explain why you get the MsgBox telling you just that.

As always, it helps to do some debugging yourself before posting - or at the very least post an example of the file you are using so that we can test with the same data.

M23

This is a sample CSV presented in three ways 1) as a CSV file ascidata_mod2.csv;

2) as text, (just the relevant part)

,P1,CvP1,N10,N15,N20,N25,N30,N35,N40,N45,N50,P95,P90,P75,P50,P25,P10,P5,P2.5,P1
Pev1,59,48.4,0.5,5.4,10.3,15.1,20,24.9,29.8,34.7,39.6,14.5,19.5,35,60.5,86,99.2,102.2,104,106
,P2,CvP2,N10,N15,N20,N25,N30,N35,N40,N45,N50,P95,P90,P75,P50,P25,P10,P5,P2.5,P1
Pev2,74,34.5,0.1,1.1,2.5,4.3,6.5,9.2,12.2,15.7,19.5,26.5,36.2,56.2,79,96,101.8,103.8,105.5,107.2
P1 FREQ,P1 HIST,P2 FREQ,P2 HIST,,Pev1,,,,,,Pev2,,,,,,,,,
100,0,100,0,P1,59,,,,P2,74,,,,,,,,,,
100,0,100,0,CvP1,48.4,,,,CvP2,34.5,,,,,,,,,,
100,0,100,0,N10,0.5,P95,14.5,,N10,0.1,P95,26.5,,,,,,,,
100,0,100,0,N15,5.4,P90,19.5,,N15,1.1,P90,36.2,,,,,,,,
100,0,100,0,N20,10.3,P75,35,,N20,2.5,P75,56.2,,,,,,,,
100,0,100,0,N25,15.1,P50,60.5,,N25,4.3,P50,79,,,,,,,,
100,0,100,0,N30,20,P25,86,,N30,6.5,P25,96,,,,,,,,
100,0,100,0,N35,24.9,P10,99.2,,N35,9.2,P10,101.8,,,,,,,,
100,0,100,0,N40,29.8,P5,102.2,,N40,12.2,P5,103.8,,,,,,,,
100,0,100,0,N45,34.7,P2.5,104,,N45,15.7,P2.5,105.5,,,,,,,,
100,0,100,0,N50,39.6,P1,106,,N50,19.5,P1,107.2,,,,,,,,
100,0,100,0
100,0,100,0

3) as binary ascidata_mod2_binary.jpg

The cells I am particularly interested in extracting F6-F16, H6-H16, K6-K16 & M6-16

Editing the CSV file manually is not an option. The CSV is created from interpreted raw data and the actual layout can be adjusted somewhat but the CSV data must be accessible.

 

ascidata_mod2.csv

ascidata_mod2_binary.jpg


The most powerful number in the Universe.  Zero.

Share this post


Link to post
Share on other sites
Melba23

fopetesl,

As I suspected, the lines do not contain the same number of elements, so _FileReadToArray will not create a "square" 2D array and will return @error 3. See what I mean about doing some errorchecking on the code?

So we have to do some more manipulation to get the data into a 2D array:

#include <Array.au3>
#include <File.au3>

$sFilePath = "Test.csv"

; Create file if required
If Not FileExists($sFilePath) Then

    $sData = ",P1,CvP1,N10,N15,N20,N25,N30,N35,N40,N45,N50,P95,P90,P75,P50,P25,P10,P5,P2.5,P1" & @CRLF & _
                    "Pev1,59,48.4,0.5,5.4,10.3,15.1,20,24.9,29.8,34.7,39.6,14.5,19.5,35,60.5,86,99.2,102.2,104,106" & @CRLF & _
                    ",P2,CvP2,N10,N15,N20,N25,N30,N35,N40,N45,N50,P95,P90,P75,P50,P25,P10,P5,P2.5,P1" & @CRLF & _
                    "Pev2,74,34.5,0.1,1.1,2.5,4.3,6.5,9.2,12.2,15.7,19.5,26.5,36.2,56.2,79,96,101.8,103.8,105.5,107.2" & @CRLF & _
                    "P1 FREQ,P1 HIST,P2 FREQ,P2 HIST,,Pev1,,,,,,Pev2,,,,,,,,," & @CRLF & _
                    "100,0,100,0,P1,59,,,,P2,74,,,,,,,,,," & @CRLF & _
                    "100,0,100,0,CvP1,48.4,,,,CvP2,34.5,,,,,,,,,," & @CRLF & _
                    "100,0,100,0,N10,0.5,P95,14.5,,N10,0.1,P95,26.5,,,,,,,," & @CRLF & _
                    "100,0,100,0,N15,5.4,P90,19.5,,N15,1.1,P90,36.2,,,,,,,," & @CRLF & _
                    "100,0,100,0,N20,10.3,P75,35,,N20,2.5,P75,56.2,,,,,,,," & @CRLF & _
                    "100,0,100,0,N25,15.1,P50,60.5,,N25,4.3,P50,79,,,,,,,," & @CRLF & _
                    "100,0,100,0,N30,20,P25,86,,N30,6.5,P25,96,,,,,,,," & @CRLF & _
                    "100,0,100,0,N35,24.9,P10,99.2,,N35,9.2,P10,101.8,,,,,,,," & @CRLF & _
                    "100,0,100,0,N40,29.8,P5,102.2,,N40,12.2,P5,103.8,,,,,,,," & @CRLF & _
                    "100,0,100,0,N45,34.7,P2.5,104,,N45,15.7,P2.5,105.5,,,,,,,," & @CRLF & _
                    "100,0,100,0,N50,39.6,P1,106,,N50,19.5,P1,107.2,,,,,,,," & @CRLF & _
                    "100,0,100,0" & @CRLF & _
                    "100,0,100,0"
    $hFile = FileOpen($sFilePath, $FO_OVERWRITE)
    FileWrite($hFile, $sData)
    FileClose($hFile)
EndIf

; Read file to 1D array
Global $aLines
_FileReadToArray($sFilePath, $aLines)
;_ArrayDisplay($aLines, "", Default, 8)

;Global $aComma[UBound($aLines)] ; Use this if you want to see how many commas were found on each line
$iMaxComma = 0
For $i = 0 To UBound($aLines) - 1
    StringReplace($aLines[$i], ",", "")
    If @extended > $iMaxComma Then
        $iMaxComma = @extended
        ;$aComma[$i] = @extended
    EndIf
Next
; _ArrayDisplay($aComma, "", Default, 8)

; Now create  asuitably sized 2D array
Global $aElements[UBound($aLines)][$iMaxComma + 1]
; And split each line into it
For $i = 0 To UBound($aLines) - 1
    $aSplit = StringSplit($aLines[$i], ",")
    For $j = 1 To $aSplit[0]
        $aElements[$i][$j - 1] = $aSplit[$j]
    Next
Next
; And here is the result
_ArrayDisplay($aElements, "", Default, 8)

Now you get a 2D array and can access the elements you require.

M23


Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind._______My UDFs:

Spoiler

ArrayMultiColSort ---- Sort arrays on multiple columns
ChooseFileFolder ---- Single and multiple selections from specified path treeview listing
Date_Time_Convert -- Easily convert date/time formats, including the language used
ExtMsgBox --------- A highly customisable replacement for MsgBox
GUIExtender -------- Extend and retract multiple sections within a GUI
GUIFrame ---------- Subdivide GUIs into many adjustable frames
GUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView items
GUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeView
Marquee ----------- Scrolling tickertape GUIs
NoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxes
Notify ------------- Small notifications on the edge of the display
Scrollbars ----------Automatically sized scrollbars with a single command
StringSize ---------- Automatically size controls to fit text
Toast -------------- Small GUIs which pop out of the notification area

 

Share this post


Link to post
Share on other sites
fopetesl

Works just as described, M23. Thank you.

But, when I attempt to extract 'cells' I get the same result as _ArrayDisplay()

; And here is the result
;_ArrayDisplay($aElements, "", Default, 8); <<< works fine
 MsgBox($MB_OK, "Data Results", $aElements[6][4] & @CRLF & "  " & $aElements[6][5])

 


The most powerful number in the Universe.  Zero.

Share this post


Link to post
Share on other sites
Melba23

fopetesl,

I get a MsgBox with "P1" & "59", which is what I would expect.

M23


Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind._______My UDFs:

Spoiler

ArrayMultiColSort ---- Sort arrays on multiple columns
ChooseFileFolder ---- Single and multiple selections from specified path treeview listing
Date_Time_Convert -- Easily convert date/time formats, including the language used
ExtMsgBox --------- A highly customisable replacement for MsgBox
GUIExtender -------- Extend and retract multiple sections within a GUI
GUIFrame ---------- Subdivide GUIs into many adjustable frames
GUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView items
GUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeView
Marquee ----------- Scrolling tickertape GUIs
NoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxes
Notify ------------- Small notifications on the edge of the display
Scrollbars ----------Automatically sized scrollbars with a single command
StringSize ---------- Automatically size controls to fit text
Toast -------------- Small GUIs which pop out of the notification area

 

Share this post


Link to post
Share on other sites
fopetesl
35 minutes ago, Melba23 said:

fopetesl,

I get a MsgBox with "P1" & "59", which is what I would expect.

M23

Ooops! More finger trouble! Excess of variables. :(

Problem solved :)


The most powerful number in the Universe.  Zero.

Share this post


Link to post
Share on other sites
fopetesl

Yes, I now can access the elements I want.

However I would like to display in a pop up GUI not a message box unless I can make a MsgBox look like a GUI with options to print and/or exit _and_ make it larger.

I've looked at ComboBox, GUICtrlCreateEdit/GUICtrlSetData, TreeView plus other examples of displaying text in a GUI.  None fit tidily and/or don't have the user options I'd like.

Edited by fopetesl
MsgBox fixed size

The most powerful number in the Universe.  Zero.

Share this post


Link to post
Share on other sites
Melba23

fopetesl,

You will have to create your own custom dialog to do that. A simple GUI with a ListView and a few buttons should suffice.

M23


Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind._______My UDFs:

Spoiler

ArrayMultiColSort ---- Sort arrays on multiple columns
ChooseFileFolder ---- Single and multiple selections from specified path treeview listing
Date_Time_Convert -- Easily convert date/time formats, including the language used
ExtMsgBox --------- A highly customisable replacement for MsgBox
GUIExtender -------- Extend and retract multiple sections within a GUI
GUIFrame ---------- Subdivide GUIs into many adjustable frames
GUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView items
GUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeView
Marquee ----------- Scrolling tickertape GUIs
NoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxes
Notify ------------- Small notifications on the edge of the display
Scrollbars ----------Automatically sized scrollbars with a single command
StringSize ---------- Automatically size controls to fit text
Toast -------------- Small GUIs which pop out of the notification area

 

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

  • Similar Content

    • SOF-TECH
      By SOF-TECH
      Dear all,
      Can someone show  me how to en hance the below function to write in CSV  into column  and rows the input values ? 
      I am getting this result: 

      I would like the result to be as this 

      From A1:C1 is for headers
      From A2:C2 is for input Data
      Global Const $GUI_EVENT_CLOSE = -3 $sDataFilePath = @ScriptDir & "\Records.csv" #region ### START Koda GUI section ### Form= $Form1 = GUICreate("Demo1: New Record", 580, 115) $Input1 = GUICtrlCreateInput("", 10, 30, 270, 21) $Input2 = GUICtrlCreateInput("", 300, 30, 270, 21) $Input3 = GUICtrlCreateInput("", 10, 80, 270, 21) $Label1 = GUICtrlCreateLabel("Name:", 10, 10, 35, 17) $Label2 = GUICtrlCreateLabel("ID:", 300, 10, 18, 17) $Label3 = GUICtrlCreateLabel("Phone No:", 10, 60, 55, 17) $Button1 = GUICtrlCreateButton("Save to CSV", 450, 70, 120, 30) GUISetState(@SW_SHOW) #endregion ### END Koda GUI section ### While 1 $nMsg = GUIGetMsg() Switch $nMsg Case $GUI_EVENT_CLOSE Exit Case $Button1 _ExportData() MsgBox(64, @ScriptName, "Record Saved.") EndSwitch WEnd Func _ExportData() If Not FileExists($sDataFilePath) Then FileWriteLine($sDataFilePath, "Name;ID;Phone No.;") EndIf For $i = $Input1 To $Input3 FileWrite($sDataFilePath, GUICtrlRead($i) & ";") Next FileWriteLine($sDataFilePath, "") EndFunc ;==>_ExportData May be Excel UDF has be to be added but I can manage that my self  
      Thank you in advance
    • ShawnW
      By ShawnW
      I have a script that takes a large excel file, pulls out and reorganizes certain information I need, and spits out a trimmed down csv file which I uses to upload the information on my website. Some of this information contains characters with accents or em dashes. By default it would create a csv file in ANSI which I then uploaded but had to tell my website import system it was windows-1252 in order for it to look correct.
      This was all working fine except now I need to add in a non-breaking space and non-breaking hyphen into parts of my output. At first I tried using ChrW(0xA0) and ChrW(0x2011) as replacements. A quick test in the console looked correct, however opening the csv output in notepad++ showed the space correctly but a ? for the hyphen and the file was still encoded as ANSI. I tried to view it as UTF-8 instead but this just made the space appear as xAO and also other characters appeared that way like my em dashes appeared as x97 and another symbol as xA7 etc.
      If I instead do a convert to UTF-8 from notepad++ then those problems go away except the hyphen still displays as ?. I then noticed on the page I linked for the non-breaking hyphen it lists the UTF-8 hex as 0xE2 0x80 0x91 (e28091). I was unsure how to enter this in autoit but several things i tried all failed to get the hyphen inserted.
      I need a way to get both the space and hyphen added correctly as either ANSI or UTF-8, but if it is UTF-8 then I need a way to convert all of the other data I extracted from the excel file.
      I've included a test excel file with a single line and test script to create a csv demonstrating the problem.
      test.xlsx
      test.au3
    • FrancescoDiMuro
      By FrancescoDiMuro
      Good morning guys
      I'd like to know if there is a way to convert a PDF in CSV or, eventually, in TXT, in order to read from it, like a database...
      I have a PDF and I think ( I dind't search a lot on the forum ) with AutoIt, but I'd like work with Excel styles...
      Does anyone know a good program which convert PDF to CSV? 
      PS: the PDF file is 5 MB, and it contains 439 pages...
      Thanks everyone for the help
       
    • 31290
      By 31290
       
      Hi guys, 
      I'd like to write a piece of tool that would allow me to update a certain field in our Active Directory from a comma separated csv file composed like this:

      This file, automatically generated, can hold more than 10k lines.
      Thus, I need column A to be in one variable, column B in a second one and column C in a third one.
      I'm really missing this part as updating the AD is fairly easy once the 3 variable are populated. 
      I see things like this:
      Here's my attempts at the moment:
      #include <File.au3> #include <Array.au3> Global $csv_file = @DesktopDir & "\Book1.csv" Global $aRecords If Not _FileReadToArray($csv_file,$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]) ; Shows the line that was read from file $csv_line_values = StringSplit($aRecords[$x], ",",1) ; Splits the line into 2 or more variables and puts them in an array ; _ArrayDisplay($csv_line_values) ; Shows what's in the array you just created. ; $csv_line_values[0] holds the number of elements in array ; $csv_line_values[1] holds the value ; $csv_line_values[2] holds the value ; etc Msgbox(0, 0, $csv_line_values[1]) Next Any help on this please? 
      Thanks in advance
      -31290-
    • Jewtus
      By Jewtus
      I've had issues in the past dealing with excel so I decided to cut out the middle man and build a script that would take any file that opens in excel (csv, xml, xls, etc) and convert it into an array so I can handle the raw data in a cleaner way. I used czardas' CSV parser to do this and added a simple save in excel to save it as a csv to parse.
       
      Func _CreateCSV($fnImportFile) $oExcel = ObjCreate("Excel.Application") $oExcel.Visible=False $oBook= $oExcel.Workbooks.Open($fnImportFile) $oSheet=$oBook.ActiveSheet $fnMaster=@TempDir&"\"&Chr(Round(Random(0,24),0)+64)&Random(0,24)&Chr(Round(Random(0,24),0)+64)&Random(0,24)&Chr(Round(Random(0,24),0)+64)&".csv" ConsoleWrite($fnMaster&@CRLF) $oSheet.SaveAs($fnMaster, 6) $oBook.Close(False) $oExcel.Quit $aReturnArray=_CSVSplit(FileRead($fnMaster)) FileDelete($fnMaster) If not @error Then Return $aReturnArray Else Return -1 EndIf EndFunc ; #FUNCTION# ==================================================================================================================== ; Name...........: _CSVSplit ; Description ...: Converts a string in CSV format to a two dimensional array (see comments) ; Syntax.........: CSVSplit ( $aArray [, $sDelim ] ) ; Parameters ....: $aArray - The array to convert ; $sDelim - Optional - Delimiter set to comma by default (see 2nd comment) ; Return values .: Success - Returns a two dimensional array or a one dimensional array (see 1st comment) ; Failure - Sets @error to: ; |@error = 1 - First parameter is not a valid string ; |@error = 2 - Second parameter is not a valid string ; |@error = 3 - Could not find suitable delimiter replacements ; Author ........: czardas ; Comments ......; Returns a one dimensional array if the input string does not contain the delimiter string ; ; Some CSV formats use semicolon as a delimiter instead of a comma ; ; Set the second parameter to @TAB To convert to TSV ; =============================================================================================================================== Func _CSVSplit($string, $sDelim = ",") ; Parses csv string input and returns a one or two dimensional array If Not IsString($string) Or $string = "" Then Return SetError(1, 0, 0) ; Invalid string If Not IsString($sDelim) Or $sDelim = "" Then Return SetError(2, 0, 0) ; Invalid string $string = StringRegExpReplace($string, "[\r\n]+\z", "") ; [Line Added] Remove training breaks Local $iOverride = 63743, $asDelim[3] ; $asDelim => replacements for comma, new line and double quote For $i = 0 To 2 $asDelim[$i] = __GetSubstitute($string, $iOverride) ; Choose a suitable substitution character If @error Then Return SetError(3, 0, 0) ; String contains too many unsuitable characters Next $iOverride = 0 Local $aArray = StringRegExp($string, '\A[^"]+|("+[^"]+)|"+\z', 3) ; Split string using double quotes delim - largest match $string = "" Local $iBound = UBound($aArray) For $i = 0 To $iBound -1 $iOverride += StringInStr($aArray[$i], '"', 0, -1) ; Increment by the number of adjacent double quotes per element If Mod ($iOverride +2, 2) = 0 Then ; Acts as an on/off switch $aArray[$i] = StringReplace($aArray[$i], $sDelim, $asDelim[0]) ; Replace comma delimeters $aArray[$i] = StringRegExpReplace($aArray[$i], "(\r\n)|[\r\n]", $asDelim[1]) ; Replace new line delimeters EndIf $aArray[$i] = StringReplace($aArray[$i], '""', $asDelim[2]) ; Replace double quote pairs $aArray[$i] = StringReplace($aArray[$i], '"', '') ; Delete enclosing double quotes - not paired $aArray[$i] = StringReplace($aArray[$i], $asDelim[2], '"') ; Reintroduce double quote pairs as single characters $string &= $aArray[$i] ; Rebuild the string, which includes two different delimiters Next $iOverride = 0 $aArray = StringSplit($string, $asDelim[1], 2) ; Split to get rows $iBound = UBound($aArray) Local $aCSV[$iBound][2], $aTemp For $i = 0 To $iBound -1 $aTemp = StringSplit($aArray[$i], $asDelim[0]) ; Split to get row items If Not @error Then If $aTemp[0] > $iOverride Then $iOverride = $aTemp[0] ReDim $aCSV[$iBound][$iOverride] ; Add columns to accomodate more items EndIf EndIf For $j = 1 To $aTemp[0] If StringLen($aTemp[$j]) Then If Not StringRegExp($aTemp[$j], '[^"]') Then ; Field only contains double quotes $aTemp[$j] = StringTrimLeft($aTemp[$j], 1) ; Delete enclosing double quote single char EndIf $aCSV[$i][$j -1] = $aTemp[$j] ; Populate each row EndIf Next Next If $iOverride > 1 Then Return $aCSV ; Multiple Columns Else For $i = 0 To $iBound -1 If StringLen($aArray[$i]) And (Not StringRegExp($aArray[$i], '[^"]')) Then ; Only contains double quotes $aArray[$i] = StringTrimLeft($aArray[$i], 1) ; Delete enclosing double quote single char EndIf Next Return $aArray ; Single column EndIf EndFunc ;==> _CSVSplit ; #INTERNAL_USE_ONLY# =========================================================================================================== ; Name...........: __GetSubstitute ; Description ...: Searches for a character to be used for substitution, ie one not contained within the input string ; Syntax.........: __GetSubstitute($string, ByRef $iCountdown) ; Parameters ....: $string - The string of characters to avoid ; $iCountdown - The first code point to begin checking ; Return values .: Success - Returns a suitable substitution character not found within the first parameter ; Failure - Sets @error to 1 => No substitution character available ; Author ........: czardas ; Comments ......; This function is connected to the function _CSVSplit and was not intended for general use ; $iCountdown is returned ByRef to avoid selecting the same character on subsequent calls to this function ; Initially $iCountown should be passed with a value = 63743 ; =============================================================================================================================== Func __GetSubstitute($string, ByRef $iCountdown) If $iCountdown < 57344 Then Return SetError(1, 0, "") ; Out of options Local $sTestChar For $i = $iCountdown To 57344 Step -1 $sTestChar = ChrW($i) $iCountdown -= 1 If Not StringInStr($string, $sTestChar) Then Return $sTestChar EndIf Next Return SetError(1, 0, "") ; Out of options EndFunc ;==> __GetSubstitute  
       
      Edit:
      The code above is pretty junk (my part at least) so I wanted to improve it... This will return an array of arrays based on the excel file
      #include <Array.au3> Func _GetExcelArrays($fnImportFile) $oExcel = ObjCreate("Excel.Application") $oExcel.Visible=False $oBook= $oExcel.Workbooks.Open($fnImportFile) $sheetCount=$oBook.Worksheets.Count Local $aReturnArray[$sheetCount] For $x=1 to $sheetCount $oSheet=$oBook.Worksheets($x) $oSheet.Activate $fnMaster=@TempDir&"\"&Chr(Round(Random(0,24),0)+64)&Random(0,24)&Chr(Round(Random(0,24),0)+64)&Random(0,24)&Chr(Round(Random(0,24),0)+64)&".csv" $oSheet.SaveAs($fnMaster, 6) $aReturnArray[$x-1]=_CSVSplit(FileRead($fnMaster)) FileDelete($fnMaster) Next $oBook.Close(False) $oExcel.Quit Return $aReturnArray EndFunc  
×