使用命令行将* .xls或* .xlsx文件转换为管道分隔的.csv文件 [英] Convert *.xls or *.xlsx file to pipe separated .csv file using command line

问题描述

我有一个.xlsx文件，如下所示:

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

我想将sample.xlsx文件转换为Output.csv文件[以管道分隔].

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

请注意，我不需要任何双引号"C2,01,02".

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

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

我知道如何使用这样的手动步骤生成Output.csv:

转到控制面板->区域和语言->附加设置->使用管道"|"更新列表分隔符字段.

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

打开sample.xlsx->另存为->从下拉列表中选择另存为CSV(逗号分隔)(*.csv)类型.

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

但是我不想手动执行此操作.我想使用命令行实现相同的输出.为此，我从这篇文章中获得了参考:在命令行上将XLS转换为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

代码为:

此csv可以完美运行，但是唯一的问题是它会产生逗号分隔的csv，而不是管道分隔的

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

运行上面的代码:

XlsToCsv.vbs [sourcexlsFile] .xls [输出] .csv

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

我尝试使用其他许多值(例如1,2,3 ...)来更改csv_format = 6的值.但这不是给管道分隔的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.

请帮助.

谢谢.

推荐答案

Python解决方案.使用python 3.4和标准模块，除了openpyxl:

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

安装openpyxl:

Install openpyxl:

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

当然，xlsx文件必须只有一张纸.公式未评估，这是主要限制.

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")
   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()

用法:xlsx2csv.py file.xlsx file.csv

Usage: xlsx2csv.py file.xlsx file.csv

这篇关于使用命令行将* .xls或* .xlsx文件转换为管道分隔的.csv文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！