Change Delimiter For Excel CSV

[grasshopper3]

I am using the excel UDF and I would like to be able to set the delimiter from my script. Does anyone know how to do this? I have a combobox in my GUI that has comma, semi-colon, pipe, tab. I would like to be able to use these delimiters for a CSV.

here is my first draft...

Func _ExcelConversion($StatusBar,$FilePath,$SaveType)
    
    _GUICtrlStatusBar_SetText($StatusBar,"Creating Temp File...")
    If FileCopy($FilePath,$FilePath&'temp')=0 Then
        _GUICtrlStatusBar_SetText($StatusBar,"Failed to Create Temp File")
        MsgBox(0,'Error','Unable to create Temp file' & @crlf & _
                            'Error Code: ' & @error,2)
        Return
    EndIf

    $Handle = _ExcelBookOpen($FilePath&'temp',0)
    _GUICtrlStatusBar_SetText($StatusBar,"Opening Temp File...")
    If $Handle=0 Then
        _GUICtrlStatusBar_SetText($StatusBar,"Failed to Open Temp File")
        MsgBox(0,'Error',"Unable to open Excel File" & @crlf & _
                            'Error Code: ' & @error,2)
        Return
    EndIf
    
    If _ExcelBookSaveAs($Handle,$FilePath&'temp',$SaveType,0,1)=0 Then
        _GUICtrlStatusBar_SetText($StatusBar,"Failed to Save Temp File")
        MsgBox(0,'Error','Unable to Save File As...' & @CRLF & _
                        'Error Code: ' & @error,2)
    EndIf   

    _ExcelBookClose($Handle,0,0)
    Sleep(50)
    _GUICtrlStatusBar_SetText($StatusBar,"Renaming Temp File...")
    If FileMove($FilePath&'temp',$FilePath&'.'&$SaveType)=0 Then
        FileDelete($FilePath&'temp')
        _GUICtrlStatusBar_SetText($StatusBar,"Failed to Rename Temp File")
        MsgBox(0,'Error','Failed to rename file' & @crlf & _
                         'Error Code: ' & @error,2)
    EndIf
    Return
EndFunc ;==>_ExcelConversion

[enaiman]

If you already have a csv file - just read all of it, replace every instance of "," with the delimiter of your choice then write the new file.

I you're using _ExcelBookSaveAS - save it as a *.csv then do what I said above.

[grasshopper3]

That won't work bc the delimiter character can be in the values and text qualifiers aren't always present.

[enaiman]

Well, best of luck then.

What is left for you is to make your own SaveAs function: create a file then write every cell data separated by your own delimiter.

[Xenobiologist]

Well, best of luck then.

What is left for you is to make your own SaveAs function: create a file then write every cell data separated by your own delimiter.

[Jewtus]

Crude work around for this... I built a little script (I used _ParseCSV UDF) that seems to get around this issue:

expandcollapse popup

#include <File.au3>

Func ConvertXLS2CSV($Input=Default,$output=Default,$workbookName=Default, $delimiter=Default)
    $OList=@TempDir&"\Temp"&@MON&"."&@MDAY&"."&@YEAR&".csv"
    $oExcel = ObjCreate("Excel.Application")
    $oBook= $oExcel.Workbooks.Open($Input)
    $Sheet=$oBook.WorkSheets.Item($workbookName)
    $Sheet.SaveAs($OList, 6)
    $oBook.Close(False)
    $oExcel.Quit
    $finalOut=_ParseCSV($OList,$delimiter)
    _FileWriteFromArray($output,$finalOut,Default,Default,"|")
    FileDelete($OList)
EndFunc

