Powershell:  Adding column headers to the end of a CSV file

jomoran · 2016-01-18T10:03:27.000Z

Hello - I wonder if you could be of assistance?

I currently have a csv file with approx 90 + columns with headers but it also has addtional columns without any headers.

So that when I import it they do not come through

Is there a way I can add 15 to 20 columns headers to the end , to ensure the un named columns are included.

Many thanks

& regards

Jo

martin9700 · 2016-01-18T10:59:08.000Z

Can’t you just edit the file and put them in? Or is this something you do on a regular basis? How much effort have you put in on getting the source file fixed? This would seem to be the best route to go. If that’s not possible you could do something like this:

$File = Get-Content c:\path\Original.csv
$FirstLine = $true

$Result = ForEach ($Line in $File)
{
    If ($FirstLine)
    {
        Write-Output "$Line,put,the,other,columns,here"
        $FirstLine = $false
    }
    Else
    {
        Write-Output $Line
    }
}

$Result    #this now has your corrected CSV, you can Export it to a CSV and read it with your script or:
$Objects = $Result | ConvertFrom-Csv      #just convert it to objects (like Import-CSV does) and begin work

martin9700 · 2016-01-18T12:02:08.000Z

Another option is:

$Objects = Get-Content c:\path\originalcsv | Select -Skip 1 | ConvertFrom-CSV -Header Specify,Your,Own,Headers,Here,Must,Do,All,Of,Them

bobmccoy · 2016-01-18T17:21:57.000Z

Is it always the same number of columns, and if so how many columns are there?

Are all the labeled columns contiguous, or are there some intervening columns that don’t have names?

jomoran · 2016-01-19T06:51:58.000Z

Hello again

in answer to your question bobMccoy there will be a maximum on this occasion of 15 columns which will range from UB_01 to UB15, ( UB standing for unbundled) & they are continguous.

I have no control over the number of columns as it is an extract generated from a Governement portal.

I attemtpted to no avail to get a successfull result using the code Martin so kindly provided but it was a little slow and would not import correctly

I really only need to either

a: remove and replace the header with a correct header

b: edit the first line.

c: stick with creating the table in SSIS each time

the reason I am trying to do this is that it would seem there are going to be more files like this included in the upload and It would really be a boon if this process could be a little more fast & flexible.

regards

Jo

craigduff · 2016-01-19T12:52:37.000Z

If you just want to jam 15 additional headers on then you can do something like one of these:

$file = 'T:\17\in.csv'

$sr = [System.IO.StreamReader]($file)

$firstLine = $sr.ReadLine() + ',UB1,UB2,UB3'
$rest = $sr.ReadToEnd()

$sr.Close()

$sw = [System.IO.StreamWriter]($file)

$sw.WriteLine($firstLine)
$sw.Write($rest)

$sw.close()

$file = 'T:\17\in.csv'

$sr = [System.IO.StreamReader]($file)

$firstLine = $sr.ReadLine() + ',UB1,UB2,UB3'

$csv = $firstline + "`r`n" + $sr.ReadToEnd() |
ConvertFrom-Csv

$sr.Close()

jomoran · 2016-01-19T13:40:13.000Z

Thank you soo much Cduff.

Your first example appears to have worked in that it has everything in the right place.

As I had this before I was dismayed when SSIS actually imported the first 10 cols of the file into the first 10 newly created UB cols and then put all the other cols as one string into one column at the end and then failed the package on a truncation error.

So I am now getting over the signing issues to execute the script and then do the import.

I think perhaps I will just do a quick dummy run to see if it takes the file as is.

Thank you once again

Jo

jomoran · 2016-01-20T09:20:42.000Z

Morning Guys

Cduff many thanks;

your second script didn’t seem to make any alteration at all - sorry

Your first script:-

So close, an unusual result : in the original last column (prior to the insertion) where there is no data it is populated with data from the first column & where there is data it concatenates to the exisiting data - novel ?

the following columns are then populated with the data from the 2nd , 3rd, 4th cols etc respectively until it runs out of cols it then puts the remainder of the columns data in the last column.

I am still no nearer to a final solution.

Knowing that this is the way forward for this year on year process which has updates I am going to persevere.

This will go in as I want it.

Thanking you for your help it really is much appreciated

regards

