Remove From My Forums

Powershell convert csv to xlsx

Question
0

Sign in to vote

Hi there,

I've seen on alot of websites the ability to convert from xls to csv using Powershell but how would you go about converting from a csv to a xlsx file?

Hope someone can help.

Dave

Monday, August 30, 2010 9:05 PM

Answers

5

Sign in to vote
Hi Mervyn,

I ended up using the code below:

$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()

Thanks for asking.

Dave
- Marked as answer by Mervyn Zhang Monday, September 6, 2010 1:40 AM
Friday, September 3, 2010 3:35 PM

Threw this together and tested good on a few CSV's I had.

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 a CSV file to an Excel file
   
 .DESCRIPTION 
  Converts a CSV file to an Excel file
    
 .PARAMETER inputfile
  Name of the CSV file being converted
 
 .PARAMETER output
  Name of the converted excel file
    
 .EXAMPLE 
 
 .NOTES
 Author: Boe Prox									   
 Date Created: 								   
 Last Modified: 
   
#>
   
#Requires -version 2.0 
[CmdletBinding()] 
Param
  (  
    [parameter(Mandatory=$False,Position=1)][string]$inputfile,  
    [parameter(Mandatory=$False,Position=1)][string]$output
            
  )
    
#Create Excel Com Object
$excel = new-object -com excel.application

#Show Excel application
$excel.Visible = $True

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

#Use the first worksheet in the workbook
$worksheet1 = $workbook.worksheets.Item(1)

#Remove other worksheets that are not needed
$workbook.worksheets.Item(2).delete()
$workbook.worksheets.Item(2).delete()

#Start row and column
$r = 1
$c = 1

#Begin working through the CSV
$file = (GC $inputfile)
ForEach ($f in $file) {
  $arr = ($f).split(',')
  ForEach ($a in $arr) {
    $worksheet1.Cells.Item($r,$c) = "$(($a).replace('"',''))"
    $c++
    }
  $c = 1
  $r++    
  }    

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

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

#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)
$a = Release-Ref($worksheet1)
$a = Release-Ref($workbook)
$a = Release-Ref($range)
}

Dot source the script and use like this:

ConvertCSV-ToExcel -inputfile "test.csv" -output "report.xlsx"

Marked as answer by Mervyn Zhang Tuesday, August 31, 2010 5:57 AM

Tuesday, August 31, 2010 2:42 AM

All replies

0

Sign in to vote

One way to this is to make use of the free SQL Server Reporting Services ReportViewer class:

http://sev17.com/2009/04/send-powershell-output-to-an-excel-pdf-or-image-file/

Monday, August 30, 2010 11:10 PM

Threw this together and tested good on a few CSV's I had.

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 a CSV file to an Excel file
   
 .DESCRIPTION 
  Converts a CSV file to an Excel file
    
 .PARAMETER inputfile
  Name of the CSV file being converted
 
 .PARAMETER output
  Name of the converted excel file
    
 .EXAMPLE 
 
 .NOTES
 Author: Boe Prox									   
 Date Created: 								   
 Last Modified: 
   
#>
   
#Requires -version 2.0 
[CmdletBinding()] 
Param
  (  
    [parameter(Mandatory=$False,Position=1)][string]$inputfile,  
    [parameter(Mandatory=$False,Position=1)][string]$output
            
  )
    
#Create Excel Com Object
$excel = new-object -com excel.application

#Show Excel application
$excel.Visible = $True

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

#Use the first worksheet in the workbook
$worksheet1 = $workbook.worksheets.Item(1)

#Remove other worksheets that are not needed
$workbook.worksheets.Item(2).delete()
$workbook.worksheets.Item(2).delete()

#Start row and column
$r = 1
$c = 1

#Begin working through the CSV
$file = (GC $inputfile)
ForEach ($f in $file) {
  $arr = ($f).split(',')
  ForEach ($a in $arr) {
    $worksheet1.Cells.Item($r,$c) = "$(($a).replace('"',''))"
    $c++
    }
  $c = 1
  $r++    
  }    

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

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

#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)
$a = Release-Ref($worksheet1)
$a = Release-Ref($workbook)
$a = Release-Ref($range)
}

Dot source the script and use like this:

ConvertCSV-ToExcel -inputfile "test.csv" -output "report.xlsx"

Marked as answer by Mervyn Zhang Tuesday, August 31, 2010 5:57 AM

Tuesday, August 31, 2010 2:42 AM

0

Sign in to vote

Hi,

Do you need any other assistance? If there is anything we can do for you, please let us know.

Thanks.
This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

Friday, September 3, 2010 1:34 AM
5

Sign in to vote
Hi Mervyn,

I ended up using the code below:

$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()

