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  amp  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   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 It reads in the newly created CSV and saves it under the  processes variable It creates a new and empty Excel workbook where we can write data 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

User · Answer

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

User · Answer

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

User · Answer

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    quot C  somefolder input csv quot   outputXLSX    quot C  somefolder output xlsx quot       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  quot Data    From Text quot  in Excel  TxtConnector     quot TEXT  quot     inputCSV   Connector    worksheet QueryTables add  TxtConnector  worksheet Range  quot A1 quot     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  amp  delete the import query  query Refresh    query Delete        Save  amp  close the Workbook as XLSX  Change the output extension for Excel 2003  Workbook SaveAs  outputXLSX 51   excel Quit

User · Answer

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   Download the latest stable EPPlus version Extract EPPlus to your preferred location  e g  to  HOME Documents WindowsPowerShell Modules EPPlus  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

User · Answer

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

User · Answer

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            lt        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             gt            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

User · Answer

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

User · Answer

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  amp  delete the import query  query Refresh    query Delete        Save  amp  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

[excel] How to export a CSV to Excel using Powershell

Examples related to excel

Examples related to powershell

Examples related to windows-7

Examples related to export-to-excel

Examples related to office-2013