How do you convert multiple xlsx files to csv files with a batch script?
如何使用批处理脚本将多个xlsx文件转换为csv文件?
7 个解决方案
#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
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:
注意:
-
${x%”.xlsx”}
is bash string manipulation which clips.xlsx
from the end of the string. - $ { x %”。xlsx "}是bash字符串操作,它从字符串的末尾剪辑.xlsx。
- in2csv creates separate csv files (doesn’t overwrite the xlsx's).
- in2csv创建单独的csv文件(不覆盖xlsx)。
- 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.
- 如果文件名中有空格,上面的操作将不起作用。在运行脚本之前,最好将空格转换为下划线或其他内容。
#1
#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
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:
注意:
-
${x%”.xlsx”}
is bash string manipulation which clips.xlsx
from the end of the string. - $ { x %”。xlsx "}是bash字符串操作,它从字符串的末尾剪辑.xlsx。
- in2csv creates separate csv files (doesn’t overwrite the xlsx's).
- in2csv创建单独的csv文件(不覆盖xlsx)。
- 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.
- 如果文件名中有空格,上面的操作将不起作用。在运行脚本之前,最好将空格转换为下划线或其他内容。