0

I'm trying to convert a csv file to excel using powershell. I got some help from another post and the script runs, but the output is a blank xlsx file.

Can someone help please?

Here is the powershell script

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"

Then I'm running the following command

.\csv_to_excel.ps1 -inputfile "C:\Scripts\testcsv.csv" -outputfile "C:\Scripts\data.xlsx"

Also, if anyone has experience with the Import Excel powershell module and has some kind of guide for that, I would appreciate that as well.

Thanks

2
  • $ws.Cells.Item($i, $j) -> $ws.Cells.Item($i, $j).Value Commented Dec 20, 2016 at 21:47
  • That seemed to work but it's only putting the first line of the csv file into the xlsx file and not the second line :( Commented Dec 20, 2016 at 22:07

2 Answers 2

5

To use the Excel Module by Doug Finke from a computer with PowerShell 5 or a lower version with PSGet installed:

Install-Module importexcel
Import-CSV $inputfile | Export-Excel $outputfile

Where $inputfile and $outputfile are the file locations like your script

Sign up to request clarification or add additional context in comments.

5 Comments

You Rock!!!! Woot, that worked, will need to look into the pivot table parts of the module, but is that basically the same concept?
Yeah, it's just a couple parameters -pivotrows,-pivotcolumns, and -pivotdata. Good info about it on Doug's github site
Thanks for the info. Do you have to use those parameters in conjunction with either Import or Export excel? Trying to look at github site but not seeing info on formatting.
With Export-Excel. There are a couple examples in the help get-help Export-Excel -examples
I get the exact same output format as the input. This did not have delimiting...
0

#As an example of an EASY way to format to excel merely use "Export-Excel"

'Get-Service -ComputerName localhost |Select-Object -Property Status,Name,DisplayName|Export-Excel -Path '.\output.xlsx'

#Voila!

1 Comment

Sometimes the easiest way is DIRECT

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.