Func _ParseCSV($sFile, $sDelimiters = ',;', $sQuote = '"', $iFormat = 0, $iAddIndex = 0, $AddHeader = 0)
    Local Static $aEncoding[6] = [0, 0, 32, 64, 128, 256]
    If $iFormat < -1 Or $iFormat > 6 Then
        Return SetError(3, 0, 0)
    ElseIf $iFormat > -1 Then
        Local $hFile = FileOpen($sFile, $aEncoding[$iFormat]), $sLine, $aTemp, $aCSV[1], $iReserved, $iCount
        If @error Then Return SetError(1, @error, 0)
        $sFile = FileRead($hFile)
        FileClose($hFile)
    EndIf
    If $sDelimiters = "" Or IsKeyword($sDelimiters) Then $sDelimiters = ',;'
    If $sQuote = "" Or IsKeyword($sQuote) Then $sQuote = '"'
    $sQuote = StringLeft($sQuote, 1)
    $iAddIndex = Number($iAddIndex=True)
    $AddHeader = Number($AddHeader=True)
    Local $srDelimiters = StringRegExpReplace($sDelimiters, '[\\\^\-\[\]]', '\\\0')
    Local $srQuote = StringRegExpReplace($sQuote, '[\\\^\-\[\]]', '\\\0')
    Local $sPattern = StringReplace(StringReplace('(?m)(?:^|[,])\h*(["](?:[^"]|["]{2})*["]|[^,\r\n]*)(\v+)?', ',', $srDelimiters, 0, 1), '"', $srQuote, 0, 1)
    Local $aREgex = StringRegExp($sFile, $sPattern, 3)
    If @error Then Return SetError(2, @error, 0)
    $sFile = '' ; save memory
    Local $iBound = UBound($aREgex), $iIndex = $AddHeader, $iSubBound = 1+$iAddIndex, $iSub = $iAddIndex, $sLast='' ;changed
    If $iBound Then $sLast = $aREgex[$iBound-1]
    Local $aResult[$iBound + $iAddIndex][$iSubBound] ;changed
    For $i = 0 To $iBound - 1
        If $iSub = $iSubBound Then
            $iSubBound += 1
            ReDim $aResult[$iBound][$iSubBound]
        EndIf
        Select
            Case StringLeft(StringStripWS($aREgex[$i], 1), 1) = $sQuote
                $aREgex[$i] = StringStripWS($aREgex[$i], 3)
                $aResult[$iIndex][$iSub] = $aREgex[$i]
;~                 $aResult[$iIndex][$iSub] = StringReplace(StringMid($aREgex[$i], 2, StringLen($aREgex[$i])-2), $sQuote&$sQuote, $sQuote, 0, 1)
            Case StringRegExp($aREgex[$i], '^\v+$') ; StringLen($aREgex[$i]) < 3 And StringInStr(@CRLF, $aREgex[$i]) ;new line found
                StringReplace($aREgex[$i], @LF, "", 0, 1)
                $iIndex += @extended
                $iSub = $iAddIndex ;changed
                ContinueLoop
            Case Else
                $aResult[$iIndex][$iSub] = $aREgex[$i]
        EndSelect
        $aREgex[$i] = 0 ; save memory
        $iSub += 1
        If $iAddIndex Then $aResult[$iIndex][0] = $iIndex ;added
    Next
    If Not StringRegExp($sLast, '^\v+$') Then $iIndex+=1
    ReDim $aResult[$iIndex][$iSubBound - 1]
    If $iAddIndex Then $aResult[0][0] = "Index" ;added
    If $AddHeader Then
        For $i = 1 To $iSubBound - 2
            $aResult[0][$i] = "Col" & $i
        Next
    EndIf
    Return $aResult
EndFunc   ;==>_ParseCSV

[czardas]

Maybe this: '?do=embed' frameborder='0' data-embedContent>>

;

#include <CSVSplit.au3>

; Convert CSV to TSV
Local $sFilePath = @ScriptDir & "\test.csv" ; Change this to your own csv file path.

Local $hFile = FileOpen($sFilePath)
If $hFile = -1 Then Exit ; Unable to open file.

Local $sCSV = FileRead($hFile)
If @error Then ; Unable to read file.
    FileClose($hFile)
    Exit
EndIf
FileClose($hFile)

; Now the call the UDF functions.
Local $aCSV = _CSVSplit($sCSV, ",") ; Create the main array from the csv data.
If @error Then
    ConsoleWrite("Error = " & @error & @LF)
    Exit
EndIf

Local $sTSV = _ArrayToCSV($aCSV, @TAB) ; Use TAB delimiter.
If @error Then
    ConsoleWrite("Error = " & @error & @LF)
    Exit
EndIf

; Now you can write the converted TSV to file, or run another process.

[water]

The OP has been offline for about 3 1/2 years. So he won't read this new solution.

But you could have a look at_Excel_BookOpenText delivered with the latest version of AutoIt.

[czardas]

LOL, I didn't notice someone just answered a three and a half year old question. I just chimed in afterwards thinking it was a current topic.

[water]

My post was meant for Jewtus