Converting CSV file or files into an Excel workbook

Update 07SEPT2010: Updated script based on an excellent comment to use the Paste() method for converting CSV files into an excel workbook. Thanks Luc!

While going through the Technet Powershell Forums and Scripting Guys Forums, I came across a question by an individual who was looking for the best way to convert a .csv file into an excel file. As it stood, the individual only need to convert one csv file and that made for a fairly easy solution.

$xl = new-object -comobject excel.application
$xl.visible = $true
$Workbook = $xl.workbooks.open(“$loglocation\errors_$server.csv”)
$Worksheets = $Workbooks.worksheets
$Workbook.SaveAs(“$loglocation\errors_$server.xls”,1)
$Workbook.Saved = $True
$xl.Quit()

Simple enough. Just open up the CSV file using a created excel com object in Powershell and then save that file as an excel spreadsheet.

This got me to thinking about what if I wanted to save multiple csv files into an excel workbook? How would that be done? And of course, can this all be automated to auto-fit the columns and provide a name to each worksheet that was relevant to each csv file that was being converted?

The short answer, is…Yes! This can be done with Powershell and accomplish all of the requirements.

Below is the script that I wrote that takes multiple csv files and adds them all into excel.  It also auto-fits each column in excel so when you open it up, you already have access to all of the information without having to manually re-size each column. Plus, for each csv that you submit, a separate worksheet is created with the name of the csv file submitted, minus the extension.  Depending on the size of the csv, the time it takes for the conversion will vary.

As shown in the picture below, I have two csv files that are being converted into one excel workbook and saved to whatever filename I want using the .xlsx format.

When I open up the excel file, I now see both of my CSV’s there to view.

Simple enough!

You can find this script below or at this link on the Technet Gallery:

ConvertCSV-ToExcel.ps1

Script Below

Function Release-Ref ($ref)
{
([System.Runtime.InteropServices.Marshal]::ReleaseComObject(
[System.__ComObject]$ref) -gt 0)
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()
}

Function ConvertCSV-ToExcel
{
<#
.SYNOPSIS
Converts one or more CSV files into an excel file.

.DESCRIPTION
Converts one or more CSV files into an excel file. Each CSV file is imported into its own worksheet with the name of the
file being the name of the worksheet.

.PARAMETER inputfile
Name of the CSV file being converted

.PARAMETER output
Name of the converted excel file

.EXAMPLE
Get-ChildItem *.csv | ConvertCSV-ToExcel -output ‘report.xlsx’

.EXAMPLE
ConvertCSV-ToExcel -inputfile ‘file.csv’ -output ‘report.xlsx’

.EXAMPLE
ConvertCSV-ToExcel -inputfile @(“test1.csv”,”test2.csv”) -output ‘report.xlsx’

.NOTES
Author: Boe Prox
Date Created: 01SEPT210
Last Modified:

#>

#Requires -version 2.0
[CmdletBinding(
SupportsShouldProcess = $True,
ConfirmImpact = ‘low’,
DefaultParameterSetName = ‘file’
)]
Param (
[Parameter(
ValueFromPipeline=$True,
Position=0,
Mandatory=$True,
HelpMessage=”Name of CSV/s to import”)]
[ValidateNotNullOrEmpty()]
[array]$inputfile,
[Parameter(
ValueFromPipeline=$False,
Position=1,
Mandatory=$True,
HelpMessage=”Name of excel file output”)]
[ValidateNotNullOrEmpty()]
[string]$output
)

Begin {
#Configure regular expression to match full path of each file
[regex]$regex = “^\w\:\\”

#Find the number of CSVs being imported
$count = ($inputfile.count -1)

#Create Excel Com Object
$excel = new-object -com excel.application

#Disable alerts
$excel.DisplayAlerts = $False

#Show Excel application
$excel.Visible = $False

#Add workbook
$workbook = $excel.workbooks.Add()

#Remove other worksheets
$workbook.worksheets.Item(2).delete()
#After the first worksheet is removed,the next one takes its place
$workbook.worksheets.Item(2).delete()

#Define initial worksheet number
$i = 1
}

Process {
ForEach ($input in $inputfile) {
#If more than one file, create another worksheet for each file
If ($i -gt 1) {
$workbook.worksheets.Add() | Out-Null
}
#Use the first worksheet in the workbook (also the newest created worksheet is always 1)
$worksheet = $workbook.worksheets.Item(1)
#Add name of CSV as worksheet name
$worksheet.name = “$((GCI $input).basename)”

#Open the CSV file in Excel, must be converted into complete path if no already done
If ($regex.ismatch($input)) {
$tempcsv = $excel.Workbooks.Open($input)
}
ElseIf ($regex.ismatch(“$($input.fullname)”)) {
$tempcsv = $excel.Workbooks.Open(“$($input.fullname)”)
}
Else {
$tempcsv = $excel.Workbooks.Open(“$($pwd)\$input”)
}
$tempsheet = $tempcsv.Worksheets.Item(1)
#Copy contents of the CSV file
$tempSheet.UsedRange.Copy() | Out-Null
#Paste contents of CSV into existing workbook
$worksheet.Paste()

#Close temp workbook
$tempcsv.close()

#Select all used cells
$range = $worksheet.UsedRange

#Autofit the columns
$range.EntireColumn.Autofit() | out-null
$i++
}
}

End {
#Save spreadsheet
$workbook.saveas(“$pwd\$output”)

Write-Host -Fore Green “File saved to $pwd\$output”

#Close Excel
$excel.quit()

#Release processes for Excel
$a = Release-Ref($range)
}
}

