CSVs to multi-sheet XLS without Excel installed. Powershell

Question

I have a folder with multiple CSV files. I'd like to take all of them and make a XLS (or xlsx) with a sheet representing each CSV file.

The key difference here is Excel is not installed and can't be.

I understand I may be able to use the EPPlus library - http://epplus.codeplex.com/. I've also read of using the Access Database Engine - http://www.microsoft.com/en-us/download/details.aspx?id=13255 .

I've gone through a number of different scripts for converting CSV to XLS in the traditional fashion and tried converting them to use one of these but don't seem to be getting very far. Is this even possible without Excel?

I would be open to installing something like LibreOffice and learning to script with that if it's a viable option.

Solution 1

if you work with the openxml format *.xlsx you can manipulate excel documents without having to have office installed.

There are a few powershell modules(powertools,epplus etc) you can use to accomplish what you want to do. Here is a solution using a powershell wrapper that I wrote for Spreadsheetlight:

Create some csv files

Get-Service | Export-Csv -Path c:\temp\Services.csv -NoTypeInformation
Get-Process | Export-Csv -Path c:\temp\Processes.csv -NoTypeInformation

Create a new excel document to hold csv data

$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 

Save the document

$doc | Save-SLDocument -Verbose

Note: this will only work with *.xlsx format and not *.xls

EDIT-1

By default the data import starts at Row2, Column2 which can be changed easily:

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

Remove the default worksheet 'Sheet1' and Save Document

$doc | Remove-SLWorkSheet -WorkSheetName sheet1 -Verbose
$doc | Save-SLDocument -Verbose

Solution 2

You might also find https://github.com/dfinke/ImportExcel to be of use.

From the examples,

gsv | Export-Excel .\test.xlsx -WorkSheetname Services

dir -file | Export-Excel .\test.xlsx -WorkSheetname Files