Convert xlsx file to csv using batch

Question

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

Arlberg

@Brian Huh? I am not the original poster, and therefor I cannot accept an answer. I just retaged the question. @Arlberg has to accept the answer. – Arrington

Try this one: github.com/dilshod/xlsx2csv – Dispeople

The answer from @Swampape works well. Would be good if the OP could mark an answer. – Menorca

in2csv is the tool – Lungfish

Credit to @chris-rudd Here is a version that will handle multiple files drag and dropped from windows. Based on the above works by Christian Lemer plang ScottF https://stackoverflow.com/a/36804963 This uses VBS and needs Excel Installed – Interlocutor

Answer 1

Try in2csv!

Usage:

in2csv file.xlsx > file.csv

Lungfish

This might work but not for me. I strictly partition my OS from the apps. At least one large application I wrote failed after 2 years in production and required monthly maintenance to stay current with libraries. – Nubia

Answer 2

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.

Macnamara

On a mac: /Applications/LibreOffice.app/Contents/MacOS/soffice --headless --convert-to csv * – Arlenaarlene

Answer 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.

$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

Wolframite

Good answer but dead link now. Presumably $ExcelFiles should be something like Get-ChildItem -File -Filter '*.xlsx' – Burmaburman

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

Tinware

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

Giddens

Statement "Command line is not enough powerful to convert xlsx into csv." should be removed (untrue), and "You need some external tool" should be replaced by "You can use" etc. See answer by @Lungfish here for instance (but there are other solutions). – Cornelison

See #10557860 – Cornelison

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"

Entebbe

Worked for me with: .\toCsv.bat "%cd%\Book1.xlsx" Sheet1 "%cd%\csv.csv" – Askew

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.

Immanent

Great solution! FWIW, by quoting carefully, (including x1="${x%".xlsx"}";) and setting IFS to \n, you can handle spaces very effectively. But, there are even more robust ways, since filenames could have newlines, too. – Savick

Great answer. The many .xlsx I had contained 2 blank lines at the top. Modifying the command to remove the first 2 lines (needs to be before the redirect): for x in $(ls *.xlsx); do x1=${x%".xlsx"}; in2csv $x | sed 1,2d > $x1.csv; echo "$x1.csv done."; done Thank you for a great option, including the bash removal of .xlsx avoiding the need to use bash base option! – Professed

Answer 8

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.

Patmos

Answer 9

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

Vitus

Answer 10

Here is a way that is simpler than most of the other answers and shows how to do multiple files at once.

To follow up on the answer by jsta (a short script using xlsx2csv), actually it is not necessary to write a script at all. You can just put all the xlsx files in the same folder and then run xlsx2csv once.

On Windows, install Python and then type at the command-prompt:

pip install xlsx2csv
xlsx2csv C:\my\folder\name

Of course if you want a batch script then it can just consist of the second line above.

xlsx2csv has lots of options so it should work in most cases.

Gamp

Answer 11

Create a CSV for each Sheet within a xlsx file:

To improve on @Eric's answer, we can also provide support for sheets (chart-sheets/worksheets) within our xlsx workbook. This solution will open all xlsx files and save a csv file for each sheet within this workbook

---

On line 3, edit the code -Path "C:\ENTER\YOUR\PATH\HERE" to define your path, or remove this if you want to run this script from the active directory. Be warned, the script recurses through subdirectories - remove -Recurse if you don't want it to recurse through subdirectories.

---

See code:

$excelApp = New-Object -ComObject Excel.Application 
$excelApp.DisplayAlerts = $false 
$children = Get-ChildItem -Path "C:\ENTER\YOUR\PATH\HERE" -Recurse -File -Filter '*.xlsx'
$totalFile = $children.Length
$processedFiles = 0
$children | ForEach-Object {    
    $percentComplete = [math]::Round($processedFiles/$totalFile*100, 2)
    Write-Host $percentComplete% complete
    $path = $_.FullName
    Write-Host Processing  $path
    $workbook = $excelApp.Workbooks.Open($path)
    $workbook.Sheets | ForEach-Object { 
        $_.Select()    
        $name = $_.Name.Split([IO.Path]::GetInvalidFileNameChars()) -join '_'        
        $csvFilePath = $path -replace "\.xlsx$", " - $name.csv"        
        Write-Host Saving file $csvFilePath
        $workbook.SaveAs($csvFilePath, [Microsoft.Office.Interop.Excel.XlFileFormat]::xlCSV) 
    }
    $workbook.Close()
    $processedFiles++
}
Write-Host 100% complete

---

Sample output:

Processing  C:\excelFiles\file1.xlsx  
Saving file C:\excelFiles\file1 - sheet1.csv  
Saving file C:\excelFiles\file1 - sheet2.csv  
34.8% complete  
Processing  C:\excelFiles\file2.xlsx  
Saving file C:\excelFiles\file2 - sheet1.csv  
Saving file C:\excelFiles\file2 - sheet2.csv  
Saving file C:\excelFiles\file2 - sheet3.csv    
Saving file C:\excelFiles\file2 - sheet4.csv  
35.2% complete

Ctenophore