CODEWITHSUNDEEP
excelpowershellcsvxlsxlsx
RichT
RichT

Reputation: 143

CSVs to multi-sheet XLS without Excel installed. Powershell

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.

Upvotes: 2

Views: 12740

Answers (3)

My Ka
My Ka

Reputation: 1

my final script

# **install-module slpslib**

$outputfilename = $(get-date -f yyyyMMdd) + "_" + "_combined-data.xlsx" #creates file name with date/username

$doc = New-SLDocument -WorkbookName $outputfilename -Path D:\Reports -PassThru -Verbose
Get-ChildItem -Path D:\Reports -Filter *.csv | 
  Import-CSVToSLDocument -WorkBookInstance $doc  -AutofitColumns -Verbose


$doc | Remove-SLWorkSheet -WorkSheetName sheet1 -Verbose

$doc | Save-SLDocument -Verbose

Upvotes: 0

Kiran Reddy
Kiran Reddy

Reputation: 2904

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

Upvotes: 4

Mike Shepard
Mike Shepard

Reputation: 18156

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

Upvotes: 3

Related Questions