Adding a header to a '|' delimited CSV file in Powershell?

Question

I was wondering if anybody knows a way to achieve this without breaking/mesing with the data itself?

I have a CSV file which is delimited by '|' which was created by retrieving data from Sharepoint using an SPQuery and exported using out-file (because export-csv is not an option since I would have to store the data in a variable and this would eat at the RAM of the server, querying remotely unfortuntely will also not work so i have to do this on the server itself). Nevertheless I have the Data i need but i want to perform some manipulations and move and autocalc certain data within an excel file and export the said excel file.

The problem I have right now is that I sort of need a header to the file. I have tried using the following code:

$header ="Name|GF_GZ|GF_Title|GF_UniqueId|GF_OldId|GFURL|GF_ITEMREDIRECTID"
$file = Import-Csv inputfilename.csv -Header $header | Export-Csv D:\outputfilename.csv

In powershell but the issue here is that when i perform the second Export-Csv it will delimit at anything that has a comma and thus remove it, i sort of need the data to remain intact.

I have tried playing with the -Delimit '|' setting both on the import and the export path but no matter what i do it seems to be cutting off the data. Is there a better way to simply add a row at the Top (a header) without messing with the already existing file structure? I have found out that using a delimiter such as -delimiter '°' or any other special case character will remove my problem entirely, but i can never be sure if such a character is going to show up in the dataset and thus (as stated already) am looking for a more "elegant" solution.

Thanks

websch01ar · Accepted Answer · 2014-01-17 13:35:58Z

1

One option you have is to create the original CSV with the headers first. Then when you are exporting the SharePoint data, use the switch -Append in the Out-File command to append the SP data to the CSV.

answered Jan 17, 2014 at 13:35

websch01ar

2,1232 gold badges13 silver badges22 bronze badges

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

5 Comments

Orbital Over a year ago

Yes this is an option that I have considered, but i want the whole process to be automated as much as humanely possible and the next steps (which i still haev to code) involve processnig data in excel from multiple output files and then performing calculations so that its already preformatted for my superiors to be able to see data quickly. So while this technically is a solution its not something that helps me in the automation part. Thanks for the comment though i appreciate it.

websch01ar Over a year ago

Not sure I follow since you are automatically building the CSV with the headers first and then appending to it. Do you need the PowerShell script on how to create a CSV with headers? I assumed you could figure that one out. I did not mean to indicate that you should manually create this file.

Orbital Over a year ago

This is my code do { $itemcoll = $spSourceList.GetItems($query); foreach ($item in $itemcoll) { #write-host $($item.Url) $($item["Sachbearbeiter"]); $Name = $item["Name"]; $GF_GZ = $item["GF_GZ"]; $GF_Title = $item["GF_Title"]; $GF_UniqueId = $item["UniqueId"]; $GF_OldId = $item["OldItemId"]; #$ITyp2 = $item["Content Type"]; write-output "$Name|$GF_GZ|$GF_Title|$GF_UniqueId|$GF_OldId|$GFURL|$ITEMREDIRECTID" | Out-File -append D:\BG_SOF_SP01_Abgeschlossen_14Jan.csv #$($item.Url)|$($item.UniqueId) }

Orbital Over a year ago

THe reason i am not saving the query output to a variable first and adding data using $var += result from loop is the sheer amount of datasize is killing the RAM of the server (powershell using 3-4 Gigabyte of RAM just to temporarily save the data), whereas simlpy appending using out-file will work. I am well aware that Powershell supports -append for the export-csv command since 3.0 or 4.0 but we dont have that version availble on the server. So out-file is the only option.

websch01ar Over a year ago

Sorry for the delay in responding, but Out-File supports the -Append switch and has since version 3.0. What version of SPS are you running?

Sencha718 · Accepted Answer · 2016-07-20 15:58:44Z

0

I wouldn't even bother messing with it in csv format.

$header ="Name|GF_GZ|GF_Title|GF_UniqueId|GF_OldId|GFURL|GF_ITEMREDIRECTID"
$in_file = '.\inputfilename.csv'
$out_file = '.\outputfilename.csv'
$x = Get-Content $in_file
Set-Content $out_file -Value $header,$x

There's probably a more eloquent/refined two-liner for some of this, but this should get you what you need.

answered Jul 20, 2016 at 15:58

Sencha718

631 silver badge8 bronze badges

Collectives™ on Stack Overflow

Adding a header to a '|' delimited CSV file in Powershell?

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related