Convert xlsx file to csv using batch

Question

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

Answer 1

Try in2csv!

Usage:

in2csv file.xlsx > file.csv

Answer 2

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 

Get-ChildItem -File -Filter '*.xlsx' | 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

Answer 3

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.

Answer 4

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

Answer 5

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

Answer 6

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"

Answer 7

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:

1. ${x%”.xlsx”} is bash string manipulation which clips .xlsx from the end of the string.
2. in2csv creates separate csv files (doesn’t overwrite the xlsx's).
3. 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.

Answer 8

gocsv works on Mac, Linux and Windows and has no dependencies (no Microsoft Office, Libre Office or Python). Just run the xlsx command like so:

gocsv xlsx file.xlsx

Answer 9

This PowerShell solution worked for me. Other options (like csvkit) blew up due to encoding issues, but this worked:

• Run PowerShell as Administrator
• Install-Module ImportExcel
• Run Import-Module ImportExcel to see if you are able to run it.
  • If you get an error like, "running scripts is disabled on this system," you need to run something like this: Set-ExecutionPolicy -ExecutionPolicy Unrestricted
• cd to the directory containing all the .xlsx files
• Run this to convert every xlsx file in the current directory into a CSV: Get-ChildItem -Filter *.xlsx | ForEach-Object { Import-Excel $_.FullName | Export-Csv ($_.FullName -replace '\.xlsx$','.csv') -NoTypeInformation }

When done, you may want to put your execution permissions back. For me, that meant running Set-ExecutionPolicy -ExecutionPolicy Restricted.