Converting my array into a csv file

This topic contains 2 replies, has 3 voices, and was last updated by Profile photo of Richard Siddaway Richard Siddaway 7 months, 2 weeks ago.

Viewing 3 posts - 1 through 3 (of 3 total)
  • Author
    Posts
  • #35220
    Profile photo of Stewart Meyer
    Stewart Meyer
    Participant

    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!

    #35221
    Profile photo of Bob McCoy
    Bob McCoy
    Participant
    # 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
    
    #35225

    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

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic.