How to export a CSV to Excel using Powershell

Question

I'm trying to export a complete CSV to Excel by using Powershell. I stuck at a point where static column names are used. But this doesn't work if my CSV has generic unknown header names.

Steps to reproduce

Open your PowerShell ISE and copy & paste the following standalone code. Run it with F5
^{"C:\Windows\system32\WindowsPowerShell\v1.0\powershell_ise.exe"}

Get-Process | Export-Csv -Path $env:temp\process.csv -NoTypeInformation

$processes = Import-Csv -Path $env:temp\process.csv 
$Excel = New-Object -ComObject excel.application 
$workbook = $Excel.workbooks.add() 

$i = 1 
foreach($process in $processes) 
{ 
 $excel.cells.item($i,1) = $process.name
 $excel.cells.item($i,2) = $process.vm
 $i++ 
} 
Remove-Item $env:temp\process.csv
$Excel.visible = $true

What it does

1. The script will export a list of all active processes as a CSV to your temp folder. This file is only for our example. It could be any CSV with any data
2. It reads in the newly created CSV and saves it under the $processes variable
3. It creates a new and empty Excel workbook where we can write data
4. It iterates through all rows (?) and writes all values from the name and vm column to Excel

My questions

• What if I don't know the column headers? (In our example name and vm). How do I address values where I don't know their header names?
• How do I count how many columns a CSV has? (after reading it with Import-Csv)

I just want to write an entire CSV to Excel with Powershell

Eczema

Answer 1

Ups, I entirely forgot this question. In the meantime I got a solution.
This Powershell script converts a CSV to XLSX in the background

Gimmicks are

• Preserves all CSV values as plain text like =B1+B2 or 0000001.
  You don't see #Name or anything like that. No autoformating is done.
• Automatically chooses the right delimiter (comma or semicolon) according to your regional setting
• Autofit columns

PowerShell Code

### Set input and output path
$inputCSV = "C:\somefolder\input.csv"
$outputXLSX = "C:\somefolder\output.xlsx"

### Create a new Excel Workbook with one empty sheet
$excel = New-Object -ComObject excel.application 
$workbook = $excel.Workbooks.Add(1)
$worksheet = $workbook.worksheets.Item(1)

### Build the QueryTables.Add command
### QueryTables does the same as when clicking "Data » From Text" in Excel
$TxtConnector = ("TEXT;" + $inputCSV)
$Connector = $worksheet.QueryTables.add($TxtConnector,$worksheet.Range("A1"))
$query = $worksheet.QueryTables.item($Connector.name)

### Set the delimiter (, or ;) according to your regional settings
$query.TextFileOtherDelimiter = $Excel.Application.International(5)

### Set the format to delimited and text for every column
### A trick to create an array of 2s is used with the preceding comma
$query.TextFileParseType  = 1
$query.TextFileColumnDataTypes = ,2 * $worksheet.Cells.Columns.Count
$query.AdjustColumnWidth = 1

### Execute & delete the import query
$query.Refresh()
$query.Delete()

### Save & close the Workbook as XLSX. Change the output extension for Excel 2003
$Workbook.SaveAs($outputXLSX,51)
$excel.Quit()

Eczema

I think the TextFileOtherDelimiter should be set to ";" or ',', When I've tried using the $Excel.Application.International(5) it didn't work as expected but forcing the delimiter will solve the issue if the conversion doesn't work properly. – Biak

Above scripts works like a charm for me, but I have one issue with it. How to deal with special characters like "öäõü"? Is there some way to use encoding somewhere? – Gild

@Gild Can you describe your issue a bit more? I just tested the script with a input csv that contains Umlauts and it worked fine – Eczema

For example, if there are following characters "äöüõ" in csv, column names or data itselfs, then after export to excel those characters are not displayd correctly, for instance "ü" in csv will be "ü" in excel. – Gild

@Gild Here are my test files. Input, Output and the script. And here you see that my excel does not have the described issue. What Windows language, Excel version and language are you using? I doubt that this is a general problem. My guess is that it is related to some setting we need to identify – Eczema

