Sign in to follow this  
Followers 0
grasshopper3

Change Delimiter For Excel CSV

10 posts in this topic

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

Share this post


Link to post
Share on other sites



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.


SNMP_UDF ... for SNMPv1 and v2c so far, GetBulk and a new example script

wannabe "Unbeatable" Tic-Tac-Toe

Paper-Scissor-Rock ... try to beat it anyway :)

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.


SNMP_UDF ... for SNMPv1 and v2c so far, GetBulk and a new example script

wannabe "Unbeatable" Tic-Tac-Toe

Paper-Scissor-Rock ... try to beat it anyway :)

Share this post


Link to post
Share on other sites

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.


Scripts & functions Organize Includes Let Scite organize the include files

Yahtzee The game "Yahtzee" (Kniffel, DiceLion)

LoginWrapper Secure scripts by adding a query (authentication)

_RunOnlyOnThis UDF Make sure that a script can only be executed on ... (Windows / HD / ...)

Internet-Café Server/Client Application Open CD, Start Browser, Lock remote client, etc.

MultipleFuncsWithOneHotkey Start different funcs by hitting one hotkey different times

Share this post


Link to post
Share on other sites

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

#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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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. :idiot:

Share this post


Link to post
Share on other sites

My post was meant for Jewtus ;)

1 person likes this

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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 account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0