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:
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)
}
}
Learn Powershell | Achieve More 
Hi, nice article found it very useful, reading through your post didn’t find anything on the reverse subject, Xlsx to CSV. Could you suggest something ?
Thanks
Hi
I’m trying to just convert multiple .txt file into .xlsx files.
I just need the powershell to change the file extensions.
can you please help me doing this?
Cheers
Hi Mr Prox,
Nice script. I have been looking for something like this for a while. Trying to update it by applying an excel template, I cannot find how to incorporate it. I can get NetworkTemplatePath and TemplatesPath. But I cannot figure out how to load the template to the .csv when it is loaded into excel. ( PS V3, Excel 2016)
Thank you for any insight,
Mark
Thank you for this Great resource! I am going to give it a try.
So far I have been using Websites like zamzar or Convert.world for the conversions and they are great too. My best is Convert.world. It has such a great interface and does the job in No Time.
Boe, blundered onto your script and thought it was the answer to all my problems…except I can’t get the paste() method to work. I keep getting a “Method invocation failed because [System.__ComObject] does not contain a method named ‘Paste’ error message. Really perplexed, as I’ve made my CSV-to-worksheet logic look functionally very similar to yours . It’s got to be something I’m doing wrong – nobody else mentions that error while using your sample.
The relevant piece of my code is :
$excel = New-Object -ComObject excel.application # Create app object (for pre-2007 xls files)
$excel.visible = $True #$False
$excel.displayalerts = $False
$workbook = $excel.workBooks.add()
foreach ($item in $csvFiles)
{
$workbook.worksheets.add()
$worksheet = $workbook.worksheets.item(1)
$tempCSV = $excel.workbooks.open($item)
$tempSheet = $tempCSV.worksheets.item(1)
$workSheet.name = $tempSheet.name
$tempSheet.UsedRange.Copy() | out-null
$workSheet.Paste()
$tempCSV.close()
}
I’ve confirmed I have the correct file names (including path) loaded in my collection. Everything works, including the line to copy tab name between CSV and worksheet, until it hits the $worksheet.Paste() line.
Other examples I’ve seen specify a range to copy, but your example appears to have successfully dispensed with that. Any idea what I might be doing wrong?
Thanks,
Jerry
Pingback: Convert CSV to XLSX via PowerShell | Adam Dimech's Coding Blog
Pingback: CSV to a delimited XLSX via PowerShell | Adam Dimech's Coding Blog
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
in case any one else experiences this issue, here is the solution:
https://social.technet.microsoft.com/Forums/windowsserver/en-US/aede572b-4c1f-4729-bc9d-899fed5fad02/run-powershell-script-as-scheduled-task-that-uses-excel-com-object?forum=winserverpowershell
You have to create a folder (or two on a 64bit-windows):
(32Bit, always)
C:\Windows\System32\config\systemprofile\Dektop
(64Bit)
C:\Windows\SysWOW64\config\systemprofile\Desktop
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);
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?
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)
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
Can you post how you are running the command?
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
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’
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
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}
Pingback: Listing Database Info with Object counts | $hell Your Experience !!!
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
Interesting, I was able to convert it without issue. Did you get a message stating that it was saved to a certain location?
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?
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?
Hi Boe Prox,
Im also facing the same issue.I am using office 2003 , having excel with xlsx format is the issue??
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)
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’
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?
what could be used to do the vice versa ? Microsoft Excel Workbook Doc to a WordPress Friendly CSV File ?
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.
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
Pingback: Episode 125 – Cooking Up Some PowerShell with Lee Holmes « PowerScripting Podcast
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.
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.
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