Excel com-object via powershell

Question

I am doing data output to csv file via powershell. Generally things goes well.

I have exported the data to csv file. It contains about 10 columns. When I open it with MS Excel it's all contained in first column. I want to split it by several columns programmatically via powershell(same GUI version offers). I could make looping and stuff to split the every row and then put values to appropriate cell but then it would take way too much time.

I believe there should be an elegant solution to make one column split to multiple. Is there a way to make it in one simple step without looping?

This is what I came up with so far:

PS, The CSV file is 100% FINE. The delimiter is ','

Get-Service | Export-Csv -NoTypeInformation c:\1.csv -Encoding UTF8
$xl = New-Object -comobject Excel.Application
$xl.Visible = $true
$xl.DisplayAlerts = $False
$wb = $xl.Workbooks.Open('c:\1.csv')
$ws = $wb.Sheets|?{$_.name -eq '1'}
$ws.Activate()

$col = $ws.Cells.Item(1,1).EntireColumn

Does the file look as you would expect when you open it in a text editor like notepad? (i.e. comma seperated columns, line seperated rows). Not sure what datagridviews output looks like, I tend to use Export-Csv which Excel likes. — G42
– G42, Commented Apr 7, 2016 at 9:56
I have updated the original post. CSV file is not the reason at all. It has correct structure and all. Just when you open excel you would need to use an option to split column by specified delimiter. — Kirill Pashkov
– Kirill Pashkov, Commented Apr 7, 2016 at 10:45

G42 · Accepted Answer · 2016-04-07 14:20:28Z

2

This will get you the desired functionality; add to your code. Check out the MSDN page for more information on TextToColumns.

# Select column
$columnA   = $ws.Range("A1").EntireColumn

# Enumerations
$xlDelimited     = 1
$xlTextQualifier = 1

# Convert Text To Columns
$columnA.texttocolumns($ws.Range("A1"),$xlDelimited,$xlTextQualifier,$true,$false,$false,$true,$false)
$ws.columns.autofit()

I had to create a CSV which had "","" as delimiter to test this out. The file with "," was fine in excel.

# Opens with all fields in column A, used to test TextToColumns works
"Name,""field1"",""field2"",""field3"""
"Test,""field1"",""field.2[]"",""field3"""

# Opens fine in Excel
Name,"field1","field2","field3"
Test,"field1","field.2[]","field3"

Disclaimer: Tested with $ws = $wb.Worksheets.item(1)

answered Apr 7, 2016 at 14:20

G42

10.1k2 gold badges22 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kirill Pashkov Over a year ago

This is EXACTLY what I was looking for. Thanks a lot!

Collectives™ on Stack Overflow

Excel com-object via powershell

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related