使用批处理将xlsx文件转换为csv

时间:2021-10-20 05:26:34

How do you convert multiple xlsx files to csv files with a batch script?

如何使用批处理脚本将多个xlsx文件转换为csv文件?

7 个解决方案

#1


17  

Try in2csv!

in2csv试试!

Usage:

用法:

in2csv file.xlsx > file.csv

#2


6  

To follow up on the answer by user183038, here is a shell script to batch rename all xlsx files to csv while preserving the file names. The xlsx2csv tool needs to be installed prior to running.

要跟踪user183038的答案,这里有一个shell脚本,可以批量将所有xlsx文件重命名为csv,同时保留文件名。需要在运行之前安装xlsx2csv工具。

for i in *.xlsx;
 do
  filename=$(basename "$i" .xlsx);
  outext=".csv" 
  xlsx2csv $i $filename$outext
done

#3


5  

You need an external tool, in example: SoftInterface.com - Convert XLSX to CSV.

您需要一个外部工具,例如:SoftInterface.com—将XLSX转换为CSV。

After installing it, you can use following command in your batch:

安装后,您可以在您的批次中使用以下命令:

"c:\Program Files\Softinterface, Inc\Convert XLS\ConvertXLS.EXE" /S"C:\MyExcelFile.xlsx" /F51 /N"Sheet1" /T"C:\MyExcelFile.CSV" /C6 /M1 /V

“c:\ Program Files \ Softinterface公司\转换XLS \ ConvertXLS。EXE”/ S“C:\ MyExcelFile。xlsx / F51 / N”Sheet1 / T“C:\ MyExcelFile。CSV”/ C6 / M1 / V

#4


3  

Get all file item and filter them by suffix and then use PowerShell Excel VBA object to save the excel files to csv files.

获取所有文件项并通过后缀过滤它们,然后使用PowerShell Excel VBA对象将Excel文件保存到csv文件中。

$excelApp = New-Object -ComObject Excel.Application 
$excelApp.DisplayAlerts = $false 

$ExcelFiles | ForEach-Object { 
    $workbook = $excelApp.Workbooks.Open($_.FullName) 
    $csvFilePath = $_.FullName -replace "\.xlsx$", ".csv" 
    $workbook.SaveAs($csvFilePath, [Microsoft.Office.Interop.Excel.XlFileFormat]::xlCSV) 
    $workbook.Close() 
} 

You can find the complete sample here How to convert Excel xlsx file to csv file in batch by PowerShell

您可以在这里找到完整的示例,如何通过PowerShell将Excel xlsx文件批量转换为csv文件

#5


1  

Needs installed excel as it uses the Excel.Application com object.Save this as .bat file:

需要安装excel,因为它使用excel。应用com对象。保存为。bat文件:

@if (@X)==(@Y) @end /* JScript comment
    @echo off


    cscript //E:JScript //nologo "%~f0" %*

    exit /b %errorlevel%

@if (@X)==(@Y) @end JScript comment */


var ARGS = WScript.Arguments;

var xlCSV = 6;

var objExcel = WScript.CreateObject("Excel.Application");
var objWorkbook = objExcel.Workbooks.Open(ARGS.Item(0));
objExcel.DisplayAlerts = false;
objExcel.Visible = false;

var objWorksheet = objWorkbook.Worksheets(ARGS.Item(1))
objWorksheet.SaveAs( ARGS.Item(2), xlCSV);

objExcel.Quit();

It accepts three arguments - the absolute path to the xlsx file, the sheet name and the absolute path to the target csv file:

它接受三个参数——xlsx文件的绝对路径、表名和目标csv文件的绝对路径:

call toCsv.bat "%cd%\Book1.xlsx" Sheet1 "%cd%\csv.csv"

#6


0  

Credit to @chris-rudd

信贷@chris-rudd

Here is a version that will handle multiple files drag and dropped from windows. Based on the above works by

这是一个可以处理从windows拖拽多个文件的版本。基于以上工作

Christian Lemer
plang
ScottF

https://*.com/a/36804963

https://*.com/a/36804963

This uses VBS and needs Excel Installed

这使用VBS,需要安装Excel

#7


0  

Adding to @marbel's answer (which is a great suggestion!), here's the script that worked for me on Mac OS X El Captain's Terminal, for batch conversion (since that's what the OP asked). I thought it would be trivial to do a for loop but it wasn't! (had to change the extension by string manipulation and it looks like Mac's bash is a bit different also)

除了@marbel的答案(这是一个很好的建议!)之外,下面是我在Mac OS X El Captain的终端上使用的脚本,用于批量转换(因为OP要求这样做)。我认为做一个for循环是很简单的,但是它不是!(必须通过字符串操作来更改扩展名,看起来Mac的bash也有点不同)

for x in $(ls *.xlsx); do x1=${x%".xlsx"}; in2csv $x > $x1.csv; echo "$x1.csv done."; done

Note:

