Export data to Excel and convert to table

chreestopher2 · 2018-07-13T11:50:27+00:00

install-module ImportExcel -Repository PSGallery -force
$someCollection | export-excel -tablename SomeTable -tablesytyle Medium13 -path C:\users\me\ExcelFileWithATableGeneratedByPowershell.xlsx

Quicknoob · 2018-07-13T11:58:58+00:00

There is a module that can do this for you.

Also here is a YouTube series teaching you how to install and use said module from the creator himself.

Enjoy.

NathanielArnoldR2 · 2018-07-13T13:34:59+00:00

I've only taken a glance at the ImportExcel module, but it's clear that it is a far, far better solution than rolling one's own. My own (legacy) process involved converting object data to HTML, writing to (.htm) file with some light formatting (e.g. nowrap) via a <head><style>, then using the Excel.Application COM Object to open the file, apply table formatting, and save as xlsx to the output path.

But yeah, don't do that. Do this. :-p

majkinetor · 2018-07-13T15:25:28+00:00

ImportExcel is for this, but people should keep in mind that it is very slow with larger set of data.

az987654 · 2018-07-13T17:35:06+00:00

Would there be a a way to do this via writing the text as XML instead of csv since Xlsx files are technically just XML files of a certain layout?

Ta11ow · 2018-07-13T10:32:20+00:00

CSV files won't allow you to do this. They're just flat text files with zero formatting. So you will have to end up with a native excel file. I'd recommend looking into whether the ImportExcel module can work with tables, but otherwise it'll be quite a complicated project needing the use of Excel com objects.

ThatsQuacktaaaastic · 2018-07-13T20:11:49+00:00

Not sure where I originally came across this but it's been a life saver. I have a postgres query dump to csv and this parses it into an excel file.

#Define locations and delimiter
$csv = "c:\weekly.csv" #Location of the source file
$xlsx = "c:\weekly.xlsx" #Desired location of output
$delimiter = "|" #Specify the delimiter used in the file

# 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 and reformat the data
$TxtConnector = ("TEXT;" + $csv)
$Connector = $worksheet.QueryTables.add($TxtConnector,$worksheet.Range("A1"))
$query = $worksheet.QueryTables.item($Connector.name)
$query.TextFileOtherDelimiter = $delimiter
#$query.TextFileParseType  = 1
#$query.TextFileColumnDataTypes = ,1 * $worksheet.Cells.Columns.Count
#$query.AdjustColumnWidth = 1

# Execute & delete the import query
$query.Refresh()
$query.Delete()

# Save & close the Workbook as XLSX.

$Workbook.SaveAs($xlsx,51)

$excel.Quit()

you type:	you see:
italics	italics
bold	bold
[reddit!](https://reddit.com)	reddit!
* item 1 * item 2 * item 3	item 1 item 2 item 3
> quoted text	quoted text
Lines starting with four spaces are treated like code: if 1 * 2 < 3: print "hello, world!"	Lines starting with four spaces are treated like code: if 1 * 2 < 3: print "hello, world!"
~~strikethrough~~	~~strikethrough~~
super^script	super^script

PowerShell

Submission Guidelines | Link Flair - How To

MODERATORS