Converting xls to csv using VBScript and separate by semicolons

Question

I have a VBScript code snippet which converts my xls and xlsx files into csv files. However, I want each cell to be separated by a semicolon rather than a comma. On my computer, the list separator is set to semicolon instead of comma so when I open up an excel window and do save as csv, it separates by semicolon. However, my VBScript produces a csv file separated by commas. I found the code snippet online as I do not really know VBScript (I'm mainly a Java Programmer) that well. How can I change the code snippet to separate the csv files by semicolon rather than by comma?

if WScript.Arguments.Count < 2 Then
WScript.Echo "Error! Please specify the source path and the destination. Usage: XlsToCsv SourcePath.xls Destination.csv"
Wscript.Quit
End If
Dim oExcel
Set oExcel = CreateObject("Excel.Application")
Dim oBook
Set oBook = oExcel.Workbooks.Open(Wscript.Arguments.Item(0))
oBook.SaveAs WScript.Arguments.Item(1), 6
oBook.Close False
oExcel.Quit
WScript.Echo "Done"

Solution 1

you can keep your original script, only need to give a parameter to indicate local setting must apply. This saves my CSV with a ; separator

if WScript.Arguments.Count < 2 Then 
  WScript.Echo "Error! Please specify the source path and the destination. Usage: XlsToCsv SourcePath.xls Destination.csv" 
  Wscript.Quit 
End If 
Dim oExcel 
Set oExcel = CreateObject("Excel.Application") 
oExcel.DisplayAlerts = FALSE 'to avoid prompts
Dim oBook, local
Set oBook = oExcel.Workbooks.Open(Wscript.Arguments.Item(0))
local = true 
call oBook.SaveAs(WScript.Arguments.Item(1), 6, 0, 0, 0, 0, 0, 0, 0, 0, 0, local) 'this changed
oBook.Close False 
oExcel.Quit 
WScript.Echo "Done" 

Solution 2

The Function SaveAs is defined so : .SaveAs(FileName, FileFormat, Password, WriteResPassword, ReadOnlyRecommended, CreateBackup, AccessMode, ConflictResolution, AddToMru, TextCodepage, TextVisualLayout, Local)

Thas is, to use the semicolon (if your regional language option are correctly set)

ExcelObj.Workbooks(1).SaveAs csvFile, 6,,,,,,,,,,True

Solution 3

The use of a comma in delimited text files finds its roots in the regional settings. While the comma is standard in the US, other countries such as Germany use the semicolon instead. You can change the List Separator value in the Regional and Language settings and then choose CSV (Comma delimited) (.csv) from Excel's Save As window. The resulting file will be delimited by whatever value is in the system settings. This script changes default List Separator setting. Then it opens the specified spreadsheet and resaves it. It reverts the system setting to its previous value before finishing.

It accepts two command line parameters. The first is the input spreadsheet; the second is the output filename for the exported file.

strDelimiter = ";"

strSystemDelimiter = ""           ' This will be used to store the current sytem value
Const HKEY_CURRENT_USER = &H80000001

' Get the current List Separator (Regional Settings) from the registry
strKeyPath = "Control Panel\International"
strValueName = "sList"
strComputer = "."
Set objRegistry = GetObject("winmgmts:\\" & strComputer & "\root\default:StdRegProv")
objRegistry.GetStringValue HKEY_CURRENT_USER, strKeyPath, strValueName, strSystemDelimiter

' Set it temporarily to our custom delimiter
objRegistry.SetStringValue HKEY_CURRENT_USER, strKeyPath, strValueName, strDelimiter

' Open spreadsheet with Excel and save it in a text delimited format
Const xlCSV = 6

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open(WScript.Arguments.Item(0))
objWorkbook.SaveAs WScript.Arguments.Item(1), xlCSV
objWorkbook.Close vbFalse         ' Prevent duplicate Save dialog
objExcel.Quit

' Reset the system setting to its original value
objRegistry.SetStringValue HKEY_CURRENT_USER, strKeyPath, strValueName, strSystemDelimiter

After some testing, it seems that this only works through Excel's Save As dialog and not through command-line or automation. I've changed the script a little to make the Excel window visible and use shortcuts keys to open the Save As dialog through the Excel interface. This should do the trick. It worked for me on Vista x64 with Excel 2007. I hope this works for you.

strDelimiter = ";"

strSystemDelimiter = ""           ' This will be used to store the current sytem value
Const HKEY_CURRENT_USER = &H80000001

' Get the current List Separator (Regional Settings) from the registry
strKeyPath = "Control Panel\International"
strValueName = "sList"
strComputer = "."
Set objRegistry = GetObject("winmgmts:\\" & strComputer & "\root\default:StdRegProv")
objRegistry.GetStringValue HKEY_CURRENT_USER, strKeyPath, strValueName, strSystemDelimiter

' Set it temporarily to our custom delimiter
objRegistry.SetStringValue HKEY_CURRENT_USER, strKeyPath, strValueName, strDelimiter

' Open spreadsheet with Excel and save it in a text delimited format
Const xlCSV = 6

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = vbTrue
Set objWorkbook = objExcel.Workbooks.Open(WScript.Arguments.Item(0))

WScript.Sleep 500                 ' Delay to make sure the Excel workbook is open
strWorkbookName = objExcel.ActiveWorkbook.Name
strTitlebar = strWorkbookName
Set WshShell = CreateObject("WScript.Shell")
WshShell.AppActivate strTitlebar  ' Make the workbook active so it receives the keystrokes
WshShell.SendKeys "%fa"           ' Keyboard shortcuts for the Save As dialog
WScript.Sleep 500
WshShell.SendKeys "%tc{ENTER}"    ' Change the Save As type to CSV
If WScript.Arguments.Count > 1 Then
    WshShell.SendKeys "+{TAB}" & WScript.Arguments.Item(1)
    WScript.Sleep 500
End If                            ' This If block changes the save name if one was provided
WshShell.SendKeys "{ENTER}"       ' Save the file
WScript.Sleep 500
WshShell.SendKeys "{ENTER}"       ' Dismiss the CSV warning dialog
Set WshShell = Nothing

objWorkbook.Close vbFalse         ' Prevent duplicate Save dialog
objExcel.Quit

' Reset the system setting to its original value
objRegistry.SetStringValue HKEY_CURRENT_USER, strKeyPath, strValueName, strSystemDelimiter

Comments

[AutomatedChaos]

That is not a consistent solution because it will also replace the comma's in literals: John Doe,Euro,"1,234.00" will resolve to John Doe;Euro;"1;234.00", probably not what the user wants.

[Nathan Rice]

Obviously if this is the data type, it wouldn't be helpful. When I posted this there were no other responses, so I was simply offering some reply.

[AutomatedChaos]

Of course. And if the user has only a limited set of data without comma's it will work out fine. However, converting data into a correct CSV file is ambigeous. Even Microsoft doesn't keep up with the rfc4180 format definition.

[Luke Mat]

Thank you! However, I changed the delimiter on my computer in the control panel to semicolon but when the VBScript runs it uses the comma as its delimiter which is weird because when I Save As in Excel, it separates by semicolon. This seems to be doing the same exact thing just from a command line instead of clicking and changing the settings in the control panel. But I will try this script and let you know the results.

[Luke Mat]

Yeah there will be money in the files that's why I want to separate by semicolons instead of commas. If I wasn't dealing with money I'd stick with commas and leave it at that. But thanks for trying to help, I appreciate it.

[Luke Mat]

Yeah I got the same result. It was separated by commas. I am on a school computer and maybe it won't allow me to edit registry keys? I'll try when I'm on my home computer. I'm hoping this is the reason. I'll let you know my results at home.

[Nilpo]

Luke, I figured out the problem and posted some new code in my response. This should work for you.

[Luke Mat]

This does not work either. The excel file opens but then the code tries to use the command "fatc" in cmd and it says this "this is not recognized as an internal or batch command, operable program or batch file." The usual error message for an incorrect command. And the excel window also closes. I'm not sure why this error happens. However, this solution might not work for me because it seems as if an excel window will open for every excel file I'm trying to convert, which is a problem because I'll be using this to convert around 700 excel files at once...that's a lot of excel windows.

[Nilpo]

Luke, what version of Windows and Excel are you using? It seems that it is not activating the Excel window. Instead, the keystrokes are being sent to the currently active windows which happens to be a command shell. Also, it's going to open the files sequentially so you shouldn't have 700 excel windows open at one time.

[Luke Mat]

Windows 7 Professional and Mircosoft Office Excel 2007. And I know they wouldn't open at once, but I would like this to happen without excel windows opening up at all. But when each window opens do I have to click csv and give it a name and location and all that manually?

[Nilpo]

@LukeMat It would be impossible to do this without Excel opening at all. You can't use a program that isn't running. As for naming, that is all done automatically.

[Luke Mat]

thank you but i no longer need this code. thank you very much though.

[GWD]

Best answer here ... Only thing that you have to watch out for is your Excel version True on position 12 works for Excel 2013 For Excel 2010 it is 2 arguments less so on position 10 One can also check: msdn.microsoft.com/en-us/library/office/… in that context ...