Thanks for asking.

Dave
- Marked as answer by Mervyn Zhang Monday, September 6, 2010 1:40 AM
Friday, September 3, 2010 3:35 PM
0

Sign in to vote

Thank you for update and glad to hear you have resolved the problem. If you have more questions in the future, you’re welcomed to this forum.

Thanks
This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

Monday, September 6, 2010 1:41 AM
0

Sign in to vote

I'm aware this post is a little old but is there a way to use this code in a powershell script that runs as a scheduled task? When I run my powershell script manually it completes without issue, however, when it runs from the task scheduler it seems to die when it gets to the chunk of code above.

Any one have any ideas?

Wednesday, July 13, 2011 4:47 PM
0

Sign in to vote

You are kidding right?

I run this, (after allowing for unsigned scripts) and while it runs through without error, it also runs through without creating the excel file.

Wednesday, July 20, 2011 4:26 PM
0

Sign in to vote

Just as a comment, in-case anyone gets caught like I did, the function here called Release-Ref sometimes works and sometimes doesn't work. When it doesn't work, it is called and excel.exe just won't exit, so I've slightly changed the function and it works perfectly every time for me now:

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

The only difference is that the ReleaseComObject command is continually invoked until it releases the object, rather than just invoke it a single time and then diving into the Collect and WaitForPendingFinalizers.

Hope it can help someone else...

Thursday, October 13, 2011 5:55 AM
0

Sign in to vote
Hi Timmyy,

I have the same problem. Runs fine when run manually from the powershell prompt but fails as a scheduled task. Error is,

"xception calling "Open" with "1" argument(s): "Microsoft Excel cannot access t
he file 'C:\test.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 wor
kbook."

Tried the David Thomas suggestion but no joy. Anyone got any ideas on this???
- Edited by forthron Tuesday, May 8, 2012 4:44 AM
Tuesday, May 8, 2012 4:42 AM
0

Sign in to vote

Do you know if this will work in Office 365 Document Libraries?

Use case is that we have a Realty company that uploads a lot of CSV files and cant open them in the Excel Web App. If we can have a script that runs when a CSV file gets uploaded to the library that converts it to Excel that may work.

Any help here would be GREAT!

Thanks!

Saturday, October 31, 2015 4:52 PM
0

Sign in to vote

You would have to acquire or write a custom upload handler for SharePoint. It cannot be done with PowerShell or Excel.

Here is how to create a handler for O365. Post further questions in the SharePoint/Azure developer forums.

You should also look in the O365 SharePoint add-in store to see if someone has already built this tool.

\_(ツ)_/

Saturday, October 31, 2015 5:01 PM
0

Sign in to vote

jrv - did you supply how to create the O365 handler via hyperlink? It did not come through.

thanks,

Monday, November 2, 2015 7:00 PM
0

Sign in to vote
I suggest asking in the SharePoint developers forum and in the O365 Office developers forum. There is an API that can be run in O365 tools.

https://store.office.com/appshome.aspx?productgroup=SharePoint

http://dev.office.com/

\_(ツ)_/
- Proposed as answer by MikeyP1024 Tuesday, July 11, 2017 8:49 PM
- Unproposed as answer by MikeyP1024 Tuesday, July 11, 2017 8:49 PM
Monday, November 2, 2015 7:14 PM
0

Sign in to vote

download the ps script from gallery..

https://gallery.technet.microsoft.com/scriptcenter/7c56c444-2476-4625-b1d9-821f30280e44/?tduid=(2552a09e4f7fe336b3c1b5f048898b76)(256380)(2459594)(TnL5HPStwNw-Hpg0X8dAYs1T5Zww4yznIA)()

Wednesday, August 17, 2016 3:36 PM
0

Sign in to vote

Thank you, this saved me a lot of time.

Wednesday, November 2, 2016 1:36 PM
0

Sign in to vote

The short answer is does not work in OS365 as Dave used in above. Creates just a CSV spreadsheet with .xls extension. My variation:

$xl = new-object -comobject excel.application
$xl.visible = $true
$Workbook = $xl.workbooks.open($jwtTokenCSVfilePath)
$Worksheets = $Workbooks.worksheets
$Workbook.SaveAs($jwtPath + "ExcelJWTtokens.xls",1)
$Workbook.Saved = $True
$xl.Quit()

CSV file has a CSV header first line two columns. remaining 2 column lines. Note is actually .xls file which OS365 opens in compatiblity mode.

Tuesday, July 11, 2017 8:56 PM

Products

Resources

Solutions

Updates

Trials

Related Sites

Training

Certifications

Other resources

Support options

More support

Not an IT pro?

Answered by:

Powershell convert csv to xlsx

Question

Answers

All replies