How to create an .xlsx from multiple .csvs (as worksheets) without Excel on a Server using PowerShell?

Question

First time poster.

I'm trying to code in PowerShell a script on my server that will do the following:

Loop through csvs all located in a single directory Create a single Excel Workbook with each csv as a worksheet (Worksheet name as file name) Save the Workbook in a specified directory ***Microsoft Office (hence Excel) does not exist on the Server

Any help would be greatly apprecited.

I've found below but it does not accomodate multiple csvs. How to export a CSV to Excel using Powershell

Any help would be greatly apprecited.

You cannot do this if Excel doesn't exist on the server. The answer you linked to could easily be modified to work with multiple files, but it still won't work without Excel as the ComObject won't be found — arco444, Oct 12 '15 at 14:36

score 3 · Answer 1 · answered Oct 12 '15 at 14:48

3

This module https://github.com/dfinke/ImportExcel will allow you to read/write excel files (with multiple pages and formatting) without having Excel loaded.

answered Oct 12 '15 at 14:48

Mike Shepard

17,466
6
51
69

dontomp · Answer 2 · 2015-10-15T02:26:50.547

I think this script does what you want, but you'll need to download EPPlus (free .NET Library) from https://epplus.codeplex.com first. Adjust your paths accordingly.

Add-Type -Path 'C:\tmp\EPPlus 4.0.4\EPPlus.dll'
$pkg = New-Object OfficeOpenXml.ExcelPackage(New-Object System.IO.FileInfo('c:/tmp/csvdemo/book.xlsx'))
Get-ChildItem 'c:/tmp/csvdemo/*.csv' |% { [void]$pkg.Workbook.Worksheets.Add($_.Name).Cells.LoadFromText($_) }
$pkg.Save()

Some more info over here: https://dontomp.net/read-and-write-excel-workbook-with-powershell/

score 0 · Answer 3 · edited Jun 20 '20 at 09:12

Here are the relevant bits from a previous question.You will need to download the spreadsheetlight module to your server.

Create a new xlsx file

$doc = New-SLDocument -WorkbookName bigxldoc -Path C:\temp -PassThru -Verbose

Import the csv files into excel

Get-ChildItem -Path C:\temp -Filter *.csv | 
  Import-CSVToSLDocument -WorkBookInstance $doc  -AutofitColumns -Verbose

Finally Save the document

$doc | Save-SLDocument -Verbose

How to create an .xlsx from multiple .csvs (as worksheets) without Excel on a Server using PowerShell?

3 Answers3

Create a new xlsx file

Import the csv files into excel

Finally Save the document