注意:

  1. ${x%”.xlsx”} is bash string manipulation which clips .xlsx from the end of the string.
  2. $ { x %”。xlsx "}是bash字符串操作,它从字符串的末尾剪辑.xlsx。
  3. in2csv creates separate csv files (doesn’t overwrite the xlsx's).
  4. in2csv创建单独的csv文件(不覆盖xlsx)。
  5. The above won't work if the filenames have white spaces in them. Good to convert white spaces to underscores or something, before running the script.
  6. 如果文件名中有空格,上面的操作将不起作用。在运行脚本之前,最好将空格转换为下划线或其他内容。

#1


17  

Try in2csv!

in2csv试试!

Usage:

用法:

in2csv file.xlsx > file.csv

#2


6  

To follow up on the answer by user183038, here is a shell script to batch rename all xlsx files to csv while preserving the file names. The xlsx2csv tool needs to be installed prior to running.

要跟踪user183038的答案,这里有一个shell脚本,可以批量将所有xlsx文件重命名为csv,同时保留文件名。需要在运行之前安装xlsx2csv工具。

for i in *.xlsx;
 do
  filename=$(basename "$i" .xlsx);
  outext=".csv" 
  xlsx2csv $i $filename$outext
done

#3


5  

You need an external tool, in example: SoftInterface.com - Convert XLSX to CSV.

您需要一个外部工具,例如:SoftInterface.com—将XLSX转换为CSV。

After installing it, you can use following command in your batch:

安装后,您可以在您的批次中使用以下命令:

"c:\Program Files\Softinterface, Inc\Convert XLS\ConvertXLS.EXE" /S"C:\MyExcelFile.xlsx" /F51 /N"Sheet1" /T"C:\MyExcelFile.CSV" /C6 /M1 /V

“c:\ Program Files \ Softinterface公司\转换XLS \ ConvertXLS。EXE”/ S“C:\ MyExcelFile。xlsx / F51 / N”Sheet1 / T“C:\ MyExcelFile。CSV”/ C6 / M1 / V

#4


3  

Get all file item and filter them by suffix and then use PowerShell Excel VBA object to save the excel files to csv files.

获取所有文件项并通过后缀过滤它们,然后使用PowerShell Excel VBA对象将Excel文件保存到csv文件中。

$excelApp = New-Object -ComObject Excel.Application 
$excelApp.DisplayAlerts = $false 

$ExcelFiles | ForEach-Object { 
    $workbook = $excelApp.Workbooks.Open($_.FullName) 
    $csvFilePath = $_.FullName -replace "\.xlsx$", ".csv" 
    $workbook.SaveAs($csvFilePath, [Microsoft.Office.Interop.Excel.XlFileFormat]::xlCSV) 
    $workbook.Close() 
} 

You can find the complete sample here How to convert Excel xlsx file to csv file in batch by PowerShell

您可以在这里找到完整的示例,如何通过PowerShell将Excel xlsx文件批量转换为csv文件

#5


1  

Needs installed excel as it uses the Excel.Application com object.Save this as .bat file:

需要安装excel,因为它使用excel。应用com对象。保存为。bat文件:

@if (@X)==(@Y) @end /* JScript comment
    @echo off


    cscript //E:JScript //nologo "%~f0" %*

    exit /b %errorlevel%

@if (@X)==(@Y) @end JScript comment */


var ARGS = WScript.Arguments;

var xlCSV = 6;

var objExcel = WScript.CreateObject("Excel.Application");
var objWorkbook = objExcel.Workbooks.Open(ARGS.Item(0));
objExcel.DisplayAlerts = false;
objExcel.Visible = false;

var objWorksheet = objWorkbook.Worksheets(ARGS.Item(1))
objWorksheet.SaveAs( ARGS.Item(2), xlCSV);

objExcel.Quit();

It accepts three arguments - the absolute path to the xlsx file, the sheet name and the absolute path to the target csv file:

它接受三个参数——xlsx文件的绝对路径、表名和目标csv文件的绝对路径:

call toCsv.bat "%cd%\Book1.xlsx" Sheet1 "%cd%\csv.csv"

#6


0  

Credit to @chris-rudd

信贷@chris-rudd

Here is a version that will handle multiple files drag and dropped from windows. Based on the above works by

这是一个可以处理从windows拖拽多个文件的版本。基于以上工作

Christian Lemer
plang
ScottF

https://*.com/a/36804963

https://*.com/a/36804963

This uses VBS and needs Excel Installed

这使用VBS,需要安装Excel

#7


0  

Adding to @marbel's answer (which is a great suggestion!), here's the script that worked for me on Mac OS X El Captain's Terminal, for batch conversion (since that's what the OP asked). I thought it would be trivial to do a for loop but it wasn't! (had to change the extension by string manipulation and it looks like Mac's bash is a bit different also)

除了@marbel的答案(这是一个很好的建议!)之外,下面是我在Mac OS X El Captain的终端上使用的脚本,用于批量转换(因为OP要求这样做)。我认为做一个for循环是很简单的,但是它不是!(必须通过字符串操作来更改扩展名,看起来Mac的bash也有点不同)

for x in $(ls *.xlsx); do x1=${x%".xlsx"}; in2csv $x > $x1.csv; echo "$x1.csv done."; done

Note:

注意:

  1. ${x%”.xlsx”} is bash string manipulation which clips .xlsx from the end of the string.
  2. $ { x %”。xlsx "}是bash字符串操作,它从字符串的末尾剪辑.xlsx。
  3. in2csv creates separate csv files (doesn’t overwrite the xlsx's).
  4. in2csv创建单独的csv文件(不覆盖xlsx)。
  5. The above won't work if the filenames have white spaces in them. Good to convert white spaces to underscores or something, before running the script.
  6. 如果文件名中有空格,上面的操作将不起作用。在运行脚本之前,最好将空格转换为下划线或其他内容。