CSV_Split & Listview Array

Hobbyist · November 24, 2016

This script uses code from Czardas & Guiness, for proper credit, as well as my newbie code.

I need to be able to run CSV files through a script I have created and the CSV file input needs to be in a given order.

The proper order of fields is: Posted Date,Reference Number,Payee,Address,Amount.

Some files received are NOT in this order and so my attached script is to convert them into the proper order. It is the process I have chosen, so I bet there are other ways as well. The idea is to load a CSV file into a listview for REVIEW of order, drag the columns to the proper order (using the template in the lower listview), convert the loaded file to the proper order in the lower listview and save the result. That result is an array saved to file in CSV format.

My script using the converted CSV files gets the desired calculations and results, so it seems to be working.

Here is the problem I have discoverd and cannot solve.

The CSV files in original order of : Posted Date,Reference Number,Payee,Address,Amount OPEN up fine in Excel or MsWorks as a spreadsheet - all in correct columns.

The converted CSV files, while working in my script, open as spreadsheet but NOT in the same organized order as an Standard type.

When I look at both in Notepad everything looks fine, but I also have been told that is not a good place to look at CSV files.

I have attached a Standard File (which has the correct field order for my script) as it is received.

I have also attached file that needs to be converted to the order of the Standard File. It is this out of order type file I have written the attached script. If you open both of them as spreadsheet you will see the Standard Files opens organized whereas the non Standard File is not.

I would like to get the non Standard file to properly open as a spreadsheet even though it does work in my other script. The reason is I will want to further develop my script to aggregate several files into one. This will work in the case of the Standard File but not the non Standard File. And I just don't know what I can do to fix this.

And much Thanks to Czardas & Guiness for their script.

Hobbyist

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

#include <FileConstants.au3>
#include <MsgBoxConstants.au3>
#include <Array.au3>
#include <ButtonConstants.au3>
#include <ColorConstants.au3>
#include <ComboConstants.au3>
#include <Date.au3>
#include <EditConstants.au3>
#include <File.au3>
#include <GUIComboBox.au3>
#include <GUIConstantsEx.au3>
#include <GUIListBox.au3>
#include <GuiListView.au3>
#include <ListViewConstants.au3>
#include <Misc.au3>
#include <MsgBoxConstants.au3>
#include <StaticConstants.au3> ;11/7
#include <String.au3> ; Only used to fill array
#include <WindowsConstants.au3>
#include <StringConstants.au3>
#include <FileConstants.au3>
#include <GuiTab.au3>
 #include <GuiButton.au3>
 #include <GuiMenu.au3>
 #include <WinAPI.au3>
 #include <Constants.au3>
#include <WinAPIFiles.au3>
#include-once
;--------------------------------------------------------------------
;Concept is to take any CSV file and run it through my script.  This portion is just for converting
;My script is written to accept fields in a certain order.
;The order for a csv file to work in my script is : Posted Date,Reference Number,Payee,Address,Amount
;The below script is to allow viewing a csv file in a listview and rearrange the column to the needed correct order for the script.
;The columns in the top listview are moveable and should be moved to matched the order in the lower listview.
;Saved button saves the array(used to create the lower listview) in csv format.
;Additionally the converted csv file should be able to be opened in Excel or MsWorks - THIS IS WHERE THE DIFFERENCE HAPPENS.
;--------------------------------------------------------------------------
;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
$main = GUICreate(" Dash Board", 680, 515, 150, 100)

$Button12 = GUICtrlCreateButton("Import Files", 10, 60, 120 ,33)
GUICtrlSetState($Button12,$GUI_enABLE)
GUICtrlSetFont(-1, 8, 800, 0, "MS Sans Serif")
GUICtrlSetColor(-1, 0xFF0000)
GUICtrlSetBkColor(-1, 0xE3E3E3)

$Button13 = GUICtrlCreateButton("Convert Record", 10, 100, 120, 33)
GUICtrlSetState($Button13,$GUI_enABLE)
GUICtrlSetFont(-1, 8, 800, 0, "MS Sans Serif")
GUICtrlSetColor(-1, 0xFF0000)
GUICtrlSetBkColor(-1, 0xE3E3E3)

