;==========================================================================================================================================
;
; How to format text report on CSV data?
;
; Example script for demonstration about some aspects of CSV file handling and manipulation.
;
;------------------------------------------------------------------------------------------------------------------------------------------
; Detlev Dalitz.20110303.
;==========================================================================================================================================

DirChange (DirScript ())

; Define Filenames.               ; Test data file is included in attached zip file.
strFileIn = "TestData.in.txt"     ; Nearly CSV format (no full compatible CSV  format).
strFileOut = "TestData.out.txt"   ; Output file with compatible CSV format.
strFileForm = "TestData.form.txt" ; Output file with width formatted columns.
strFileTemp = "TestData.tmp.txt"  ; Temporary file.

;------------------------------------------------------------------------------------------------------------------------------------------
; Repair a slightly invalid CSV file.
;------------------------------------------------------------------------------------------------------------------------------------------
; The input file should be a valid CSV formatted file, but here the file "TestData.in.txt" is not valid.
; Apparently the semicolon is used as the column delimiter, so it looks nearly like a CSV formatted file.
; But ...
; - because the data fields are not enclosed in double apostrophes,
; - because there are TAB characters in the fields,
; ... illegal characters like the TAB character must be removed from the input file,
; ... resp. the fields have to be enclosed into double apostrophes to save the TAB characters.
; Following solution simply removes the TAB characters.
;------------------------------------------------------------------------------------------------------------------------------------------

; Remove TAB characters from the file.
intBytes = FilePut (strFileTemp, StrClean (FileGet (strFileIn), @TAB, "", @TRUE, 1))
; Still not valid CSV data, because of missing double quotes around the data fields.

; Read the CSV data into dim-2 array and sort on specified column.
intSortCol = 2                                  ; 0 = First column.
arrCSV = ArrayFileGetCSV (strFileTemp, 0, ";")  ; Get nearly CSV formatted file into dim-2 array.
ArraySort (arrCSV, @DESCENDING, intSortCol)     ; Sort array descending on given column.
intBytes = ArrayFilePutCSV (strFileOut, arrCSV) ; Put CSV array to CSV file.
blnResult = FileDelete (strFileTemp)            ; Delete temporary file.
Drop (intSortCol, intBytes, blnResult, arrCSV)

; Check result.
Run (strFileIn, "")  ; Invalid CSV format
Run (strFileOut, "") ; Valid CSV format.


;------------------------------------------------------------------------------------------------------------------------------------------
; Format output for displaying purpose.
;------------------------------------------------------------------------------------------------------------------------------------------
; Get the valid output CSV file from the step before into a dim-2 array.
arrForm = ArrayFileGetCSV (strFileOut, 0)  ; Get CSV formatted file into temporary dim-2 array.
intRowLast = ArrInfo (arrForm, 1) - 1
intColLast = ArrInfo (arrForm, 2) - 1

; Define companion info dim-2 array with same amount of rows as columns in input data.
; Col0 = Column Vartype, Col1 = Column Width Integer, Col2 = Column Width Float Fraction.
arrFormInfo = ArrDimension (ArrInfo (arrForm, 2), 3)

; Define some special characters.
; Note: Characters must be adapted according to national settings.
strDecPoint = "."    ; Decimal Point character.
strCSVDelim = ","    ; CSV delimiter character.
strDoubleQuote = '"' ; Double Apostrophe character.
strReplaceChar = "_" ; Must be count minimal and maximal only one Byte.
strSaveChars = strDoubleQuote : strCSVDelim : strDecPoint

; Prepare temp array for measurement purposes.
intBytes = ArrayFilePutCSV (strFileTemp, arrForm)
intBytes = FilePut (strFileTemp, StrClean (FileGet (strFileTemp), @CRLF : strSaveChars, strReplaceChar, @TRUE, 2))
arrTemp = ArrayFileGetCSV (strFileTemp, 0)  ; Get CSV formatted file into temporary dim-2 array.
blnResult = FileDelete (strFileTemp)

; Measure maximum width of each column and guess data types.
For intCol = 0 To intColLast
   ArraySort (arrTemp, @DESCENDING, intCol)
   Switch @TRUE
   Case IsInt (arrForm [0, intCol])
      arrFormInfo[intCol, 0] = 1 ; Data type Integer.
      arrFormInfo[intCol, 1] = StrLen (arrTemp [0, intCol]) ; Column width.
      Break
   Case IsFloat (arrForm [0, intCol])
      arrFormInfo[intCol, 0] = 32 ; Data type Float.
      arrFormInfo[intCol, 1] = StrIndex (arrTemp [0, intCol], strDecPoint, 0, @FWDSCAN) - 1 ; Integer width.
      arrFormInfo[intCol, 2] = StrLen (arrTemp [0, intCol]) - arrFormInfo[intCol, 1] - 1 ; Fraction width.
      Break
   Case @TRUE
      arrFormInfo[intCol, 0] = 2 ; Data type String.
      arrFormInfo[intCol, 1] = StrLen (arrTemp [0, intCol]) ; Column width.
   EndSwitch
Next
Drop (strSaveChars, strReplaceChar, strDoubleQuote, strCSVDelim, intCol, intBytes, blnResult, arrTemp)

; Format all array cells.
strFiller = " " ; Fill character. Space character is sufficient.
For intRow = 0 To intRowLast
   For intCol = 0 To intColLast
      Switch arrFormInfo[intCol, 0]
      Case 1
         arrForm[intRow, intCol] = StrFixLeft (arrForm[intRow, intCol], strFiller, arrFormInfo[intCol, 1])
         Break
      Case 32
         arrFloat = Arrayize (arrForm[intRow, intCol], strDecPoint)
         arrFloat[0] = StrFixLeft (arrFloat[0], strFiller, arrFormInfo[intCol, 1])
         If ArrInfo (arrFloat, 1) > 1
            arrFloat[1] = StrFix (arrFloat[1], strFiller, arrFormInfo[intCol, 2])
            arrForm[intRow, intCol] = arrFloat[0] : strDecPoint : arrFloat[1]
         Else
            arrForm[intRow, intCol] = StrFix (arrFloat[0], strFiller, 1 + arrFormInfo[intCol, 2] + arrFormInfo[intCol, 1])
         EndIf
         Break
      Case 2
         arrForm[intRow, intCol] = StrFix (arrForm[intRow, intCol], strFiller, arrFormInfo[intCol, 1])
      EndSwitch
   Next
Next
Drop (strFiller, strDecPoint, intRowLast, intRow, intColLast, intCol, arrFloat, arrFormInfo)

intBytes = ArrayFilePutCSV (strFileForm, arrForm, "|", @FALSE, 2) ; Put array to CSV file.
Drop (intBytes, arrForm)

; Check result.
Run (strFileForm, "") ; Formatted text file.


:CANCEL
DropWild ("str*")
Drop (param0)
; The Drop() statements are not really needed, here in this example script applied just for fun.
Exit
;==========================================================================================================================================