Jo%uFEFF

martin9700 · 2016-01-20T09:51:52.000Z

jomoran:

So close, an unusual result : in the original last column (prior to the insertion) where there is no data it is populated with data from the first column & where there is data it concatenates to the exisiting data - novel ?

the following columns are then populated with the data from the 2nd , 3rd, 4th cols etc respectively until it runs out of cols it then puts the remainder of the columns data in the last column.

Wait, what? I’m not sure I understand… are the columns not consistent? Would it be possible for you to post the first 10 lines of the CSV? Please sanitize the data as needed. Otherwise we’re just guessing on this side what needs to be done.

Thanks

jomoran · 2016-01-20T11:42:17.000Z

Thanks for your time Martin

Wait, what? I’m not sure I understand - Thats 2 of us

I have attached an excel file with three tabs

Original is the original

Original upd is the updated after power shell

and the third tab is what SSIS is seeing to pull through .

This may make it a bit clearer I hope,

Just in case I have also listed the columns below

PROCODET,PROVSPNO,EPIORDER,STARTAGE,Sex,CLASSPAT,ADMISORC,ADMIMETH,DISDEST,DISMETH,EPIDUR,MAINSPEF,NEOCARE,TRETSPEF,DIAG_01,DIAG_02,DIAG_03,DIAG_04,DIAG_05,DIAG_06,DIAG_07,DIAG_08,DIAG_09,DIAG_10,DIAG_11,DIAG_12,DIAG_13,DIAG_14,DIAG_15,DIAG_16,DIAG_17,DIAG_18,DIAG_19,DIAG_20,DIAG_21,DIAG_22,DIAG_23,DIAG_24,OPER_01,OPER_02,OPER_03,OPER_04,OPER_05,OPER_06,OPER_07,OPER_08,OPER_09,OPER_10,OPER_11,OPER_12,OPER_13,OPER_14,OPER_15,OPER_16,OPER_17,OPER_18,OPER_19,OPER_20,OPER_21,OPER_22,OPER_23,OPER_24,CRITICALCAREDAYS,REHABILITATIONDAYS,SPCDAYS,Local_Patient_ID,CDS_Unique_Identifier,START_DATE_SPELL,END_DATE_SPELL,START_DATE_EPISODE,END_DATE_EPISODE,Consultant_Code,RowNo,FCE_HRG,GroupingMethodFlag,DominantProcedure,FCE_PBC,CalcEpidur,ReportingEPIDUR,FCETrimpoint,FCEExcessBeddays,SpellReportFlag,SpellHRG,SpellGroupingMethodFlag,SpellDominantProcedure,SpellPDiag,SpellSDiag,SpellEpisodeCount,SpellLOS,ReportingSpellLOS,SpellTrimpoint,SpellExcessBeddays,SpellCCDays,SpellPBC,UnbundledHRGs

These are the columns I am adding

,UB02,UB03,UB04,UB05,UB06,UB07,UB08,UB09,UB10,UB11,UB12,UB13,UB14,ub15

but when I generate/update the file I end up with the data appending itself to the end column populating the last column with the first cols and the actual data for that column - if I had wanted to do this it would never have happened .

regards

Jo

uip_fce.xlsx (273 KB)

martin9700 · 2016-01-20T12:15:41.000Z

OK, so I took the first worksheet in the spreadsheet and saved it as a CSV. I then ran this code and got what looked like a good result to me:

