none
Powershell convert csv to xlsx RRS feed

  • Question

  • 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

  • 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

  • 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
  • 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
  • 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

  • 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
  • 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
  • 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
  • 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
  • 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
  • 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
  • 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
    Moderator
  • 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
  • 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
    Moderator
  • 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
  • Thank you, this saved me a lot of time.
    Wednesday, November 2, 2016 1:36 PM
  • 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