$Button14 = GUICtrlCreateButton("Save Data", 10, 140, 120, 33)
GUICtrlSetState($Button14,$GUI_enABLE)
GUICtrlSetState($Button14,$GUI_FOCUS)
GUICtrlSetFont(-1, 8, 800, 0, "MS Sans Serif")
GUICtrlSetColor(-1, 0xFF0000)
GUICtrlSetBkColor(-1, 0xE3E3E3)

$Button15 = GUICtrlCreateButton("Cancel", 10, 180, 120, 33)
GUICtrlSetState($Button15,$GUI_enABLE)
GUICtrlSetFont(-1, 8, 800, 0, "MS Sans Serif")
GUICtrlSetColor(-1, 0xFF0000)
GUICtrlSetBkColor(-1, 0xE3E3E3)

$List10 =  GUICtrlCreateListview("",  154, 40,500, 200 );$LVS_EX_HEADERDRAGDROP;,$LVS_SINGLESEL, $LVS_EX_GRIDLINES + $LVS_EX_FULLROWSELECT)
_GUICtrlListView_SetExtendedListViewStyle ($List10,BitOR( $LVS_EX_HEADERDRAGDROP, $LVS_EX_GRIDLINES))
_GUICtrlListView_AddColumn($List10, "A", 70)
_GUICtrlListView_AddColumn($List10, "B", 70)
_GUICtrlListView_AddColumn($List10, "C", 70)
_GUICtrlListView_AddColumn($list10, "D", 70)
_GUICtrlListView_AddColumn($list10, "E", 70)
_GUICtrlListView_AddColumn($list10, "F", 70)
 GUICtrlSetFont(-1, 8.5, 700, 0, "MS Ariel")
GUICtrlSetBkColor($List10, $COLOR_aqua)
GUICtrlSetFont(-1, 8.5, 700, 0, "MS Ariel")
GUICtrlSetState($List10,$GUI_enABLE);
GUICtrlSetState($List10,$GUI_show)

$list11 =  GUICtrlCreateListview("",  154,280, 500, 200,$LVS_SINGLESEL, $LVS_EX_GRIDLINES + $LVS_EX_FULLROWSELECT);,$LVS_SINGLESEL, $LVS_EX_GRIDLINES + $LVS_EX_FULLROWSELECT)
;The order for a csv file to work in my script is : Posted Date,Reference Number,Payee,Address,Amount
_GUICtrlListView_AddColumn($List11, "Date",70)
_GUICtrlListView_AddColumn($list11, "Code/Ref/Blank",100)
_GUICtrlListView_AddColumn($list11, "  Vendor", 175)
_GUICtrlListView_AddColumn($list11, "Address/Blank", 70)
_GUICtrlListView_AddColumn($list11, "Amount", 60)
_GUICtrlListView_AddColumn($list11, "Paid", 40)
 GUICtrlSetFont(-1, 8.5, 700, 0, "MS Ariel")
GUICtrlSetBkColor($list11, $COLOR_aqua)
GUICtrlSetFont(-1, 8.5, 700, 0, "MS Ariel")
GUICtrlSetState($list11,$GUI_enABLE);
GUICtrlSetState($list11,$GUI_show)

global $a_order[0]
global $aListView [0] [0]
global $sDelimiters
global $sQuote
global $sfile
global $statementfile
global  $iFormat
global $sDelim
GUISetState(@SW_SHOW)

While 1

        $iMsg = GUIGetMsg()
         Switch $iMsg

                    Case $GUI_EVENT_CLOSE

                 Exit

             case $Button12  ;import downloaded statement to convert

                _CSVSplit()  ;Czardas as below

             case $Button13   ; convert statement data to needed format for processing

                 $a_order = _GUICtrlListView_GetColumnOrderArray($List10)
                _ArrayDelete (  $a_order, 0)

                _GUICtrlListView_CreateArray_Card() ;Guiness as below

            Case  $Button14         ; save results

                _SaveConvert()

            Case  $Button15         ; cancel entire process - import and conversion
                _GUICtrlListView_DeleteAllItems ( $List10 )
                _GUICtrlListView_DeleteAllItems ( $List11 )
         EndSwitch

WEnd


