Author Posts

February 12, 2016 at 9:29 am

I have an array that begins like this:

20160123,ARC/INFO,Carol.Fielding,NSL503
20160124,ARC/INFO,Adam.Leyshon,NSL503
20160124,ARC/INFO,Adrian.Jones,NSL503
20160124,GeoStats,Andrew.Burridge,NSL501
20160124,GeoStats,Dave.Tavner,NSL504
20160124,GeoStats,Glyn.Wensley,NSL501

Each item in the array is a string that consists of four parts (a date, a product, a user and a server) that are separated by commas.

In my naivety I thought that I could simply export this to a csv file and the commas would be recognized as delimiters by Excel and chopped into four columns.

Well, that didn't work. It created a single column with quote marks enclosing the whole thing.

I experimented with the split function, tried to create a new object with four properties in it, tried using an array of arrays, tried using hash tables, all to no avail. I've got myself confused now. I'm new to PowerShell and it's messing with me....

Does anybody have any idea how I might go about converting the above into a csv file that looks correct in Excel?

Thanks!

February 12, 2016 at 9:39 am

# populate your array however, the here-string is just for demo
$ar = @"
20160123,ARC/INFO,Carol.Fielding,NSL503
20160124,ARC/INFO,Adam.Leyshon,NSL503
20160124,ARC/INFO,Adrian.Jones,NSL503
20160124,GeoStats,Andrew.Burridge,NSL501
20160124,GeoStats,Dave.Tavner,NSL504
20160124,GeoStats,Glyn.Wensley,NSL501
"@ -split "`r`n"

$ar | ConvertFrom-Csv -Header "Date","Product","User","Server" |
    Export-Csv -Path .\foo.csv -NoTypeInformation -Encoding ASCII

February 12, 2016 at 10:33 am

As an alternative if you're using PowerShell 5

Get-Content -Path C:\Test2\t1.txt |
ConvertFrom-String -PropertyNames 'Date','Product','User','Server' -Delimiter ',' |
Export-Csv -Path C:\Test2\t1.csv -NoTypeInformation

I put your example data into a file. if you already have it in an array use that to start the pipeline