About Boe Prox

Microsoft Cloud and Datacenter MVP working as a SQL DBA.
This entry was posted in powershell, scripts and tagged , , . Bookmark the permalink.

28 Responses to Converting CSV file or files into an Excel workbook

  1. Pingback: CSV to a delimited XLSX via PowerShell | Adam Dimech's Coding Blog

  2. Dan says:

    stumbled upon this script…this is great! however, I have a problem….I’m calling your script from an existing powershel script that I have in place. My script queries a sql database and uses export-csv to dump some data to several csv files. Then I call your script to combine and generate a nice spreadsheet . Works fine when running interactively from command line; does not work when running from task scheduler….I get the error below…I have verified that the path referenced in the error exist and that permissions are not an issue…..any suggestions?

    Exception calling “Open” with “1” argument(s): “Microsoft Excel cannot access
    the file ‘C:\scripts\monthly_report\001.csv’. There
    are several possible reasons:
    The file name or path does not exist.
    The file is being used by another program.
    The workbook you are trying to save has the same name as a currently open
    workbook.”
    At C:\scripts\monthly_report\ConvertCSV-ToExcel.ps1:105 char:13
    + $tempcsv = $excel.Workbooks.Open($input)
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : ComMethodTargetInvocation

  3. Asmer Khan says:

    Thanks for your help. I was trying to convert a csv to xlsx in C# and the code below worked perfect for in.

    using Excel = Microsoft.Office.Interop.Excel;

    private void CreateExcelDocument(string filename)
    {
    string csvFileName = “”;
    string excelFileName = “”;

    Excel.Application _application;
    Excel.Workbook _workbook;
    Excel.Worksheet _worksheet;

    csvFileName = filename + “.csv”;
    excelFileName = filename + “.xlsx”;

    object misValue = System.Reflection.Missing.Value;

    _application = new Excel.Application();
    _application.DisplayAlerts = false;

    _workbook = _application.Workbooks.Open(csvFileName, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue);

    _worksheet = (Excel.Worksheet)_workbook.ActiveSheet;

    _workbook.SaveAs(excelFileName, Excel.XlFileFormat.xlOpenXMLWorkbook, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);

  4. Chris says:

    hello,
    Many thanks for a very nice function, just what I wanted!
    One thing, on my computer (and for my colleagues) we have comma (,) as separator in our csv-files. It works when we open these files in Excel.

    However when I run your function for my csv-files and open “report.xslx” I have all the values in the first coulmn. (Function works without problem and write with green at the end that the file has been saved)

    If I replace all commas with semicolon in the csv-files and run your finction it looks very good!

    Is there an easy change in the code to avoid this problem?

  5. julie andreacola says:

    This was just what I needed. I used it with office 2010 and PS 3.0. I did have to make a change when saving the workbook. I changed $workbook.saveas(“$pwd\$output”) to $workbook.saveas($output)

  6. Michael says:

    Hi all,
    I start the script and nothing. No error, just nothing….

    Windows Server 2008 R2, PowerShell v. 2.0

    Thanks for the help
    Michael

    • Boe Prox says:

      Can you post how you are running the command?

      • Michael says:

        Hi Boe,

        Thanks for the quick reply.
        I have several csv files that I want to convert

        I run the script with these parameters:
        \ ConvertCSV-ToExcel-inputfile @ (“test1.csv”, “test2.csv”) output ‘Report.xlsx’

        I also tested the following:
        . \ ConvertCSV ToExcel-input-file ‘file.csv’-output ‘Report.xlsx’

        Thanks for the help

        Greetings from South Tyrol / Italy
        Michael

        • Boe Prox says:

          Did you dot source the file before running the function?
          . .\ConvertCSV-ToExcel
          Then run the command
          ConvertCSV-ToExcel -inputfile @(“test1.csv”,”test2.csv”) -output ‘Report.xlsx’

    • Michael says:

      I have tested the following:

      . \ ConvertCSV-ToExcel-inputfile @ (“file_1.csv”, “file_2.csv”) output ‘Report.xlsx’
      . \ ConvertCSV-ToExcel-inputfile @ (“. \ File_1.csv”, “. \ File_2.csv”) output ‘Report.xlsx’
      . \ ConvertCSV-ToExcel-inputfile @ (“c: \ scripts \ file_1.csv”, “c: \ scripts \ file_1.csv file_2.csv”) output ‘Report.xlsx’
      c: \ scripts \ ConvertCSV-ToExcel-inputfile @ (“c: \ scripts \ file_1.csv”, “c: \ scripts \ file_1.csv file_2.csv”) output ‘Report.xlsx’

      end: ConvertCSV-ToExcel —> Without parameters

      Nothing. No error, no output …
      I use Office 2010 Prof.

      Greetings
      Michael

  7. Gerold manders says:

    Per processed file It is better to close and kill Excel instances to prevent files being “locked” by Excel. Locked files can generate all kinds of weird messages.

    Add to the script something like:
    ###— Close Excel —
    $xl.quit()

    ###— Kill Excel instance —
    if (ps excel) { kill -name excel}

  8. Pingback: Listing Database Info with Object counts | $hell Your Experience !!!

  9. Ben says:

    Are there any pre-requisites to running this script? if I run the example command:

    Get-ChildItem *.csv | ConvertCSV-ToExcel -output ‘report.xlsx’

    I get no error prompt, however there is no xlsx created, I am sure I am doing something silly, but this script sounds just too perfect to pass up

    • Boe Prox says:

      Interesting, I was able to convert it without issue. Did you get a message stating that it was saved to a certain location?

      • Ben says:

        I get no messages at all. I am not 100% sure I am doing this correctly.

        Do I need to create the xlsx file manually first? I am running the command:

        Get-ChildItem *.csv | .\ConvertCSV-ToExcel -output ‘D:\Reports\application\application.xlsx’

        (this is once I have manually navigated to D:\reports\application)

        It immediately just comes up waiting for another command with no messages to speak of.

        Currently using Powershell 3.0, is there anything I need to modify in the .ps1 file?

        • Boe Prox says:

          One thing that is standing out to me is using .\ConvertCSV-ToExcel instead of ConvertCSV-ToExcel but that may just be a typo in the comments. I don’t have PoshV3 installed on my laptop at this moment so I am unable test right now to see if has something to do with a different version. I can look at loading up V3 later this weekend and do some testing as well to see if I can produce the same issue. What version of Office do you have installed as well?

      • Sandeep says:

        Hi Boe Prox,
        Im also facing the same issue.I am using office 2003 , having excel with xlsx format is the issue??

    • György Helmeczy says:

      Add this line:

      ConvertCSV-ToExcel

      to the very end of ConvertCSV-ToExcel.ps1
      (next problem to me, it doesn’t use the input/output file name – but they can be hard-coded – it’s enough to me)

      • Boe Prox says:

        You shouldn’t have to add that line at the end. This is an advanced function, so you would need to dot source the script to load the function into memory.
        Ex.
        #Load function into memory
        . .\ConvertCSV-ToExcel.ps1

        #Run function
        ConvertCSV-ToExcel -InputFile .\Test.csv -Output ‘test.xlsx’

  10. Ty says:

    I need to make this script work in a batch file. The input files and output files will be the same every time. I would like to hardcode the files into the script. Instead of worrying about passing paramaters into it through the batch file.

    Is this possible?

  11. Martin says:

    what could be used to do the vice versa ? Microsoft Excel Workbook Doc to a WordPress Friendly CSV File ?

    • Boe Prox says:

      Try something like this:

      $xlCSV = 6
      $Excel = New-Object -Com Excel.Application
      $Excel.visible = $False
      $Excel.displayalerts=$False
      $WorkBook = $Excel.Workbooks.Open(“C:\users\boe\desktop\process.xlsx”)
      $Workbook.SaveAs(“C:\users\boe\desktop\process.csv”,$xlCSV)
      $Excel.quit()

      Keep in mind that you must supply the full path to the file for it to work. Excel is a little weird about just supplying the document only.

      • forthron says:

        Ho Boe Prox,

        Your script works fine for me when run manually at the powershell prompt but when I run through a scheduled task it fails, reporting
        “There are several possible reasons:
        • The file name or path does not exist.
        • The file is being used by another program.
        • The workbook you are trying to save has the same name as a currently open wor
        kbook.”

        Without giving you more info, have you come across this problem??

        Kind regards,
        Needles

  12. Pingback: Episode 125 – Cooking Up Some PowerShell with Lee Holmes « PowerScripting Podcast

  13. LucD says:

    Nice script.
    I did something similar in my http://www.lucd.info/2010/05/29/beyond-export-csv-export-xls/ post.
    From my tests the Paste() function came out a lot faster than ‘replacing’ the cells one-by-one.
    And you can do lots of other nice things with XLS files like adding a graph.

    • boeprox says:

      Thanks! I will definitely look at incorporating the Paste() function into my script. Always appreciate another set of eyes and new ideas on my scripts.

      • Kumar says:

        Hi All,

        My requirement :

        1) I have attached 2 sample sheets [ book1.csv & bokk2.xls ] .I want to copy the data from book1.csv to book2.xls with single click .while copying it should copy the whole data [ 4 columns ] to book2’s first 4 colums .and should save it as the same file name [ as book1.xls ]

        Like this i have plenty of .csv files to be converted into predefined .xls sheet .

        So could you please help me to do this using a macro

        with single click it should copy all the .csv files which are present in a folder to another predefined .xls sheet and should save the files as same name with .xls xtension

        Hope you guys can help me on this ..

        Thanks in advance .. this will realy helps me alot …! reduces my work

        Thanks
        kumar

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s