@Eczema actually it is working with your example, but for some reason, it is not working with mine. The difference is that I will take data from database into csv, at first those special characters came from database as ?, but if I encode csv into utf8 then those characters are displayed correctily in csv, but not in excel. I created separate topic as well for this issue, could you please take a look: #58235513 – Gild

I ended up using a modified version of this. The Excel process created doesn't close using this answer. (You'll end up with a ton of Excel processes.) Refer to this, and my comment, https://mcmap.net/q/212173/-cannot-close-excel-exe-after-interop-process. – Amador

Answer 2

I am using excelcnv.exe to convert csv into xlsx and that seemed to work properly. You will have to change the directory to where your excelcnv is. If 32 bit, it goes to Program Files (x86)

Start-Process -FilePath 'C:\Program Files\Microsoft Office\root\Office16\excelcnv.exe' -ArgumentList "-nme -oice ""$xlsFilePath"" ""$xlsToxlsxPath"""

Mani

Why doesn't this have any upvotes? This is definitely the easiest way to convert .csv files to .xlsx. Thanks for the simple solution! – Gallows

This seems to work, but if you have any other delimiter than the one specified for you locality this command won't convert the data to columns. I haven't been able to find if you can specify the delimiter character. – Allina

What is the switch -nme for? – Bedcover

I couldn't get this to work. excelcnv.exe always returns a 0 exit code, and provides no feedback or output. – Amador

Answer 3

Why would you bother? Load your CSV into Excel like this:

$csv = Join-Path $env:TEMP "process.csv"
$xls = Join-Path $env:TEMP "process.xlsx"

$xl = New-Object -COM "Excel.Application"
$xl.Visible = $true

$wb = $xl.Workbooks.OpenText($csv)

$wb.SaveAs($xls, 51)

You just need to make sure that the CSV export uses the delimiter defined in your regional settings. Override with -Delimiter if need be.

---

Edit: A more general solution that should preserve the values from the CSV as plain text. Code for iterating over the CSV columns taken from here.

$csv = Join-Path $env:TEMP "input.csv"
$xls = Join-Path $env:TEMP "output.xlsx"

$xl = New-Object -COM "Excel.Application"
$xl.Visible = $true

$wb = $xl.Workbooks.Add()
$ws = $wb.Sheets.Item(1)

$ws.Cells.NumberFormat = "@"

$i = 1
Import-Csv $csv | ForEach-Object {
  $j = 1
  foreach ($prop in $_.PSObject.Properties) {
    if ($i -eq 1) {
      $ws.Cells.Item($i, $j++).Value = $prop.Name
    } else {
      $ws.Cells.Item($i, $j++).Value = $prop.Value
    }
  }
  $i++
}

$wb.SaveAs($xls, 51)
$wb.Close()

$xl.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($xl)

Obviously this second approach won't perform too well, because it's processing each cell individually.

Rood

I forgot to mention that I can't use the OpenText-method. Its the same method as double clicking a CSV and open it with Excel. But this method has its flaws: You will lose leading zeros e.g. from binary values due to Excels auto detected column format. And values starting with = or - are misinterpreted as formulas. Do you know a powershell method which avoids this? – Eczema

How is that an issue in your scenario? The output of Get-Process doesn't have formula-looking values, and you can force leading zeroes by formatting a column the way you want it to look like. – Rood

As I mentioned in my question "This file is only for our example". Lets assume we have a CSV with leading zeros and equal signs and we want to avoid Excels auto format detection. All values should be treated as plain text. – Eczema

$ws.Cells.Item($i, $j++).Value = $prop.Name and $ws.Cells.Item($i, $j++).Value = $prop.Value are causing the error. I can echo the excel cell value and also the CSV value, but I can't set the excel cell value. (I'm using your exact code. No modifications. Of course I created an input CSV first) – Eczema

Worked just fine when I tested it, but I have to admit that the MS Office version on my Windows test box is quite antique. Try Item($i, $j) and put $j++ after the conditional. Also try $ws.Cells.Item($i, $j).Value2 instead of $ws.Cells.Item($i, $j).Value. – Rood

In the below line, Remove "Value" and run the script. $ws.Cells.Item($i, $j++).Value = $prop.Name Eg. $ws.Cells.Item($i, $j++) = $prop.Name – Waldrup

