Jump to content

Change Delimiter For Excel CSV


Recommended Posts

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
Link to comment
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 :)

Link to comment
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

Link to comment
Share on other sites

  • 3 years later...

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
Link to comment
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.
Link to comment
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 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

My post was meant for Jewtus ;)

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
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
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...