How do you convert multiple xlsx files to csv files with a batch script?
This question is related to
excel
csv
batch-file
xlsx
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)
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. Alternative way of converting to csv. Use libreoffice
:
libreoffice --headless --convert-to csv *
Please be aware that this will only convert the first worksheet of your Excel file.
Get all file item and filter them by suffix and then use PowerShell Excel VBA object to save the excel files to csv files.
$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
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.
for i in *.xlsx;
do
filename=$(basename "$i" .xlsx);
outext=".csv"
xlsx2csv $i $filename$outext
done
You need an external tool, in example: SoftInterface.com - Convert XLSX to 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
Needs installed excel as it uses the Excel.Application
com object.Save this as .bat
file:
@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:
call toCsv.bat "%cd%\Book1.xlsx" Sheet1 "%cd%\csv.csv"
Source: Stackoverflow.com