Jatin Jatin - 4 months ago 45
Vb.net Question

Convert *.xls or *.xlsx file to pipe separated .csv file using command line

I have an .xlsx file like this:


Heading C1 C2,01,02 C3 C4
R1 1 4 7 10
R2 2 5 8 11,1
R3 3 6 9,0 12

I want to convert sample.xlsx file into Output.csv file [pipe separated].

Please note that I don't want any double quotes "C2,01,02".



I know how to produce Output.csv using manual steps like this:

Goto control panel -> Region and Language -> Additional Settings -> update list separator field with pipe "|".

Open sample.xlsx -> save as -> from the drop down select save as type CSV(Comma delimited)(*.csv).

But I don't want to do this manually. I want to achieve the same Output using command line. For this, I have taken reference from this post:
Convert XLS to CSV on command line

Code is:

This csv works perfectly but the only problem is that it produces comma separated csv instead of pipe separated.

if WScript.Arguments.Count < 2 Then
WScript.Echo "Please specify the source and the destination files. Usage: ExcelToCsv <xls/xlsx source file> <csv destination file>"
End If
csv_format = 6
Set objFSO = CreateObject("Scripting.FileSystemObject")
src_file = objFSO.GetAbsolutePathName(Wscript.Arguments.Item(0))
dest_file = objFSO.GetAbsolutePathName(WScript.Arguments.Item(1))
Dim oExcel
Set oExcel = CreateObject("Excel.Application")
Dim oBook
Set oBook = oExcel.Workbooks.Open(src_file)
oBook.SaveAs dest_file, csv_format
oBook.Close False

To run the above code:

XlsToCsv.vbs [sourcexlsFile].xls [Output].csv

I tried changing value of csv_format = 6 with many other values like 1,2,3...and so on. but it's not giving pipe separated csv.

Please help.

Thanks in advance.


Python solution. Uses python 3.4 and standard modules except for openpyxl:

Install openpyxl:

cd /D C:\python34
scripts\pip install openpyxl

Of course xlsx file must have only 1 sheet. Formulas are not evalulated, that's the main limitation.

import openpyxl,csv,sys
if len(sys.argv)<3:
   print("Usage xlsx2csv.py file.xlsx file.csv")

i = sys.argv[1]
o = sys.argv[2]

f = open(o,"w",newline='')
cw = csv.writer(f,delimiter='|',quotechar='"')

wb = openpyxl.load_workbook(i)
sheet = wb.active
for r in sheet.rows:
    row = [c.value for c in r]

Usage: xlsx2csv.py file.xlsx file.csv