@AnsgarWiechers This has a performance impact. in the opposite of the solution that has been provided by nixda, question that has been marked as correct – Biak

@Biak I thought it was rather obvious that the second code sample would have performance issues. But apparently it wasn't. Caveat added. – Rood

Answer 4

This topic really helped me, so I'd like to share my improvements. All credits go to the nixda, this is based on his answer.

For those who need to convert multiple csv's in a folder, just modify the directory. Outputfilenames will be identical to input, just with another extension.

Take care of the cleanup in the end, if you like to keep the original csv's you might not want to remove these.

Can be easily modifed to save the xlsx in another directory.

$workingdir = "C:\data\*.csv"
$csv = dir -path $workingdir
foreach($inputCSV in $csv){
$outputXLSX = $inputCSV.DirectoryName + "\" + $inputCSV.Basename + ".xlsx"
### Create a new Excel Workbook with one empty sheet
$excel = New-Object -ComObject excel.application 
$excel.DisplayAlerts = $False
$workbook = $excel.Workbooks.Add(1)
$worksheet = $workbook.worksheets.Item(1)

### Build the QueryTables.Add command
### QueryTables does the same as when clicking "Data » From Text" in Excel
$TxtConnector = ("TEXT;" + $inputCSV)
$Connector = $worksheet.QueryTables.add($TxtConnector,$worksheet.Range("A1"))
$query = $worksheet.QueryTables.item($Connector.name)

### Set the delimiter (, or ;) according to your regional settings
### $Excel.Application.International(3) = ,
### $Excel.Application.International(5) = ;
$query.TextFileOtherDelimiter = $Excel.Application.International(5)

### Set the format to delimited and text for every column
### A trick to create an array of 2s is used with the preceding comma
$query.TextFileParseType  = 1
$query.TextFileColumnDataTypes = ,2 * $worksheet.Cells.Columns.Count
$query.AdjustColumnWidth = 1

### Execute & delete the import query
$query.Refresh()
$query.Delete()

### Save & close the Workbook as XLSX. Change the output extension for Excel 2003
$Workbook.SaveAs($outputXLSX,51)
$excel.Quit()
}
## To exclude an item, use the '-exclude' parameter (wildcards if needed)
remove-item -path $workingdir -exclude *Crab4dq.csv

Deegan

Hello, your code is exactly what i needed, however is there a way to add "change sheet name" to something defined in the code? thank you, I know this is an older post and I hope someone can help!! – Antineutrino

@DefcaTrick Have a look here: https://mcmap.net/q/375694/-powershell-excel-rename-worksheets-base-upon-filename At the beginning, just do this: $worksheet = $workbook.worksheets.Item(1) $worksheet.name = "specify your own custom sheetname" Sheet will now be renamed, the rest is the same. – Deegan

Thank you this worked. Some work around exporting csv's generated from Tableau. – Retractile

Answer 5

If you want to convert CSV to Excel without Excel being installed, you can use the great .NET library EPPlus (under LGPL license) to create and modify Excel Sheets and also convert CSV to Excel really fast!

Preparation

1. Download the latest stable EPPlus version
2. Extract EPPlus to your preferred location (e.g. to $HOME\Documents\WindowsPowerShell\Modules\EPPlus)
3. Right Click EPPlus.dll, select Properties and at the bottom of the General Tab click "Unblock" to allow loading of this dll. If you don't have the rights to do this, try [Reflection.Assembly]::UnsafeLoadFrom($DLLPath) | Out-Null

Detailed Powershell Commands to import CSV to Excel

# Create temporary CSV and Excel file names
$FileNameCSV = "$HOME\Downloads\test.csv"
$FileNameExcel = "$HOME\Downloads\test.xlsx"

# Create CSV File (with first line containing type information and empty last line)
Get-Process | Export-Csv -Delimiter ';' -Encoding UTF8 -Path $FileNameCSV

# Load EPPlus
$DLLPath = "$HOME\Documents\WindowsPowerShell\Modules\EPPlus\EPPlus.dll"
[Reflection.Assembly]::LoadFile($DLLPath) | Out-Null

