Convert Excel file to CSV from a PowerShell script
In a BI environment, we find a lot of data that is not stored in a database, and also a lot of users who have ease and preference in storing data in an Excel spreadsheet. So in this post I will show you how to convert an Excel file using a PowerShell script.
In fact this is a very easy process, we can convert all tabs within Excel, or convert only a single tab. So that way, converting to a CSV file, it can be easily added to a database through a procedure or ETL tool, instead of a native Excel file.
For the first script, where we convert all tabs of an Excel file, just enter the name of the directory, the name of the file and the file format, in the script below.
The script use the function to convert Excel files with XLSX to CSV format, however this format can be changed in the script.
# To execute the script without agreeing with the execution policy
Set-ExecutionPolicy Bypass -Scope Process# Defines the directory where the file is located
$dir = "D:\"
# Defines the name of the Excel file
$excelFileName = "YOUR_FILE_NAME"
# Define a function to convert the file
Function ExportWSToCSV ($excelFileName, $csvLoc)
{
$excelFile = $dir + $excelFileName + ".xlsx"
$E = New-Object -ComObject Excel.Application
$E.Visible = $false
$E.DisplayAlerts = $false
$wb = $E.Workbooks.Open($excelFile)
foreach ($ws in $wb.Worksheets)
{
$n = $excelFileName + "_" + $WS.Name
$ws.SaveAs($csvLoc + $n + ".csv", 6)
}
$E.Quit()
}
# For each file in the directory with the xlsx format, convert to CSV using the function above
$ens = Get-ChildItem $dir -filter *.xlsx
foreach($e in $ens)
{
ExportWSToCSV -excelFileName $e.BaseName -csvLoc $dir
}
For the second script, where we convert just one tab of an Excel file, just enter the name of the directory and the name of the file in the script below.
# To execute the script without agreeing with the execution policy
Set-ExecutionPolicy Bypass -Scope Process# Defines the directory where the file is located
$dir = "D:\"
# Defines the name of the Excel file
$excelFileName = "YOUR_FILE_NAME"
# Define a function to convert the file
Function ExportWSToCSV ($excelFileName, $csvLoc)
{
$excelFile = $dir + $excelFileName + ".xlsx"
$E = New-Object -ComObject Excel.Application
$E.Visible = $false
$E.DisplayAlerts = $false
$wb = $E.Workbooks.Open($excelFile)
foreach ($ws in $wb.Worksheets)
{
$n = $excelFileName
$ws.SaveAs($csvLoc + $n + ".csv", 6)
}
$E.Quit()
}
# For each file in the directory with the xlsx format, convert to CSV using the function above
$ens = Get-ChildItem $dir -filter *.xlsx
foreach($e in $ens)
{
ExportWSToCSV -excelFileName $e.BaseName -csvLoc $dir
}
That’s it! Now we can convert an Excel file to a CSV file easily.
The scripts for this post can be found on my Github (@guimatheus92).
