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

Question

I have an .xlsx file like this:

sample.xlsx:

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".

Output.csv:

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 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>"
    Wscript.Quit
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
oExcel.Quit

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.

You are aware that CSV is the extension associated with Comma Separated Values? It's difficult to have a pipe-delimited comma separated values file. — Ken White
– Ken White, Commented Sep 4, 2016 at 6:46
Seems to be difficult to do unless you hack: experts-exchange.com/questions/23712758/…. A python solution to post-process the file would take 3 or 4 lines, though. — Jean-François Fabre
– Jean-François Fabre ♦, Commented Sep 4, 2016 at 6:48
@Ken White: Okay, so can I get a corresponding Output.txt file? — Jatin
– Jatin, Commented Sep 4, 2016 at 6:49
@Jean-François Fabre: Can you please post your hack solution? I am okay with python script also. All I want is that the task of converting an xlsx to pipe separated csv should be automated. — Jatin
– Jatin, Commented Sep 4, 2016 at 6:55

Jean-François Fabre · Accepted Answer · 2016-09-06 09:05:46Z

4

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.

And the empty lines are filtered out too.

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

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]
    if row:
        cw.writerow(row)
f.close()

Usage: xlsx2csv.py file.xlsx file.csv

edited Sep 6, 2016 at 9:05

answered Sep 4, 2016 at 7:27

Jean-François Fabre♦

141k24 gold badges179 silver badges246 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Dirk Reichel · Accepted Answer · 2016-09-04 07:38:23Z

1

If you are running your script anyways, better extend it like this:

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>"
    Wscript.Quit
End If
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, 3
oBook.Close False
oExcel.Quit
Set objFile = objFSO.OpenTextFile(dest_file, 1)
strText = objFile.ReadAll
objFile.Close
strNewText = Replace(strText, " ", "|")
Set objFile = objFSO.OpenTextFile(dest_file, 2)
objFile.WriteLine strNewText
objFile.Close

edited Sep 4, 2016 at 7:38

answered Sep 4, 2016 at 7:34

Dirk Reichel

7,9991 gold badge18 silver badges31 bronze badges

6 Comments

Jean-François Fabre Over a year ago

the problem with this kind of solution is if there are actual comas in the cells. Otherwise it's OK.

Dirk Reichel Over a year ago

@Jean-FrançoisFabre changed it to tab-separated... this will avoid that problem ;)

Jatin Over a year ago

@Dirk Reichel: your script behaving differently. First, I want it pipe separated [your script is TAB seperated]. Second, I dont want any double quotes [Your script is giving double quotes].

Dirk Reichel Over a year ago

I'll check that again... worked perfect with my test files... just a sec. (Just make sure that Replace(strText, " ", "|") the " " has a tabulator in it... not a space")

Jatin Over a year ago

@Dirk Reichel: I am able to see PIPE after replacing space with tab in the first argument in Replace(strText, "<TAB>", "|"). Now, only 1 more problem remaining.i.e., double quotes.

|

Collectives™ on Stack Overflow

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

sample.xlsx:

Output.csv:

2 Answers 2

Comments

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

sample.xlsx:

Output.csv:

2 Answers 2

Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related