Get-Content .\uip_fce.csv | Select -Skip 1 | ConvertFrom-Csv -Header PROCODET,PROVSPNO,EPIORDER,STARTAGE,Sex,CLASSPAT,ADMISORC,ADMIMETH,DISDEST,DISMETH,EPIDUR,MAINSPEF,NEOCARE,TRETSPEF,DIAG_01,DIAG_02,DIAG_03,DIAG_04,DIAG_05,DIAG_06,DIAG_07,DIAG_08,DIAG_09,DIAG_10,DIAG_11,DIAG_12,DIAG_13,DIAG_14,DIAG_15,DIAG_16,DIAG_17,DIAG_18,DIAG_19,DIAG_20,DIAG_21,DIAG_22,DIAG_23,DIAG_24,OPER_01,OPER_02,OPER_03,OPER_04,OPER_05,OPER_06,OPER_07,OPER_08,OPER_09,OPER_10,OPER_11,OPER_12,OPER_13,OPER_14,OPER_15,OPER_16,OPER_17,OPER_18,OPER_19,OPER_20,OPER_21,OPER_22,OPER_23,OPER_24,CRITICALCAREDAYS,REHABILITATIONDAYS,SPCDAYS,Local_Patient_ID,CDS_Unique_Identifier,START_DATE_SPELL,END_DATE_SPELL,START_DATE_EPISODE,END_DATE_EPISODE,Consultant_Code,RowNo,FCE_HRG,GroupingMethodFlag,DominantProcedure,FCE_PBC,CalcEpidur,ReportingEPIDUR,FCETrimpoint,FCEExcessBeddays,SpellReportFlag,SpellHRG,SpellGroupingMethodFlag,SpellDominantProcedure,SpellPDiag,SpellSDiag,SpellEpisodeCount,SpellLOS,ReportingSpellLOS,SpellTrimpoint,SpellExcessBeddays,SpellCCDays,SpellPBC,UnbundledHRGs,UB02,UB03,UB04,UB05,UB06,UB07,UB08,UB09,UB10,UB11,UB12,UB13,UB14,ub15 | Export-Csv .\uip_fce-converted.csv -NoTypeInformation

Sorry it looks a little messy, there are tricks we could use the make the code look a little cleaner but ultimately I think this will do what you want.

craigduff · 2016-01-20T13:09:36.000Z

My 1st and 2nd script do essentially the same thing. I wasn’t sure if you were just trying to alter the file or import the data in with column headers. The 2nd script intentionally didn’t alter the file.

From the described symptoms, my guess is that the import csv is using unix style line endings, but my code is using the .Net default of windows style line endings and that is somehow messing with the import. Just a guess really.

If that guess is correct, then this modification may help.

$file = 'T:\17\in.csv'

$sr = [System.IO.StreamReader]($file)

$firstLine = $sr.ReadLine()   ',UB1,UB2,UB3'
$rest = $sr.ReadToEnd()

$sr.Close()

$sw = [System.IO.StreamWriter]($file)
$sw.NewLine = "`n"

$sw.WriteLine($firstLine)
$sw.Write($rest)

$sw.close()

But, if the first set of 90 or so columns is always the same, then what Martin posted above should work and be the easiest solution.

bobmccoy · 2016-01-20T13:23:45.000Z

There’s a couple of problems here. Jo your requirements seem to be pretty imprecise, so we’re spending a lot of time guessing. So let me ask this – is it that your are trying to split out the values in the UnbundledHRGs column?

And if that was the case, it would be incredibly helpful to see an example where the UB* columns were populated.

jomoran · 2016-01-20T13:29:21.000Z

Quick question Martin

Is there a particular reason for the output being

“SpellTrimpoint” “SpellExcessBeddays” “SpellCCDays” “SpellPBC” “UnbundledHRGs”

“” “” “0” “PBC0213B” “XD31Z”

“” “” “0” “PBC02U1”

“” “” “0” “PBC0213A”

How did the quotes creep in - any ideas

thank you once again for your help it really is appreciated

regards

Jo

martin9700 · 2016-01-20T13:50:41.000Z

Quotes are a standard part of CSV’s and the Export-CSV cmdlet automatically adds them.

jomoran · 2016-01-21T12:25:37.000Z

Hello guys

Thank you for all your help.

Martins method works in a script and produces the output which uploads to the database in the correct format with all the data.

My next issue is running it as a script as it needs a certificate to execute but that is for another question.

Thanks soo much you have been very patient and I appreciate all your help & support

regards

Jo

psophos · 2016-01-21T13:43:02.000Z

A certificate?

What is the execution policy on the system that you’re trying to run it on?

Get-ExecutionPolicy

jomoran · 2016-01-21T14:01:50.000Z

Hello Martin B

Currently it is restricted so I can run unsaved scripts but not executables

which is a little unhelpful when trying to execute it in SSIS

Thanks

regards

Jo

psophos · 2016-01-21T14:14:39.000Z

Ah, set it to:

Set-ExecutionPolicy RemoteSigned

Should allow you to run your scripts on it.

Lots of people advocate setting it to unrestricted but I’ve never had a problem with RemoteSigned.