Create Record using Headers from a .csv-CodePudding

<EDIT: I kind of have it working, but in order to get it to work, my template csv has to have a blank line for every line I am going to be adding to it. So, if I could figure out how to add lines to the imported empty (just a header row) csv file, I could then use export-csv at the end. (It would be somewhat slower, but it would at least work.)>

I am creating a .csv file in PowerShell. The output file has 140 columns. Many of them are null.

I started out just doing

    $out = 'S-' $Snum ',,,,,TRUE,,,,,' 'S-' $Snum ',"'
    $out = $out   '{0:d9}' -f $item.SupplierCode2

until I had filled all the columns with the correct value. But, the system that is reading the output keeps changing the column locations. So, I wanted to take the header row from the template for the system and use that to name the columns. Then, if the columns change location, it won't matter because I will be referring to it by name.

Because there are so many columns, I'm trying to avoid a solution that has me enter all the column names. By using a blank .csv with just the headers, I can just paste that into the csv whenever it changes and I won't have to change my code.

So, I started by reading my csv file in so I can use the headers.

$TempA = Import-Csv -Path $Pathta -Encoding Default

Then I was hoping I could do something like this:

$TempA.'Supplier Key' = "S-$Snum"
$TempA.'Auto Complete' = "TRUE"
$TempA.'Supplier ID' = "S-$Snum"
$tempA.'Supplier Data - Supplier Reference ID' = '{0:d9}' -f $item.SupplierCode2

I would only need to fill in the fields that have values, everything else would be null. Then I was thinking I could write out this record to a file. My old write looked like this

$writer2.WriteLine($out)

I wanted to write the line from the new csv line instead

$writer2.WriteLine($TempA)

I'd rather use streams if I can because the files are large and using add-Content really slows things down.

I know I need to do something to add a line to $TempA and I would like each loop to start with a new line (with all nulls) because there are times when certain lines only have a small subset of the values populated.

Clearly, I'm not taking the correct approach here. I'd really appreciate any advice anyone can give me.

Thank you.

CodePudding user response：

If you only want to fill in certain fields, and don't mind using Export-Csv you can use the -append and -force switches, and it will put the properties in the right places. For example, if you had the template CSV file with only the column names in it you could do:

$Output = ForEach($item in $allItems){
    [PSCustomObject]@{
        'Supplier Key' = "S-$Snum"
        'Auto Complete' = "TRUE"
        'Supplier ID' = "S-$Snum"
        'Supplier Data - Supplier Reference ID' = '{0:d9}' -f $item.SupplierCode2
    }
}
$Output | Export-Csv -Path $Pathta -Append -Force

That would create objects with only the four properties that you are interested in, and then output them to the CSV in the correct columns, adding commas as needed to create blank values for all other columns.