;-----------------------------------------------------------------------------------------------------------------------------------------
#DefineFunction udfCheckCSVFile (strCSVFile, strDelimiter, blnTrim)
; Remove leading and trailing empty lines.
If !!blnTrim
   intBytesWritten = FilePut (strCSVFile, StrReplace (StrTrim (StrReplace (FileGet (strCSVFile), @CRLF, @TAB)), @TAB, @CRLF))
EndIf

blnRetVal = @TRUE
strFileTemp = FileCreateTemp ("WB")

arrCSVFile = ArrayFileGet (strCSVFile)
intLineLast = ArrInfo (arrCSVFile, 1) - 1

IntControl (72, 1, @TRUE, "", 0) ; On next cancel event goto label :CANCEL.

For intLine = 0 To intLineLast
   intBytesWritten = FilePut (strFileTemp, arrCsvFile[intLine])

   intLastEM = ErrorMode (@OFF)
   LastError ()
   arrTest = ArrayFileGetCSV (strFileTemp, 0, strDelimiter)
   intLastError = LastError ()
   ErrorMode (intLastEM)
   If intLastError > 0
      blnRetVal = @FALSE
      Pause ("udfCheckCSVFile | Error in CSV line", "File: " : strCSVFile : @LF : "Line: " : intLine + 1 : @LF : arrCsvFile[intLine])
   EndIf
Next

:CANCEL
Drop (arrTest, arrCSVFile)
blnresult = FileDelete (strFileTemp)

Return blnRetVal
;..........................................................................................................................................
; This UDF udfCheckCSVFile is a simple helper to check a given CSV file for possible formatting errors.
;
; The checkup itself relies on the built-in rules of the WinBatch function ArrayFileGetCSV.
; If an input line cannot be accepted to be a correct CSV line, then the function ArrayFileGetCSV
; returns the minor error "1808 File contains invalid CSV line(s)".
; This UDF catches the error and offers a simple message to the user.
;
; This UDF expects that the given CSV File
; - has CRLF end of line delimiter;
; - has not any @TAB character;
; - allows read and write access when removing leading and trailing empty lines.
;
; This UDF returns the boolean value @TRUE (1) or @FALSE (0) to indicate that the given file can be used as a CSV file or not.
;
; Detlev Dalitz.20100814.
;..........................................................................................................................................
#EndFunction
;------------------------------------------------------------------------------------------------------------------------------------------


; Test.

; Create temporary test file.
strCSVFile = FileCreateTemp ("CSV")


; Build CSV lines with acceptable content. Delimiter is semicolon.
strLine1 = `1;"Field 1.2";11.1;"Field 1.4"`
strLine2 = `2;"Field 2.2";22.2;"Field 2.4"`
strLine3 = `3;"Field 3.2";33.3;"Field 3.4"`
intBytesWritten = FilePut (strCSVFile, strLine1 : @CRLF : strLine2 : @CRLF : strLine3)

blnResult = udfCheckCSVFile (strCSVFile, ";", @FALSE)

strMsgTitle = "Check CSV File"
strMsgText = "File: " : strCSVFile : @LF : "Result: " : ItemExtract (1 + blnResult, "failed,passed", ",")
Pause (strMsgTitle, strMsgText)


; Build CSV lines with non acceptable content. Delimiter is semicolon.
strLine1 = `1;"Field 1.2";11,1;"Field 1.4"` ; Decimal comma instead of decimal point is no error.
strLine2 = `2;"Field 2.2";22.2:"Field 2.4"` ; Bad delimiter, colon instead of semicolon.
strLine3 = `3;"Field 3.2";33.3;"Field 3.4`  ; Missing double apostrophe after last item.
intBytesWritten = FilePut (strCSVFile, strLine1 : @CRLF : strLine2 : @CRLF : strLine3)

blnResult = udfCheckCSVFile (strCSVFile, ";", @FALSE)

strMsgTitle = "Check CSV File"
strMsgText = "File: " : strCSVFile : @LF : "Result: " : ItemExtract (1 + blnResult, "failed,passed", ",")
Pause (strMsgTitle, strMsgText)


:CANCEL
blnResult = FileDelete (strCSVFile)
Exit