Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Extract and convert all Excel worksheets into CSV files using PowerShell


By:   |   Last Updated: 2014-05-12   |   Comments (21)   |   Related Tips: More > PowerShell

Problem

A few colleagues have asked me if PowerShell provides an easy way to export Excel as a CSV. Whether we have multiple Excel files, or just multiple worksheets in Excel, PowerShell simplifies the process. In addition to that, once the format is in CSV, we have multiple options to insert the data into a SQL Server database.

Solution

In this simple example, we'll use an Excel workbook that has four different worksheets - Derivatives, SP, Futures, Contents. We want each worksheet as it's own CSV file and we want the CSV file name to take into account its Excel source as well as the workbook where it originates.

We'll use an Excel workbook that has four different worksheets

The following will fire off Excel (don't run just yet), but keep it invisible in the background:

    $excelFile = "C:\ExcelFiles\OurFile.xlsx"
    $E = New-Object -ComObject Excel.Application
    $E.Visible = $false
    $E.DisplayAlerts = $false

Since we want to be able to change the file location, we'll change $excelFile = "C:\ExcelFiles\OurFile.xlsx" to $excelFile = "C:\ExcelFiles\" + $excelFileName + ".xlsx".

Next, let's pass in the workbook and loop through each worksheet in the workbook:

    $wb = $E.Workbooks.Open($excelFile)
    foreach ($ws in $wb.Worksheets)
    {
        $n = $excelFileName + "_" + $ws.Name
    }

Here, we've opened the workbook of our Excel file, and looped through each worksheet initiating a new variable called $n, which holds the Excel file name with an underscore and the worksheet name. Our naming convention for our CSV files is now built.

Now, we want to save the Excel file in the correct format and exit Excel. While we're at it, let's wrap our code in a function that we can call where we'll enter the Excel file name and the location we want to drop the CSVs into:

Function ExportWSToCSV ($excelFileName, $csvLoc)
{
    $excelFile = "C:\ExcelFiles\" + $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()
}
ExportWSToCSV -excelFileName "file" -csvLoc "C:\CSVFiles\"
ExportWSToCSV -excelFileName "file" -csvLoc "C:\CSVFiles\"

Just a note here, while in Windows 7 everything terminates correctly, sometimes Excel will linger in Windows 8. We can add the below line after $E.Quit().

stop-process -processname EXCEL

Extract All Worksheets from All Files

Finally, if we want export multiple Excel files, we'll get all the Excel files in a specific location by filtering on the Excel extension and storing the results in a variable ($ens below this). From there, we'll loop through each Excel file and call our function - in the below example, we exported 4 worksheets from file> and 2 worksheets from file1:

$ens = Get-ChildItem "C:\ExcelFiles\" -filter *.xlsx
foreach($e in $ens)
{
    ExportWSToCSV -excelFileName $e.BaseName -csvLoc "C:\CSVFiles\"
}
Now that our Excel worksheets are CSVs, we have quite a few different tools that we can import the data

Now that our Excel worksheets are CSVs, we have quite a few different tools that we can import the data. As we can see, PowerShell makes CSV creation from Excel simple and quick.

Next Steps
  • Edit the function in a way that matches your needs.
  • Test the script on Excel files.


Last Updated: 2014-05-12


get scripts

next tip button



About the author
MSSQLTips author Tim Smith Tim Smith works as a DBA and developer and also teaches Automating ETL on Udemy.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Tuesday, February 06, 2018 - 12:59:38 PM - Mario Back To Top

I love this code, where would I set the delimeter in this code. I need "|". Please assist

Regards

 


Wednesday, January 31, 2018 - 6:56:25 PM - Mario Back To Top

 I love this code, where would I set the delimeter in this code. I need "|"

Regards

 


Friday, October 28, 2016 - 8:36:31 AM - Robin Back To Top

Hello,

 

Thank you for this explenation, It really helped a lot. But I'm running into a problem. Could you or someone else help me if you have the same Problem?

It can't find my excel file. But it's not an xlsx file. its only xls.. does it make a differents?

The Problem: It can't find the file but it's there. The name was written correctly in the error message.. I've checked the file is there... Could it be that PS does not have the premission to Modify the file?

 

here is my code: (better said your code)

Function ExportWSToCSV ($FN)
{
    $excelFile =< $checkallserver
    $E = New-Object -ComObject Excel.Application
    $E.Visible = $false
    $E.DisplayAlerts = $false
    start-sleep -seconds 2
    $wb = $E.Workbooks.Open($excelfile) ## Error is here it cant find the file.
    foreach ($ws in $wb.Worksheets)
    {
        $n = $FN + "_" + $ws.Name
        $ws.SaveAs($checkallserver + $n + ".csv", 6)
        Write-Host "Er hets gmacht wooohooO" ##just a notifier for me, that i see it worked in the script
    }
    $E.Quit()
}

 

Error Message:

Exception calling "Open" with "1" argument(s): "Wir konnten
'Server_Alle_20161004.xls' nicht finden. Wurde das Objekt vielleicht verschoben,
umbenannt oder gelöscht?"
At D:\CFS\ILO-Skript\ILO-Skript.ps1:91 char:5
+     $wb = $E.Workbooks.Open($excelfile) ## Error is here it cant find the file.
+     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : ComMethodTargetInvocation

 

 

Thanks you for the help.

 

Greetings Robin

 

 

 

 


Wednesday, October 21, 2015 - 2:45:37 PM - Bernadine Back To Top

This worked like a charm!

Thank you so much!!

 

The only issue I had was I needed to use the 'stop-process -processname EXCEL' even though I'm on Windows 7; not sure why.

My other issue is my spreadsheet contained " " encasing the distinguishedName column, which doubled up "" "".

 

This is awesome; I'll certainly be able to use it in the future.

-Bernadine


Thursday, February 26, 2015 - 1:04:47 PM - Duncan Fairweather Back To Top

What about exporting to a tab-delimited format?


Wednesday, February 18, 2015 - 12:17:38 AM - maxflipz Back To Top

Be careful using the STOP-PROCESS -processname Excel because on a shared workstation or server, that command will kill all instances of Excel including those opened by other users.

I use this now, pay attention to the WHILE statements.  Change the variables to match those in your script.

While ([System.Runtime.Interopservices.Marshal]::ReleaseComObject($xlsRng)) {'cleanup xlsRng'}
While ([System.Runtime.Interopservices.Marshal]::ReleaseComObject($xlsSh)) {'cleanup xlsSh'}
While ([System.Runtime.Interopservices.Marshal]::ReleaseComObject($xlsWb)) {'cleanup xlsWb'}
While ([System.Runtime.Interopservices.Marshal]::ReleaseComObject($xlsObj)) {'cleanup xlsObj'}
[gc]::collect() | Out-Null
[gc]::WaitForPendingFinalizers() | Out-Null


Tuesday, December 23, 2014 - 1:55:16 PM - Dan Back To Top

Nice article...thanks for sharing.

You wouldn't happen to know how to do the opposite?....take the results of several sql queries and insert each result set into a separate worksheet in a single excel file?


Friday, December 12, 2014 - 8:27:00 AM - Tim Back To Top

@Dylan - The machine must have Excel where a developer will convert an XLS to CSV, as the above script starts Excel then saves the worksheets as a CSV file.


Thursday, December 11, 2014 - 2:12:44 AM - Dylan Thomas Back To Top

Thanks for this Tim, Is there anyway to this if you do not have excel installed on your machine


Wednesday, November 26, 2014 - 3:54:13 PM - Mark S Back To Top

 

Awesome, awesome. Garbage in, shiny object out.


Tuesday, August 12, 2014 - 12:10:29 PM - Dan holmes Back To Top

Looks like the same technique as described here:

http://dnhlmssql.blogspot.com/2013/05/from-excel-to-insert-into-another.html


Friday, August 08, 2014 - 3:57:01 PM - gene Back To Top

Function excel2csv([string]$ExcelFile,[string]$csvFile,[string]$SheetName,[int]$EndRow,[string]$EndCol)
{
#################################
# convert Excel to CSV
#################################
#write-host -foregroundcolor "yellow" "Excel file " $ExcelFile
#write-host -foregroundcolor "yellow" "CSV file " $csvFile
#write-host -foregroundcolor "yellow" "Sheet " $SheetName
#write-host -foregroundcolor "yellow" "Row " $EndRow
#write-host -foregroundcolor "yellow" "Column " $EndCol
if (!(Test-Path $ExcelFile))
{write-host -foregroundcolor "red" "Excel file does not exist"; exit 1}

$excelObject = New-Object -ComObject Excel.Application
$excelObject.Visible = $false
$excelObject.DisplayAlerts = $false
$wb= $excelObject.Workbooks.Open($ExcelFile)
$list = $wb.sheets | where {$_.name -eq "$SheetName"}
$csv = $wb.sheets.add()
for ($i=2; $i -lt $EndRow+1 ; $i++) {
    If ($list.Cells.Item($i,1).text -eq "") {
        $from = $list.Range("a1:$EndCol$($i-1)")
        $copy = $from.copy()
        $to = $csv.range("a1")
        $csv.paste($to)
        $i=$EndRow+1
    }
}
$xlCSVType = 6
$csv.SaveAs("$csvFile",$xlCSVType)
$wb.close()
$excelObject.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($wb) > $Null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excelObject) > $Null
}


