2

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.

RichT
  • 143
  • 1
  • 4
  • 16

3 Answers3

4

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
Kiran Reddy
  • 2,836
  • 2
  • 16
  • 20
  • This works great! I just seem to be getting an extra blank sheet, "Sheet1". Also all sheets seem to have a blank column and row surrounding them. – RichT Jul 01 '15 at 15:23
  • when you create a new document there needs to be at least one worksheet so 'sheet1' is added by default which can be removed later as showin in my edited answer above. Notice the `importstartcell' parameter whose value is `A1`. that tells excel that import should begin at Row A Column 1. You can specify a any value you want. – Kiran Reddy Jul 02 '15 at 02:31
  • Perfect answer. I'm unable to find how to convert column from text to numbers. Is it possible? Thank you – culter Jul 03 '19 at 13:36
3

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
Mike Shepard
  • 17,466
  • 6
  • 51
  • 69
0

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
ChrisGPT was on strike
  • 127,765
  • 105
  • 273
  • 257
My Ka
  • 1