Convert xlsx file to csv using batch
ExcelCsvBatch FileXlsxExcel Problem Overview
How do you convert multiple xlsx files to csv files with a batch script?
Excel Solutions
Solution 1 - Excel
Solution 2 - Excel
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.
Solution 3 - Excel
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
Solution 4 - Excel
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
Solution 5 - Excel
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
Solution 6 - Excel
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"
Solution 7 - Excel
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. -
in2csv creates separate csv files (doesn’t overwrite the xlsx's).
-
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.