Tuesday, June 03, 2014 - 2:18:51 AM - RS Back To Top

Hi Tim : - Thank you for the followup. How do i replace the commas with lets say a forward slash in the cell.

--Can you email me the solution. Thank you .- RS

 

## Simple example of Excel cell loop
Function ExportWSToCSV ($excelFileName, $csvLoc)
{
    $excelFile = "C:\ExcelFiles\" + $excelFileName + ".xlsx"
    $E = New-Object -ComObject Excel.Application
    $E.Visible = $false
    $E.DisplayAlerts = $false
    $wb = $E.Workbooks.Open($excelFile)
    foreach ($ws in $wb.Worksheets)
    {
$maxrow = $first.UsedRange.Cells | Select-Object -Last 1 | Select-Object -Property Row
$maxrow = $maxrow.Row
$maxcol = $first.UsedRange.Cells | Select-Object -Last 1 | Select-Object -Property Column
$maxcol = $maxcol.Column
 
 
for ($c = 1; $c -le $maxrow = $maxrow.Row; $c++)
{
for ($r = 1; $r -le $maxrow; $r++)
{
if ($ws.Cells.Item($r,$c).Value() -like "*,*") ## Or other character, like " ' |
{
## Replace or change [WHAT DO I WRITE HERE ?]
}
}
}
 
$n = $excelFileName + "_" + $ws.Name
        $ws.SaveAs($csvLoc + $n + ".csv", 6)
    }
    $E.Quit()
}
ExportWSToCSV -excelFileName "file" -csvLoc "C:\CSVFiles\"
 