;guiness below
Func _GUICtrlListView_CreateArray_Card()    ;temp array to register changes in listview during editing process.

   Local $iRows = _GUICtrlListView_GetItemCount($list10)
   Local $iCols = _GUICtrlListView_GetColumnCount($list10)
   ReDim $aListView[$iRows][$iCols]
   For $i = 0 To $iRows -1
      For $j = 0 To 4;$iCols -1
          Local $aItem = _GUICtrlListView_GetItem($list10, $i, $a_order[$j])
          $aListView[$i][$j] = $aItem[3]
            $aListView[$i][3] = $aListView[$i][2]
                if $aListView[$i][4] > 0 Then
                    $aListView[$i][4] = $aListView[$i][4] *-1
                        EndIf


      Next
  Next
_ArrayDisplay($aListView,"???? converted") ;just taking a look while working out the bugs
;this is array used for lower listview, saved below _saveConvert
_GUICtrlListView_AddArray ( $List11, $aListView ) ; move adjusted colimns to lower listview from array created


EndFunc  ;<==> _GUICtrlListView_CreateArray()

Func _SaveConvert()
;this should now be csv file in required order:  Posted Date,Reference Number,Payee,Address,Amount(as above)
_FileWriteFromArray ("C:\Dash Board\converted "&today()&".csv", $aListView,0,Default,","); save new array to file

If Not @error Then
    MsgBox($MB_SYSTEMMODAL, " Status", ""& "  File Saved")
    Else
    MsgBox($MB_SYSTEMMODAL, " Status", "Error Code  "&  @error & "  File Not Saved")
EndIf

    _GUICtrlListView_DeleteAllItems ( $List10 ) ; clean house
    _GUICtrlListView_DeleteAllItems ( $List11 ) ; clean house

    EndFunc


Func today() ;Return the current date in mm/dd/yyyy form
    Return (@MON & "-" & @MDAY & "-" & @YEAR)
EndFunc   ;==>today

;czardas below

