Using Powershell to write out two header rows without deleting existing data

Question

I have a need to generate two header rows to an existing csv file because the system where the csv will be uploaded needs the two header rows. The csv file will contain data that I want to keep.

I have been testing a powershell script to do this, and I can write a single row of headers, but am struggling to write two rows.

Below is the powershell script I am currently trying to build out.

$file = "C:\Users\_svcamerarcgis\Desktop\Test.csv"
$filedata = import-csv $file -Header WorkorderETL 'n ICFAORNonICFA, WONUmber, Origin
$filedata | export-csv $file -NoTypeInformation

The end result I'm looking for should be as follows:

WorkorderETL
ICFAORNonICFA, WONUmber, Origin
xxx,yyy,zzz

why do you want header in two different lines? are you talking about writing something that has nothing to do with header? like a topic or of the sort ? — Jawad
– Jawad, Commented Jan 23, 2020 at 21:44
Yes, I need "WorkorderETL" as the first line in the csv file so that the system I'm uploading into recognizes what tool it needs to use. I have no control of the system that I'm uploading to. The second row under "WorkorderETL" will be the actual headers followed by the data. — ChisumTrail
– ChisumTrail, Commented Jan 23, 2020 at 21:52
I dont think you'll be able to get it imported and exported with an additional string above. First line is considered headers and rest is data. — Jawad
– Jawad, Commented Jan 23, 2020 at 22:13

mklement0 · Accepted Answer · 2020-02-10 14:30:49Z

1

The sole purpose of Import-Csv's -Header parameter is to provide an array of column names to serve as the property names of the custom objects that the CSV rows are parsed into - you cannot repurpose that for special output formatting for later exporting.

You can use the following approach instead, bypassing the need for Import-Csv and Export-Csv altogether (PSv5+):

$file = 'C:\Users\User\OneDrive\Scripts\StackTesting\Test.csv'

# Prepend the 2-line header to the existing file content
# and save it back to the same file
# Adjust the encoding as needed.
@'
WorkorderETL
ICFAORNonICFA,WONUmber,Origin

'@ + (Get-Content -Raw $file) | Set-Content $file -NoNewline -Encoding utf8

To be safe, be sure to create a backup of the original file first. Since the file is being read (in full) and rewritten in the same pipeline, there's a hypothetical chance of data loss if writing back to the input file get interrupted.

edited Feb 10, 2020 at 14:30

answered Jan 24, 2020 at 3:51

mklement0

453k68 gold badges728 silver badges988 bronze badges

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

Comments

Owain Esau · Accepted Answer · 2020-01-24 00:37:26Z

1

You may be better trying to handle this as a text file, considering you are just trying to add a single line at the top of the CSV:

$file = "C:\Users\User\OneDrive\Scripts\StackTesting\Test.csv"
$CSV = "c1r1, c2r1, c3r1 `nc1r2, c2r2, c3r2" 

$filedata = Get-Content $file 

$filedata = "WorkorderETL`n" + $CSV

$filedata | Out-File $file

This will resul in the CSV file holding:

WorkorderETL
c1r1, c2r1, c3r1 
c1r2, c2r2, c3r2

Which looks to be what you want.

answered Jan 24, 2020 at 0:37

Owain Esau

1,9222 gold badges23 silver badges37 bronze badges

Collectives™ on Stack Overflow

Using Powershell to write out two header rows without deleting existing data

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related