Monday, June 02, 2014 - 1:12:08 PM - Tim Back To Top

One of the approaches is like this GitHub example, which should be used inside of the above function (within the foreach ($ws in $wb.Worksheets) and before the line $n = $excelFileName + "_" + $ws.Name).  It will loop through string columns where commas exist and can have a numeric approach added to it, if that's easier than simply updating the formatting (strings remain unchanged).


Monday, June 02, 2014 - 12:15:18 AM - RS Back To Top

It depends on the developers; some may prefer to go in and replace commas in Excel directly, using a tool like Interlop.  I would use StreamReader and StreamWriter to look at the CSV file once it's exported.  Note that Excel will wrap the data with commas in quotations.  What that means is that I'll end up with a ratio of delimiters to quotations; at that point, I will determine the ratio and based on the number, replace all commas for values between quotations.  This would be similar to my OutputInvalidandValidData method on my C# ReadFiles class here https://github.com/tmmtsmith/SQLServer/blob/master/ETL/CSharpWriteBadAndGoodLinesByDelimiter.cs - the difference being that on the else, it would then count the ratio of quotations to delimeters.

 

Hi Tim - i did not understand this. I do have commas in the data and is creating problems. Can you please let me know how to handle this, in the most simplest manner. Thank you - RS


Thursday, May 22, 2014 - 9:52:31 AM - Tim Back To Top

@Brain and RS

It depends on the developers; some may prefer to go in and replace commas in Excel directly, using a tool like Interlop.  I would use StreamReader and StreamWriter to look at the CSV file once it's exported.  Note that Excel will wrap the data with commas in quotations.  What that means is that I'll end up with a ratio of delimiters to quotations; at that point, I will determine the ratio and based on the number, replace all commas for values between quotations.  This would be similar to my OutputInvalidandValidData method on my C# ReadFiles class here https://github.com/tmmtsmith/SQLServer/blob/master/ETL/CSharpWriteBadAndGoodLinesByDelimiter.cs - the difference being that on the else, it would then count the ratio of quotations to delimeters.

Keep in mind that with Excel you can find numerous exceptions - for instance, what happens if an extra quotation mark is thrown in?  Or, I've seen some people build an Excel sheet with six or seven tables in one sheet spread across the sheet (not organized).  A developer must weight the cost whether handling every exception is (a) worth it to his/her company/client, or (b) worth it with his/her time (if this isn't required for a company/client).

@Paul

Good to hear that.  Generally, if I'm paying for the data, I refuse Excel data from data providers.  Since I also provide data to clients, I know that I have to provide the data they want since they're paying for it.  Granted, some employees must do this for their company and there are some great tools that automate all of this which a company can pay for without it hitting the employee, such as ASPOSE, which also works with several languages.


Thursday, May 22, 2014 - 12:17:10 AM - RS Back To Top

Hi Tim :

How do you handle it when the data itself contains commas?

Please let us know. Thanks !!


Wednesday, May 21, 2014 - 11:23:41 AM - Paul Wilcox Back To Top

Good post, and the preferable route if you have Excel installed.  But consider a situation where you don't have Excell installed and don't care to or don't want to install it.  In my case I am using oledb.  I did have to download a provider (Microsoft.ace.oledb.12.0), but it was free.  Also, I wondered if downloading libreoffice would allow it to work similarly to the kind of code in the main article, but since I already had the oledb provider, I went that route.  The additional advantage of oledb is that you can also use it to establish a linked server in SQL Server and read from text files just as you would any SQL server table.  But it's certainly a lot more work than the coding above.  So, as I said, if you've got Excel, this article is the better route.  Thanks Tim.


Wednesday, May 21, 2014 - 10:23:57 AM - Brain Back To Top

The article explains how to convert excel file to csv file.

But did not emphasize concepts behind them.

1.Using com enabled applications or objects with powershell.

2.Excel com api.

Did I miss anything?

 

Thanks

Brain.

 


Wednesday, May 21, 2014 - 6:53:10 AM - mouse Back To Top

How do you handle it when the data itself contains commas?


Friday, May 16, 2014 - 11:59:53 AM - RS Back To Top

Awesome awesome post. so lucid. It saved my day to convert excel files to csvs. 

Also the following is another way to exit Excel. I think only the quitting of Excel was giving me trouble in your code and i included the following. Please keep these posts coming. So helpful.

 

http://powershell.com/cs/blogs/tips/archive/2011/11/15/closing-excel-gracefully.aspx

[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) Start-Sleep1'Excel processes: {0}'-f@(Get-Processexcel-ea0).Count


Learn more about SQL Server tools