Func _CSVSplit() ; Parses csv string input and returns a one or two dimensional array
     Local Const $sMessage = "Select a single file .CSV file."
 Local $sFileOpenDialog = FileOpenDialog($sMessage, "" & "c:\", "Text & Commas(*.csv)", $FD_FILEMUSTEXIST)
$statementfile = $sFileOpenDialog

$sDelim = ","
$csv = FileRead ($statementfile)

_GUICtrlListView_AddArray ( $List10,$csv);$aCSV - populate the initial listview to review field order

    If Not IsString($csv) Or $csv = "" Then Return SetError(1, 0, 0) ; Invalid string
    If Not IsString($sDelim) Or $sDelim = "" Then Return SetError(2, 0, 0) ; Invalid string

    $csv = StringRegExpReplace($csv, "[\r\n]+\z", "") ; [Line Added] Remove training breaks
    ConsoleWrite($csv)
    Local $iOverride = 63743, $asDelim[3] ; $asDelim => replacements for comma, new line and double quote
    For $i = 0 To 2
        $asDelim[$i] = __GetSubstitute($csv, $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($csv, '\A[^"]+|("+[^"]+)|"+\z', 3) ; Split string using double quotes delim - largest match

    $csv = ""

    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
        $csv &= $aArray[$i] ; Rebuild the string, which includes two different delimiters
    Next
    $iOverride = 0

    $aArray = StringSplit($csv, $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
_ArrayDisplay($aCSV," split   $aCSV ");
_GUICtrlListView_AddArray ( $List10,$aCSV);$aCSV
    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
_ArrayDisplay($aArray," split")
EndFunc ;==> _CSVSplit

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

Standard File.csv

Need2Convert_205xxxx.csv

snoopie · November 24, 2016

If you open the need2convert CSV in Scite and go to View -> End of Line (CTRL + SHIFT + 9) you will see some random @LF characters which you should first eliminate with

$sNeed2convertCSV = StringReplace($sNeed2convertCSV, @LF, "")

Edited November 24, 2016 by snoopie
Typo

kylomas · November 24, 2016

~~need2convert is also missing a close quote (") at the end of description.~~

How are these files generated?

Quote is on next line due to the extraneous(?) LF

Edited November 24, 2016 by kylomas

mikell · November 24, 2016

It seems that line #243 should be like this

$aCSV[$i][$j -1] = StringStripWS($aTemp[$j], $STR_STRIPLEADING+$STR_STRIPTRAILING) ; Populate each row

czardas · November 24, 2016

56 minutes ago, mikell said:
It seems that line #243 should be like this
$aCSV[$i][$j -1] = StringStripWS($aTemp[$j], $STR_STRIPLEADING+$STR_STRIPTRAILING) ; Populate each row

I'm not sure I understand this. I haven't looked in detail at the problem. If a field contains characters of any kind, removing them would constitute corruption. I'll have to look later and see what's going on.

Edited November 24, 2016 by czardas

czardas · November 24, 2016

If the columns appear in a different order, then I suspect that something is happening with the ListView code: csv format does not contain information about column order. A ListView control can display the columns in any order. If there is one small mistake in the code which handles column order, then the columns could appear almost anywhere.

I can't test it right now.

Edit: Removed a suggestion which was wrong.

Edited November 24, 2016 by czardas

Hobbyist · November 24, 2016

First question response - I just get the file as is. Don't know if the order is going to be the same all the time.

Second - The purpose of the top Listview is to load the csv file in order to examine the order of data. Upon seeing that, I can drag the columns in the top Listview to match the column order of the lower Listview (its like a template).

The top Listview reordered columns are reflected in the lower Listview and an array is created based upon the lower Listview order. This new array is then saved to file as a CSV.

The new CSV(converted) file works in my other script. What I noticed is IF I then open the new (converted)CSV file in a spreadsheet, it is NOT formatted as the Standard CSV file I also submitted. It is this issue that is my concern, since using the files in spreadsheet is also needed.

Additionally I had previously tried to load the Need2Convert file into my other script and data was all over the place - IT was then I got directed to Czardas CSVSplit code that worked wonders on getting my Need2Convert file to work in my script. Using that plus Guiness to create my new array was fine.

The first suggestion of removing @LF makes sense but not sure as to how that impacts CSVSplit.

mikell · November 24, 2016

4 hours ago, czardas said:

I'm not sure I understand this.

I just noticed that in the "Need2Convert_205xxxx.csv" file there were unwanted newlines truncating the lines, so a quote seemed missing at the end of description - and of course the file couldn't display correctly

czardas · November 25, 2016

I'll have to take a look, but CSVSplit parses that file correctly. The issue is elsewhere.

I don't understand this:

$aListView[$i][3] = $aListView[$i][2] ; what's going on here?
                if $aListView[$i][4] > 0 Then
                    $aListView[$i][4] = $aListView[$i][4] *-1
                        EndIf

Hobbyist · November 25, 2016

I fully apoloize for uploading the incorrect file that I refer to as needing to be converted.

The attached file is, indeed, the correct file I am attempting to put through the "convert" portion of my script.

When I load it in the above script, the rows are all over the place. Please us the above script and run it to see what I mean.

Also for the question:

$aListView[$i][3] = $aListView[$i][2] ; what's going on here?
                if $aListView[$i][4] > 0 Then
                    $aListView[$i][4] = $aListView[$i][4] *-1
                        EndIf

this is used to copy the name and/or address into the next column(needed later in my script). Sometimes it is provided when I get the file and sometimes not, but either way [$i3] should NOT be blank when I create the array. The lower Listview shows the column headings and the needed order. So if a file had the date in column 2 for example(upper Listview) I simply drag it to the appropriate order shown in the lower Listview. $i4 is checked for pos/neg value and adjusted accordingly. After viewing the lower Listview for all column changes, the new array is created so I can get a new CSV file.

Again what I have experienced is the final converted file does not properly open in spreadsheet, even though I created a CSV file written from the lower Listview. So I am not sure what would do that. The Standard CSV file include in my OP runs in my script and opens fine in spreadsheet (nothing is changed with it, runs as received).

My entire attempt here is to view a file before running, to see if all the data is in the right order and IF NOT, reorder it and save it to a new CSV file(not destroy the original) so it runs just like the Standard I referred to.

Admittedly there may be a better way, but I am so new at this. And find so much I don't know. For instance the @LF response to my OP helped me understand that LF will not show up in notepad, but definitely impact results.

I really appreciate the help and responses I am getting.

February2016_205MY LF.CSV

czardas · November 25, 2016

Well it all sounds mysterious to me. For special reorganizing of the data, I think the best approach is to do all modifications within an array and use a bespoke function to transfer the data to the ListView control. Certain characters (such as pipe) may corrupt data when you use certain functions from the ListView UDF, but there are ways you can prevent this. Changes to column order need to be tracked: so that they can be accounted for when reading or writing to the ListView control.

How was the csv that you just posted created? It looks like corruption occurred during creation (from a visual perspective), although the csv format is actually correct. One thing you should know about CSVSplit is that all line breaks outside of any enclosed field (within double quotes) are each treated as a new row, regardless of how many commas are within the previous record. The alternative would be to reject the file as being incorrect csv format, but it isn't (according to the specifications I read at the time). CSVSplit also parses this file correctly. You should check the data is correct before generating the csv and make sure the generated file is formatted as you expect.

Edited November 25, 2016 by czardas

Hobbyist · November 25, 2016

When i run the code in the posted script, the listview box is populated like this(below). Did you try this also? Or are you using your CSVsplit function outside of the code (perhaps my error). As you can see column alignment would be wrong.

I though I had just dropped your CSVSplit into the script and added the _GUICtrlListView_AddArray ( $List10,$aCSV), thus taking the submitted file, running it through CSVSplit and then add it to my Listview. So I am using the array created in CSVSplit to add items to listview and NOT get what is represented below.

What i did do is, opened the file in SCITE as one response suggested and checked line view. I find at the end of each line there is CRLF. Is that is what is causing the break? CSVSplit correct this?

WHAT I Get:

"Status","Date","Description","Debit","Credit"
"Cleared","1/16/2016","CHINA STARFIRE SAINT Elsewhere XX ",,
",""19.92"",",,,,
"Cleared","1/27/2016","THE HOME DEPOT 3028 SAINT Elsewhere XX ",,
",""9.09"",",,,,
"Cleared","2/5/2016","PAYMENT THANK YOU",,
","",""25.00",,,,

Should your CSVSplit change it to?:

"Status","Date","Description","Debit","Credit"

"Cleared","1/16/2016","CHINA STARFIRE SAINT Elsewhere XX ",, ",""19.92"",",,,,

"Cleared","1/27/2016","THE HOME DEPOT 3028 SAINT Elsewhere XX ",, ",""9.09"",",,,,

"Cleared","2/5/2016","PAYMENT THANK YOU",, ","",""25.00",,,,

I will take the file in question and run it just in CSVSplit outside my above script and display an array to see if I get a good read like you. I will update you as soon as I can do it.

And thank you very much for being sooooooo patient with me.

Hobbyist · November 25, 2016

Local $sFilePath =  "C:\Dash Board\Statement\February2016_205MY LF.CSV"  

Local $hFile = FileOpen($sFilePath)
If $hFile = -1 Then
    MsgBox(0, "", "Unable to open file")
    Exit
EndIf

Local $sCSV = FileRead($hFile)
If @error Then
    MsgBox(0, "", "Unable to read file")
    FileClose($hFile)
    Exit
EndIf
FileClose($hFile)

Local $aCSV = _CSVSplit($sCSV) ; Create the main array
If @error Then
    ConsoleWrite("Error = " & @error & @LF)
    Exit
EndIf

_ArrayDisplay($aCSV);run submitted file through CSVSplit and display array, use array to populate Listview.

this is the resulting array with row 2 & 4& 6 being out of place(using the correct file I submitted this morning). they should be each part of the preceeding line. You are getting this same result, when you say CSVSplit works????

Row|Col 0|Col 1|Col 2|Col 3|Col 4
[0]|Status|Date|Description|Debit|Credit
[1]|Cleared|1/16/2016|CHINA STARFIRE SAINT Elsewhere XX ||
[2]|,"19.92",||||
[3]|Cleared|1/27/2016|THE HOME DEPOT 3028 SAINT Elsewhere XX ||
[4]|,"9.09",||||
[5]|Cleared|2/5/2016|PAYMENT THANK YOU||
[6]|,","25.00||||

Using:

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

czardas · November 25, 2016

Yes that is the way the file is formatted. The csv data contains 7 rows. It looks like there should only be four rows, but the line breaks are not enclosed. They need to be hidden within enclosed fields in the csv data, otherwise you will get 7 rows.

Quote

CSV is a delimited data format that has fields/columns separated by the comma character and records/rows terminated by newlines.

https://en.wikipedia.org/wiki/Comma-separated_values#Basic_rules

Edited November 25, 2016 by czardas

Hobbyist · November 26, 2016

Ok, Now I am a little confused by your last post, given my post that proceeded it.

So when you say your CSV_Split handles/read the file are you saying it reads and it would show four rows or still the seven rows when I output it? If your answer is four, I have something else wrong, but if the answer is seven I think I misunderstood what your CSV_Split was to be used for. You are way more advanced at this than I am.

kylomas · November 26, 2016

Hobbyist,

EOL's (CRLF, LF, CR) delimit each line unless the EOL is enclosed (for example in quotes). Czardas's code is acting according to accepted rules and splitting on all CRLF's in the file provided. In fact, when I open your file in Excel I get the same thing, 7 lines with the Credit/Debit column on it's own line. If this is a consistent file format then it can easily be dealt with, otherwise you may want to see if you can control the format from the source (whatever that is).

kylomas

czardas · November 26, 2016

@kylomas Thanks! Yes, my code interprets the file exactly the same as Excel, i.e. 7 rows.

Edited November 26, 2016 by czardas

Hobbyist · November 26, 2016

Kylomas & Czardas

Thanks for the input and guidance. My experience with receiving files is that even if I think I have control over something, someone ends up through a wrench into the process. Having received different formats, I thought I could build a review process by which I could see things "as they are" and rearrange them to be "things as they are needed". So when I stumbled upon Czardas' function I was excited but also misunderstood what I could get out of it. And not having the major experience or skill, as both of you, I guess I really overestimated the success of such a venture.

And again thanks for your patience.

Hobbyist

Hobbyist · November 26, 2016

It is easier to give birth than to resurrect, but I will take a shot at asking this.

If @crlf causes a new line would it work to:

1.read line #1 of the file and count the fields in use

2. strip away all @crlf in the file

3. for every nth string insert a @crlf until the end

4. save the file for reading. avoid the situation above of 7 rows versus 4 rows.

Trying to think this through before reaching for the Excedrin bottle.

Thank you and looking for your expert opinion and feedback.

czardas · November 26, 2016

Without knowing how this one corrupt file was created, the only thing you can do is analyse the format.

Your suggested solution:

"Cleared","1/16/2016","CHINA STARFIRE             SAINT Elsewhere XX ",, ",""19.92"",",,,,

should probably read:

"Cleared","1/16/2016","CHINA STARFIRE             SAINT Elsewhere XX ","19.92",

OR

"Cleared","1/16/2016","CHINA STARFIRE             SAINT Elsewhere XX ","""19.92""",

OR

"Cleared","1/16/2016","CHINA STARFIRE             SAINT Elsewhere XX ",,"""19.92"""

OR

"Cleared","1/16/2016","CHINA STARFIRE             SAINT Elsewhere XX ",,"19.92"

etc...

~~Assuming that~~ this is the result of a bug: you can only guess in which column 19.92 belongs. I think the last two formats are the most likely (notice that I removed several commas and modified the double quotes). Only by knowing the corruption process, which caused this file to be formatted in this strange way, might it be possible to reverse the damage to the syntax.

It is impossible to spot the error automatically, because the file's syntax is perfect for 7 rows (no errors). Once again, it is the code that generated the csv that needs to be fixed, or the program needs to be analysed.

Perhaps you corrupted this file yourself without realizing it - I can think of several ways you might have done that.

Edited November 27, 2016 by czardas
some mistakes

CSV_Split & Listview Array

Recommended Posts

Link to comment

Share on other sites

Top Posters In This Topic

Top Posters In This Topic

Popular Posts

kylomas

mikell

mikell

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Create an account or sign in to comment

Create an account

Sign in

Recently Browsing 0 members