Posted on: July 23, 2020 Posted by: ndconsultingllc Comments: 0

Excel – Multiple Tabs to CSV

The Problem

Several associates have asked me how to easily export an Excel workbook with multiple Excel tabs into a CSV file. I find myself going back to this script very frequently, particularly when I have data that I need to import into a database table.

Using PowerShell, you can easily export Excel as a CSV. This script will work on a file with a single tab or multiple tabs. For example, if the workbook has multiple tabs, the script will create a file for each tab.

Example shows the original workbook with multiple tabs that will be converted to CSV files.
Example showing the workbook with multiple tabs.

The Script

Step 1: Build The Function

The first step is to create the function that will house most of the code to convert the Excel tabs to CSV. The function will take two variables: Excel filename and directory to save the CSV files.

In our example, we will use the following values:

$excelFilename: GLRecurringTransactions.xlsx
$csvLoc: C:\Temp\CSV\

Function ExportExcel2CSV ($excelFileName, $csvLoc)
{

}

Step 2: Set Path

Next, we’ll want to start building the guts of the function. I’ll break this out into sections and describe each line. At the end of this post, you’ll see the final script in its entirety.

The first line within the function will set the variable “$excelFile” with the variable that will be passed to the function at execution. Be sure to change “C:\Temp\” to the location of your Excel file.

Function ExportExcel2CSV ($excelFileName, $csvLoc)
{
     $excelFile = "C:\Temp\" + $excelFileName + ".xlsx"
}

Step 3: Set the Excel Object

Next, we will set create the Excel object and set a couple of attributes of the Excel object. We will create a variable “$Exl” and instantiate the Excel object. However, if you do not want Excel to open, the “Visible” attribute should be set to “$false”. Likewise, if you do not want to see Excel alerts, set “DisplayAlerts” to “$false”. The final statement tells Powershell to open the Excel file declared in $excelFile with the values of the attributes set in “Visible” and “DisplayAlerts”.

Function ExportExcel2CSV ($excelFileName, $csvLoc)
{
 
    $excelFile = "C:\Temp\" + $excelFileName + ".xlsx"
   
    $Exl = New-Object -ComObject Excel.Application
    $Exl.Visible = $false
    $Exl.DisplayAlerts = $false

    $wb = $Exl.Workbooks.Open($excelFile)
}

Step 4: Create the ForEach Loop

After that, we will create a loop that will cycle through each worksheet, create a filename and export the data to a CSV file. the first line in the FOREACH loop, “$ws.Name” will assign the worksheet name to “$n”. The next line will tell PowerShell what the CSV file naming convention will be. In the example below, we are using “$csvLoc” which we passed to the function earlier. After “$csvLoc”, I’m adding a prefix to the filename. The next segment is the worksheet name, and finally the file extension.

While this is a basic script, it’s very easy to customize it. For Example, if you wanted to get creative with the new filename, you could create date and time stamps and add them to the file “wb.SaveAs” section.

Finally, the parameter (“6” in the example below) tells PowerShell the format to save the file. Click here to see all the available format codes.

Using our example above, “ws.SaveAs” will create the following files:

C:\Temp\CSV\Dat_GLRecurringTransaction1.csv
C:\Temp\CSV\Dat_GLRecurringTransaction2.csv
etc.

Function ExportExcel2CSV ($excelFileName, $csvLoc)
{
 
    $excelFile = "C:\Temp\" + $excelFileName + ".xlsx"
   
    $Exl = New-Object -ComObject Excel.Application
    $Exl.Visible = $false
    $Exl.DisplayAlerts = $false

    $wb = $Exl.Workbooks.Open($excelFile)

    foreach ($ws in $wb.Worksheets)
    {
        $n = $ws.Name
        $ws.SaveAs($csvLoc + "Dat_" + $n + ".csv", 6)

    }
 }

Step 5: Script Execution

The final section is will execute the function we created above. In this example, we set the variable “$files” to an array of the Excel files in C:\Temp. Next, the script will loop through each file in “$files” and call the ExportExcel2CSV function passing the necessary variables.

The final line of the script will kill all Excel processes. Unfortunately, PowerShell will not release the file lock. Make sure you have all other Excel files closed. In other words, this will kill the Excel process and if you have any unsaved Excel files open, they will automatically close.

Function ExportExcel2CSV ($excelFileName, $csvLoc)
{
 
    $excelFile = "C:\Temp\" + $excelFileName + ".xlsx"
   
    $Exl = New-Object -ComObject Excel.Application
    $Exl.Visible = $false
    $Exl.DisplayAlerts = $false

    $wb = $Exl.Workbooks.Open($excelFile)

    foreach ($ws in $wb.Worksheets)
    {
        $n = $ws.Name
        $ws.SaveAs($csvLoc + "Dat_" + $n + ".csv", 6)

    }
 }
 
$files = Get-ChildItem "C:\Temp\" -filter *.xlsx

foreach($Exl in $files)
{
    ExportExcel2CSV -excelFileName $Exl.BaseName -csvLoc "C:\Temp\"
    stop-process -processname EXCEL
}

As a result, by running the PowerShell script we created above you can easily convert the Excel tabs to CSV files. In addition to being easy to run, this will save you an enormous amount of time. In other words, you will not have to save each tab individually.

For more information on the services ND Consulting offers, please visit our Services page.

Excel – Multiple Tabs to CSV was last modified: September 7th, 2021 by ndconsultingllc
Categories: