Search code examples
powershellcsvpowershell-5.0

Add header row to .csv


I realize this is a very commonly asked question, however none of the solutions that have worked in the questions I have found have worked for me. I have a powershell script that creates a .csv file (using new-item) and then populates it with four columns of information (using add-content). I would like to add to this script to add a header to the .csv so I can further parse it and separate the information by the contents of one of the columns, however nothing I have tried is working. I have tried using the -value parameter to make the first row of the csv the headers I want, which is working to put the header in the correct columns, however the first row of information that is being added through add-content is going into the header row. Example:

Desired output:
Header | Header | Header | Header
Info   | Info   | Info   | Info
Info   | Info   | Info   | Info
Actual Output:
Header | Header | Header | Header | Info   | Info   | Info   | Info   |
Info   | Info   | Info   | Info   |

I have also tried using import-csv and adding a header that way, then piping the output to export-csv, however this has been returning a blank .csv each time. Code I have used for that:

Import-Csv $file -header "Header,Header,Header,Header" |export-csv $file -notypeinformation

Way I see it I need to skip the first row when doing add-content, however I'm quite new to powershell and don't know quite how to do that. I've been going in circles on this for a while now, so any input would be much appreciated.

** EDIT **

Answer by TheMadTechnician fixed the issue, pasting currently working code per second half of their answer. It is a relatively simple script to parse a csv, check if there is a tracking number, if there is check against ups website, if delivered deposit in csv, if delivery exception deposit in other csv:

$file= import-csv "**FILEPATH**\Intransit_report.csv"
$newfile= New-Item "**FILEPATH**\$(get-date -format dd-MM-yyyy)_delivered.csv" -type file -force -value "Tag Number,Final Dest,Tracking ID,Attribute Ref #`n"
$exceptionfile= New-Item "**FILEPATH**\$(get-date -format dd-MM-yyyy)_delivery_exceptions.csv" -type file -force -value "Tag Number,Final Dest,Tracking ID,Attribute Ref #`n"
foreach ($i in $file) {
    $tagnum= $i | select-object -expandproperty "Tag Number"
    $trackingid = $i | select-object -expandproperty "Tracking ID"
    $refnum= $i | select-object -expandproperty "Attribute Ref #"
    $findest= $i | select-object -expandproperty "Final Dest"
    if ($trackingid.length -gt 1){
        $uri= "**URI**$trackingid"
        $html= Invoke-Webrequest -URI $uri
        $fields= $HTML.ParsedHtml
        $trackstate= $fields.getElementByID("ttc_tt_spStatus")
        if($trackstate.innerText -like "*Business Days*"){
        break}
        elseif($trackstate.innerText -like "Delivered*"){
            "$tagnum, $findest, $trackingid, $refnum" | Add-Content $newfile
        }
        elseif($trackstate.innerText -like "*Attempt Made*"){
            "$tagnum, $findest, $trackingid, $refnum" | Add-Content $exceptionfile
        }
    }
}

Solution

  • I've got an easy answer, and a good answer. The easy answer is to add `n to your -value parameter.

    New-Item C:\Path\To\File.csv -value "Header,Header,Header,Header`n"
    

    That just appends the New Line character to the end of your header text, and anything added after that goes on the next line.

    The better answer is that you're probably going about things the hard way. Rather than adding content to a file several times I would recommend collecting the data (unless there is a huge amount), and then outputting it all at once at the end. If you're looking at making a CSV you should make an array of objects, and then just export those objects at the end. Without more data on what you're doing it's hard to give a viable example that you can relate to. Let me know if you want more info on this, and update your question with some more code showing what you're doing.

    Edit: Looking at what you have, I think I'd just add a status property to each item, and then export once at the end for each file filtering on the status. Something like this:

    $file= import-csv "**FILEPATH**\Intransit_report.csv"
    foreach ($i in ($file | Where{$_.'Tracking ID'})) {
        $trackingid = $i | select-object -expandproperty "Tracking ID"
        $uri= "**URI**$trackingid"
        $html= Invoke-Webrequest -URI $uri
        $fields= $HTML.ParsedHtml
        $trackstate= $fields.getElementByID("ttc_tt_spStatus")
        Switch -Regex ($trackstate.innerText){
            "Business Days" {Add-Member -InputObject $i -NotePropertyName 'Status' -NotePropertyValue 'In Transit';Continue}
            "Delivered" {Add-Member -InputObject $i -NotePropertyName 'Status' -NotePropertyValue 'Delivered';Continue}
            "Attempt Made" {Add-Member -InputObject $i -NotePropertyName 'Status' -NotePropertyValue 'Attempt Made'}
        }
    }
    
    $file | Where{$_.Status -eq 'Delivered'} | Select 'Tag Number','Final Dest','Tracking ID','Attribute Ref #' | Export-CSV "**FILEPATH**\$(get-date -format dd-MM-yyyy)_delivered.csv" -NoTypeInformation
    $file | Where{$_.Status -eq 'Attempt Made'} | Select 'Tag Number','Final Dest','Tracking ID','Attribute Ref #' | Export-CSV "**FILEPATH**\$(get-date -format dd-MM-yyyy)_delivery_exceptions.csv" -NoTypeInformation
    

    This way it's doing all of the processing at once, and then writting twice, instead of writing to the drive X number of times, where X is the number of items that are either already delivered or have had delivery attempts made.