Hobbyist Posted October 28, 2018 Posted October 28, 2018 expandcollapse popup#Region ;**** Directives created by AutoIt3Wrapper_GUI **** #AutoIt3Wrapper_Outfile=J:\Dash Board 2018.exe #AutoIt3Wrapper_AU3Check_Parameters=-q -d -w 1 -w 2 -w 3 -w- 4 -w 5 -w 6 -w- 7 #AutoIt3Wrapper_Run_Tidy=y #AutoIt3Wrapper_Run_Au3Stripper=y #EndRegion ;**** Directives created by AutoIt3Wrapper_GUI **** ;<<<<<<<<<<<<<<<<<<<<<<<< #include <Array.au3> #include <ButtonConstants.au3> #include <ColorConstants.au3> #include <File.au3> #include <GUIConstantsEx.au3> #include <GuiListView.au3> #include <ListViewConstants.au3> #include <MsgBoxConstants.au3> #include <WindowsConstants.au3> #include <GuiButton.au3> ;<<<<<<<<<<<< Global $list10 Global $list11 Global $a_order[0] Global $aListView[0][0] ;used in adding category to budget Global $main3 Local $main = GUICreate("Dash Board", 680, 515, 150, 100) Local $Button15 = GUICtrlCreateButton("Log File", 10, 180, 158, 33) GUICtrlSetState($Button15, $GUI_enABLE) GUICtrlSetFont(-1, 8.5, 800, 0, "Verdana") GUICtrlSetColor(-1, 0xFF0000) GUICtrlSetBkColor(-1, 0xE3E3E3) GUISetState(@SW_SHOW) While 1 Local $iMsg = GUIGetMsg() Switch $iMsg Case $GUI_EVENT_CLOSE Exit Case $Button15 _importChild() EndSwitch WEnd Func _importChild() $main3 = GUICreate("Dash Board", 679, 490, 1, 1, $WS_POPUP, $WS_EX_MDICHILD, $main) Local $Button125 = GUICtrlCreateButton("Import Files", 20, 70, 120, 33) GUICtrlSetState($Button125, $GUI_enable) GUICtrlSetFont(-1, 8.5, 800, 0, "Verdana") GUICtrlSetColor(-1, 0xFF0000) GUICtrlSetBkColor(-1, 0xE3E3E3) Local $Button135 = GUICtrlCreateButton("Convert Records", 20, 125, 120, 33) GUICtrlSetState($Button135, $GUI_enable) GUICtrlSetFont(-1, 8.5, 800, 0, "Verdana") GUICtrlSetColor(-1, 0xFF0000) GUICtrlSetBkColor(-1, 0xE3E3E3) Local $Button145 = GUICtrlCreateButton("Save Data", 20, 180, 120, 33) ;~ GUICtrlSetState($Button14, $GUI_enable) ;~ GUICtrlSetState($Button14, $GUI_focus) ;~ GUICtrlSetState($Button145, $GUI_disABLE) ;~ GUICtrlSetFont(-1, 8.5, 800, 0, "Verdana") ;~ GUICtrlSetColor(-1, 0xFF0000) ;~ GUICtrlSetBkColor(-1, 0xE3E3E3) Local $Button155 = GUICtrlCreateButton("Cancel or Exit", 20, 235, 120, 33) ;GUICtrlSetState($Button15, $GUI_enable) GUICtrlSetFont(-1, 8.5, 800, 0, "Verdana") GUICtrlSetColor(-1, 0xFF0000) GUICtrlSetBkColor(-1, 0xE3E3E3) $list10 = GUICtrlCreateListView("", 154, 70, 505, 200) _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) ;----------------- _GUICtrlListView_AddColumn($list10, "G", 70) _GUICtrlListView_AddColumn($list10, "H", 70) ;------------ Local $columnorder = _GUICtrlListView_GetColumnOrder($list10) GUICtrlSetFont(-1, 8.5, 700, 0, "Verdana") GUICtrlSetBkColor($list10, 0x33D0C9) GUICtrlSetFont(-1, 8.5, 700, 0, "Verdana") GUICtrlSetState($list10, $GUI_enable); GUICtrlSetState($list10, $GUI_show) ;------------- ;This listview represents the default data sequence $list11 = GUICtrlCreateListView("", 20, 280, 640, 200, $LVS_SINGLESEL, $LVS_EX_GRIDLINES + $LVS_EX_FULLROWSELECT) _GUICtrlListView_AddColumn($list11, "Date", 80) _GUICtrlListView_AddColumn($list11, "Code/Ref/Blank", 125) ;basically anything _GUICtrlListView_AddColumn($list11, " Vendor", 185) _GUICtrlListView_AddColumn($list11, "Address/Blank", 120); repeats the vendor data _GUICtrlListView_AddColumn($list11, "Amt Dbt", 70); dollar amounts _GUICtrlListView_AddColumn($list11, "Amt Cr", 70) ;dollar amounts GUICtrlSetFont(-1, 8.5, 700, 0, "Verdana") GUICtrlSetBkColor($list11, 0x33D0C9) GUICtrlSetFont(-1, 8.5, 700, 0, "Verdana") GUICtrlSetState($list11, $GUI_enable); GUICtrlSetState($list11, $GUI_show) GUISetState(@SW_SHOW) While 1 Local $iMsg = GUIGetMsg() Switch $iMsg Case $Button125 ;import downloaded statement to convert _loadit() ;load statement download to listview; see what columns have to be moved to match template_ ;drag columns to match bottom listview11 default. GUICtrlSetState($Button135, $GUI_enable) Case $Button135 ; convert statement data to needed default , see titles in listview11 format for processing If $columnorder <> _GUICtrlListView_GetColumnOrder($list10) Then $a_order = _GUICtrlListView_GetColumnOrderArray($list10) _ArrayDelete($a_order, 0) _GUICtrlListView_CreateArray_Card() GUICtrlSetState($Button145, $GUI_enable) GUICtrlSetState($Button135, $GUI_disABLE) Else MsgBox(0, "Convert Error ", "No Columns Have Been Moved. " & @CR & "Adjust or Exit.") EndIf Case $Button155 ; cancel entire process - import and conversion _GUICtrlListView_DeleteAllItems($list10) _GUICtrlListView_DeleteAllItems($list11) GUICtrlSetState($Button145, $GUI_disABLE) GUIDelete($main3) ExitLoop EndSwitch WEnd EndFunc ;==>_importChild Func _loadit() Local Const $sMessage = "Select a single file .CSV file." Local $sFileOpenDialog = FileOpenDialog($sMessage, "" & "c:\", "Text & Commas(*.csv)", $FD_FILEMUSTEXIST) Global $statementfile = $sFileOpenDialog Global $csv = FileRead($statementfile) $csv = StringReplace($csv, '"', "") $csv = StringRegExpReplace($csv, '(\N+\R\N+)\K\R', "") ;mikell $csv = StringRegExpReplace($csv, ',,(?=,|\R)', "") ;mikell Local $array = _CSVSplit($csv) ;czardas _GUICtrlListView_AddArray($list10, $array) $array = 0 EndFunc ;==>_loadit Func _GUICtrlListView_CreateArray_Card() ;temp array to register changes in listview during editing process;during statement converting ;converts listview10 to listview11 AFTER columns have been moved. Local $iRows = _GUICtrlListView_GetItemCount($list10) Local $iCols = _GUICtrlListView_GetColumnCount($list10) ReDim $aListView[$iRows][$iCols] For $i = 0 To $iRows - 1 For $j = 0 To $iCols - 1 ;For $j = 0 To 5 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 ReDim $aListView[$iRows][6] _GUICtrlListView_AddArray($list11, $aListView) EndFunc ;==>_GUICtrlListView_CreateArray_Card ; #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 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 This script contains function from @czardas and 3 lines of script from @mikell, so I thank them. This script is to import a CSV file (three different formats are included here) into listview10, the top one. I then drag any column to match a position in the bottom template, Listview11. Click the convert button and the newly arranged listview10 is copied into listview11 which will be saved for processing. The Save button is not functional as that is not part of the issue. The issue is getting a downloaded file into the format I need for processing. Format ONE file is considered the default, as all other processing works with it. Format THREE will properly convert only IF I COMMENT OUT, mikell's lines (162,163,164) and leave czardas function intact. BUT in doing so, Format TWO will NOT work. Leave them in and Format Three will not properly convert. And czardas function remains intact. CSV files are confusing to me because of the different possible formats. So I am not sure if my problem arises with what I have included in the script in regards to trying to convert files to my preferred format (Format One) and for which I can do all my other processing. I would appreciate any help or guidance. And know that I am a newbie. @czardas if you see this, I would appreciate any help. @mikell I would appreciate any help as well. Thanks Hobbyist Format One.csv Format Three.csv Format Two.csv
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now