# Set CSV Format
$Format = New-object -TypeName OfficeOpenXml.ExcelTextFormat
$Format.Delimiter = ";"
# use Text Qualifier if your CSV entries are quoted, e.g. "Cell1","Cell2"
$Format.TextQualifier = '"'
$Format.Encoding = [System.Text.Encoding]::UTF8
$Format.SkipLinesBeginning = '1'
$Format.SkipLinesEnd = '1'

# Set Preferred Table Style
$TableStyle = [OfficeOpenXml.Table.TableStyles]::Medium1

# Create Excel File
$ExcelPackage = New-Object OfficeOpenXml.ExcelPackage 
$Worksheet = $ExcelPackage.Workbook.Worksheets.Add("FromCSV")

# Load CSV File with first row as heads using a table style
$null=$Worksheet.Cells.LoadFromText((Get-Item $FileNameCSV),$Format,$TableStyle,$true) 

# Load CSV File without table style
#$null=$Worksheet.Cells.LoadFromText($file,$format) 

# Fit Column Size to Size of Content
$Worksheet.Cells[$Worksheet.Dimension.Address].AutoFitColumns()

# Save Excel File
$ExcelPackage.SaveAs($FileNameExcel) 

Write-Host "CSV File $FileNameCSV converted to Excel file $FileNameExcel"

Contorted

Running your example I get an error: Multiple ambiguous overloads found for "SaveAs" and the argument count: "1". Seems pretty promising, just thought I'd add this in case someone trys running and gets the same error. – Phytophagous

Answer 6

This is a slight variation that worked better for me.

$csv = Join-Path $env:TEMP "input.csv"
$xls = Join-Path $env:TEMP "output.xlsx"

$xl = new-object -comobject excel.application
$xl.visible = $false
$Workbook = $xl.workbooks.open($CSV)
$Worksheets = $Workbooks.worksheets

$Workbook.SaveAs($XLS,1)
$Workbook.Saved = $True

$xl.Quit()

Greenburg

For which fileformat stands your 1 after $XLS? I cannot find it at MSDN's overview. Normally I would use 51 instead since this stands for xlWorkbookDefault. Also $Worksheets = $Workbooks.worksheets seems irrelevant – Eczema

Using a 1 works for the .xls file extension. Using a 51 for the .xls extension I get an error to the effect of "The file you are tryign to open is in a different format tha specified by the file extension" when opening the file in Excel. Using 51 works for .xlsx extension. – Greenburg

Answer 7

I had some problem getting the other examples to work.

EPPlus and other libraries produces OpenDocument Xml format, which is not the same as you get when you save from Excel as xlsx.

macks example with open CSV and just re-saving didn't work, I never managed to get the ',' delimiter to be used correctly.

Ansgar Wiechers example has some slight error which I found the answer for in the commencts.

Anyway, this is a complete working example. Save this in a File CsvToExcel.ps1

param (
[Parameter(Mandatory=$true)][string]$inputfile,
[Parameter(Mandatory=$true)][string]$outputfile
)

$excel = New-Object -ComObject Excel.Application
$excel.Visible = $false

$wb = $excel.Workbooks.Add()
$ws = $wb.Sheets.Item(1)

$ws.Cells.NumberFormat = "@"

write-output "Opening $inputfile"

$i = 1
Import-Csv $inputfile | Foreach-Object { 
    $j = 1
    foreach ($prop in $_.PSObject.Properties)
    {
        if ($i -eq 1) {
            $ws.Cells.Item($i, $j) = $prop.Name
        } else {
            $ws.Cells.Item($i, $j) = $prop.Value
        }
        $j++
    }
    $i++
}

$wb.SaveAs($outputfile,51)
$wb.Close()
$excel.Quit()
write-output "Success"

Execute with:

.\CsvToExcel.ps1 -inputfile "C:\Temp\X\data.csv" -outputfile "C:\Temp\X\data.xlsx"

Gamaliel

Answer 8

I found this while passing and looking for answers on how to compile a set of csvs into a single excel doc with the worksheets (tabs) named after the csv files. It is a nice function. Sadly, I cannot run them on my network :( so i do not know how well it works.

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.V    isible = $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)
    }
}

Oppugn

I tried to clean up the formatting a bit, but it's still very messy. Please consider reformatting your code. – Beattie