How can I use powershell to add a text file as a column to an existing csv?-CodePudding

I have found lots of questions like this one which explain how to add a column with the same value repeated throughout the csv.

What I have is a bit different. I have randlist.txt which looks like this:

There are over 40,000 values. The same as the number of rows in this allpeeps.csv file, which looks like this

institutiongroup,personid,iscomplete,recordcreateddate,recordupdateddate,lastknowninstitution,personinstitutions,eventprogrammeapplications,customdata,hereadyyear,trackedbyhesa,foundbyhesa,sourceid,nssec,disabilityinfo,yeargroup,yeargroupoffset,expectedheentryyear,lastknownphase,autocalculateheentryyear,cohortyear,recordstateenum,photopermission,howmanyindicsusepolar4,polar4q1,polar4q12,polar4rate,polar4entrants,polar4pop,howmanyindicsusepolar3,polar3ahe,polar4ahe,tundraquintile,imddecile,estdecile,idacidecile,nssec13,nssec47,nssec48,nssecnc
"@{institutionId=22343; displaySummary=Group ABC); name=Group ABCcordState=Open}",1,TRUE,21/03/2019,21/03/2019,,"     {        institution: {          institutionId: 9921,          displaySummary: Eggbuckland Community College 140104 (#009921),          name: Eggbuckland Community College,          laestabNo: 8794185,          ukprn: 10043058,          urn: 140104,          ucasCode: 11246,          educationPhase: 2,          educationPhaseName: Secondary,          town: Plymouth,          recordState: Open        },        educationPhase: 2,        priority: 0,        recordCreatedDate: 2019-03-21T12:39:06      }  ",System.Object[],{},2023,FALSE,FALSE,,0,,Year Group 12,0,,,TRUE,2023,0,,0 out of 4,N,N,32.7,140,425,0 out of 4,2,3,4,8,9,8,0.61,0.38,0.39,0.06
"@{institutionId=22343; displaySummary=Group ABC); name=Group ABCcordState=Open}",2,TRUE,21/03/2019,21/03/2019,,"     {        institution: {          institutionId: 9921,          displaySummary: Eggbuckland Community College 140104 (#009921),          name: Eggbuckland Community College,          laestabNo: 8794185,          ukprn: 10043058,          urn: 140104,          ucasCode: 11246,          educationPhase: 2,          educationPhaseName: Secondary,          town: Plymouth,          recordState: Open        },        educationPhase: 2,        priority: 0,        recordCreatedDate: 2019-03-21T12:48:12      }  ",System.Object[],{},2023,FALSE,FALSE,,0,,Year Group 12,0,,,TRUE,2023,0,,4 out of 4,Y,Y,19.6,105,545,4 out of 4,2,1,2,3,2,3,0.36,0.6,0.64,0.06
"@{institutionId=22343; displaySummary=Group ABC); name=Group ABCcordState=Open}",3,TRUE,21/03/2019,21/03/2019,,"     {        institution: {          institutionId: 9921,          displaySummary: Eggbuckland Community College 140104 (#009921),          name: Eggbuckland Community College,          laestabNo: 8794185,          ukprn: 10043058,          urn: 140104,          ucasCode: 11246,          educationPhase: 2,          educationPhaseName: Secondary,          town: Plymouth,          recordState: Open        },        educationPhase: 2,        priority: 0,        recordCreatedDate: 2019-03-21T13:07:23      }  ",System.Object[],{},2023,FALSE,FALSE,,0,,Year Group 12,0,,,TRUE,2023,0,,1 out of 4,N,Y,27.1,160,585,0 out of 4,4,2,3,7,6,8,0.52,0.46,0.48,0.07
"@{institutionId=22343; displaySummary=Group ABC); name=Group ABCcordState=Open}",4,TRUE,21/03/2019,21/03/2019,,"     {        institution: {          institutionId: 9921,          displaySummary: Eggbuckland Community College 140104 (#009921),          name: Eggbuckland Community College,          laestabNo: 8794185,          ukprn: 10043058,          urn: 140104,          ucasCode: 11246,          educationPhase: 2,          educationPhaseName: Secondary,          town: Plymouth,          recordState: Open        },        educationPhase: 2,        priority: 0,        recordCreatedDate: 2019-03-21T13:07:24      }  ",System.Object[],{},2023,FALSE,FALSE,,0,,Year Group 12,0,,,TRUE,2023,0,,2 out of 4,Y,Y,19.6,105,545,1 out of 4,2,1,3,7,4,8,0.54,0.44,0.46,0.07
"@{institutionId=22343; displaySummary=Group ABC); name=Group ABCcordState=Open}",5,FALSE,21/03/2019,21/03/2019,,"     {        institution: {          institutionId: 9921,          displaySummary: Eggbuckland Community College 140104 (#009921),          name: Eggbuckland Community College,          laestabNo: 8794185,          ukprn: 10043058,          urn: 140104,          ucasCode: 11246,          educationPhase: 2,          educationPhaseName: Secondary,          town: Plymouth,          recordState: Open        },        educationPhase: 2,        priority: 0,        recordCreatedDate: 2019-03-21T13:07:24      }  ",System.Object[],{},2023,FALSE,FALSE,,0,,Year Group 12,0,,,TRUE,2023,0,,,,,,,,,,,,,,,,,,
"@{institutionId=22343; displaySummary=Group ABC); name=Group ABCcordState=Open}",6,TRUE,21/03/2019,21/03/2019,,"     {        institution: {          institutionId: 9921,          displaySummary: Eggbuckland Community College 140104 (#009921),          name: Eggbuckland Community College,          laestabNo: 8794185,          ukprn: 10043058,          urn: 140104,          ucasCode: 11246,          educationPhase: 2,          educationPhaseName: Secondary,          town: Plymouth,          recordState: Open        },        educationPhase: 2,        priority: 0,        recordCreatedDate: 2019-03-21T13:07:25      }  ",System.Object[],{},2023,FALSE,FALSE,,0,,Year Group 12,0,,,TRUE,2023,0,,4 out of 4,Y,Y,20.8,145,705,4 out of 4,2,1,1,4,4,3,0.42,0.53,0.58,0.07
"@{institutionId=22343; displaySummary=Group ABC); name=Group ABCcordState=Open}",7,TRUE,21/03/2019,21/03/2019,,"     {        institution: {          institutionId: 9921,          displaySummary: Eggbuckland Community College 140104 (#009921),          name: Eggbuckland Community College,          laestabNo: 8794185,          ukprn: 10043058,          urn: 140104,          ucasCode: 11246,          educationPhase: 2,          educationPhaseName: Secondary,          town: Plymouth,          recordState: Open        },        educationPhase: 2,        priority: 0,        recordCreatedDate: 2019-03-21T13:07:25      }  ",System.Object[],{},2023,FALSE,FALSE,,0,,Year Group 12,0,,,TRUE,2023,0,,1 out of 4,N,Y,27.1,160,585,0 out of 4,2,2,2,6,5,7,0.49,0.48,0.51,0.06
"@{institutionId=22343; displaySummary=Group ABC); name=Group ABCcordState=Open}",8,FALSE,21/03/2019,21/03/2019,,"     {        institution: {          institutionId: 35023,          displaySummary: IKB Academy 142127 (#035023),          name: IKB Academy,          laestabNo: 8004004,          ukprn: 10053995,          urn: 142127,          ucasCode: 19579,          educationPhase: 2,          educationPhaseName: Secondary,          town: Bristol,          recordState: Open        },        educationPhase: 2,        priority: 0,        recordCreatedDate: 2019-03-28T15:47:06      }  ",System.Object[],{},Unknown,FALSE,FALSE,,0,,Unknown,0,,,TRUE,,0,,,,,,,,,,,,,,,,,,
"@{institutionId=22343; displaySummary=Group ABC); name=Group ABCcordState=Open}",9,FALSE,21/03/2019,21/03/2019,,"     {        institution: {          institutionId: 35023,          displaySummary: IKB Academy 142127 (#035023),          name: IKB Academy,          laestabNo: 8004004,          ukprn: 10053995,          urn: 142127,          ucasCode: 19579,          educationPhase: 2,          educationPhaseName: Secondary,          town: Bristol,          recordState: Open        },        educationPhase: 2,        priority: 0,        recordCreatedDate: 2019-03-28T15:47:07      }  ",System.Object[],{},Unknown,FALSE,FALSE,,0,,Unknown,0,,,TRUE,,0,,,,,,,,,,,,,,,,,,
"@{institutionId=22343; displaySummary=Group ABC); name=Group ABCcordState=Open}",10,FALSE,21/03/2019,21/03/2019,,"     {        institution: {          institutionId: 35023,          displaySummary: IKB Academy 142127 (#035023),          name: IKB Academy,          laestabNo: 8004004,          ukprn: 10053995,          urn: 142127,          ucasCode: 19579,          educationPhase: 2,          educationPhaseName: Secondary,          town: Bristol,          recordState: Open        },        educationPhase: 2,        priority: 0,        recordCreatedDate: 2019-03-28T15:47:07      }  ",System.Object[],{},Unknown,FALSE,FALSE,,0,,Unknown,0,,,TRUE,,0,,,,,,,,,,,,,,,,,,

The output I want is

rand,institutiongroup,personid,iscomplete,recordcreateddate,recordupdateddate,lastknowninstitution,personinstitutions,eventprogrammeapplications,customdata,hereadyyear,trackedbyhesa,foundbyhesa,sourceid,nssec,disabilityinfo,yeargroup,yeargroupoffset,expectedheentryyear,lastknownphase,autocalculateheentryyear,cohortyear,recordstateenum,photopermission,howmanyindicsusepolar4,polar4q1,polar4q12,polar4rate,polar4entrants,polar4pop,howmanyindicsusepolar3,polar3ahe,polar4ahe,tundraquintile,imddecile,estdecile,idacidecile,nssec13,nssec47,nssec48,nssecnc
24830,@{institutionId=22343; displaySummary=Group ABC); name=Group ABCcordState=Open},1,TRUE,21/03/2019,21/03/2019,,"     {        institution: {          institutionId: 9921,          displaySummary: Eggbuckland Community College 140104 (#009921),          name: Eggbuckland Community College,          laestabNo: 8794185,          ukprn: 10043058,          urn: 140104,          ucasCode: 11246,          educationPhase: 2,          educationPhaseName: Secondary,          town: Plymouth,          recordState: Open        },        educationPhase: 2,        priority: 0,        recordCreatedDate: 2019-03-21T12:39:06      }  ",System.Object[],{},2023,FALSE,FALSE,,0,,Year Group 12,0,,,TRUE,2023,0,,0 out of 4,N,N,32.7,140,425,0 out of 4,2,3,4,8,9,8,0.61,0.38,0.39,0.06
4557,@{institutionId=22343; displaySummary=Group ABC); name=Group ABCcordState=Open},2,TRUE,21/03/2019,21/03/2019,,"     {        institution: {          institutionId: 9921,          displaySummary: Eggbuckland Community College 140104 (#009921),          name: Eggbuckland Community College,          laestabNo: 8794185,          ukprn: 10043058,          urn: 140104,          ucasCode: 11246,          educationPhase: 2,          educationPhaseName: Secondary,          town: Plymouth,          recordState: Open        },        educationPhase: 2,        priority: 0,        recordCreatedDate: 2019-03-21T12:48:12      }  ",System.Object[],{},2023,FALSE,FALSE,,0,,Year Group 12,0,,,TRUE,2023,0,,4 out of 4,Y,Y,19.6,105,545,4 out of 4,2,1,2,3,2,3,0.36,0.6,0.64,0.06
30795,@{institutionId=22343; displaySummary=Group ABC); name=Group ABCcordState=Open},3,TRUE,21/03/2019,21/03/2019,,"     {        institution: {          institutionId: 9921,          displaySummary: Eggbuckland Community College 140104 (#009921),          name: Eggbuckland Community College,          laestabNo: 8794185,          ukprn: 10043058,          urn: 140104,          ucasCode: 11246,          educationPhase: 2,          educationPhaseName: Secondary,          town: Plymouth,          recordState: Open        },        educationPhase: 2,        priority: 0,        recordCreatedDate: 2019-03-21T13:07:23      }  ",System.Object[],{},2023,FALSE,FALSE,,0,,Year Group 12,0,,,TRUE,2023,0,,1 out of 4,N,Y,27.1,160,585,0 out of 4,4,2,3,7,6,8,0.52,0.46,0.48,0.07
19711,@{institutionId=22343; displaySummary=Group ABC); name=Group ABCcordState=Open},4,TRUE,21/03/2019,21/03/2019,,"     {        institution: {          institutionId: 9921,          displaySummary: Eggbuckland Community College 140104 (#009921),          name: Eggbuckland Community College,          laestabNo: 8794185,          ukprn: 10043058,          urn: 140104,          ucasCode: 11246,          educationPhase: 2,          educationPhaseName: Secondary,          town: Plymouth,          recordState: Open        },        educationPhase: 2,        priority: 0,        recordCreatedDate: 2019-03-21T13:07:24      }  ",System.Object[],{},2023,FALSE,FALSE,,0,,Year Group 12,0,,,TRUE,2023,0,,2 out of 4,Y,Y,19.6,105,545,1 out of 4,2,1,3,7,4,8,0.54,0.44,0.46,0.07
15481,@{institutionId=22343; displaySummary=Group ABC); name=Group ABCcordState=Open},5,FALSE,21/03/2019,21/03/2019,,"     {        institution: {          institutionId: 9921,          displaySummary: Eggbuckland Community College 140104 (#009921),          name: Eggbuckland Community College,          laestabNo: 8794185,          ukprn: 10043058,          urn: 140104,          ucasCode: 11246,          educationPhase: 2,          educationPhaseName: Secondary,          town: Plymouth,          recordState: Open        },        educationPhase: 2,        priority: 0,        recordCreatedDate: 2019-03-21T13:07:24      }  ",System.Object[],{},2023,FALSE,FALSE,,0,,Year Group 12,0,,,TRUE,2023,0,,,,,,,,,,,,,,,,,,
42181,@{institutionId=22343; displaySummary=Group ABC); name=Group ABCcordState=Open},6,TRUE,21/03/2019,21/03/2019,,"     {        institution: {          institutionId: 9921,          displaySummary: Eggbuckland Community College 140104 (#009921),          name: Eggbuckland Community College,          laestabNo: 8794185,          ukprn: 10043058,          urn: 140104,          ucasCode: 11246,          educationPhase: 2,          educationPhaseName: Secondary,          town: Plymouth,          recordState: Open        },        educationPhase: 2,        priority: 0,        recordCreatedDate: 2019-03-21T13:07:25      }  ",System.Object[],{},2023,FALSE,FALSE,,0,,Year Group 12,0,,,TRUE,2023,0,,4 out of 4,Y,Y,20.8,145,705,4 out of 4,2,1,1,4,4,3,0.42,0.53,0.58,0.07
17331,@{institutionId=22343; displaySummary=Group ABC); name=Group ABCcordState=Open},7,TRUE,21/03/2019,21/03/2019,,"     {        institution: {          institutionId: 9921,          displaySummary: Eggbuckland Community College 140104 (#009921),          name: Eggbuckland Community College,          laestabNo: 8794185,          ukprn: 10043058,          urn: 140104,          ucasCode: 11246,          educationPhase: 2,          educationPhaseName: Secondary,          town: Plymouth,          recordState: Open        },        educationPhase: 2,        priority: 0,        recordCreatedDate: 2019-03-21T13:07:25      }  ",System.Object[],{},2023,FALSE,FALSE,,0,,Year Group 12,0,,,TRUE,2023,0,,1 out of 4,N,Y,27.1,160,585,0 out of 4,2,2,2,6,5,7,0.49,0.48,0.51,0.06
32468,@{institutionId=22343; displaySummary=Group ABC); name=Group ABCcordState=Open},8,FALSE,21/03/2019,21/03/2019,,"     {        institution: {          institutionId: 35023,          displaySummary: IKB Academy 142127 (#035023),          name: IKB Academy,          laestabNo: 8004004,          ukprn: 10053995,          urn: 142127,          ucasCode: 19579,          educationPhase: 2,          educationPhaseName: Secondary,          town: Bristol,          recordState: Open        },        educationPhase: 2,        priority: 0,        recordCreatedDate: 2019-03-28T15:47:06      }  ",System.Object[],{},Unknown,FALSE,FALSE,,0,,Unknown,0,,,TRUE,,0,,,,,,,,,,,,,,,,,,
39411,@{institutionId=22343; displaySummary=Group ABC); name=Group ABCcordState=Open},9,FALSE,21/03/2019,21/03/2019,,"     {        institution: {          institutionId: 35023,          displaySummary: IKB Academy 142127 (#035023),          name: IKB Academy,          laestabNo: 8004004,          ukprn: 10053995,          urn: 142127,          ucasCode: 19579,          educationPhase: 2,          educationPhaseName: Secondary,          town: Bristol,          recordState: Open        },        educationPhase: 2,        priority: 0,        recordCreatedDate: 2019-03-28T15:47:07      }  ",System.Object[],{},Unknown,FALSE,FALSE,,0,,Unknown,0,,,TRUE,,0,,,,,,,,,,,,,,,,,,
30067,@{institutionId=22343; displaySummary=Group ABC); name=Group ABCcordState=Open},10,FALSE,21/03/2019,21/03/2019,,"     {        institution: {          institutionId: 35023,          displaySummary: IKB Academy 142127 (#035023),          name: IKB Academy,          laestabNo: 8004004,          ukprn: 10053995,          urn: 142127,          ucasCode: 19579,          educationPhase: 2,          educationPhaseName: Secondary,          town: Bristol,          recordState: Open        },        educationPhase: 2,        priority: 0,        recordCreatedDate: 2019-03-28T15:47:07      }  ",System.Object[],{},Unknown,FALSE,FALSE,,0,,Unknown,0,,,TRUE,,0,,,,,,,,,,,,,,,,,,

The closest I've come so far is

$rands = Get-Content ./randlist.txt;$People = ./allpeeps.csv;$csv = @();for ($i = 0; $i -lt $rands.Count; $i  ){$row = "" | Select rands,People;$row.rands = $rands[$i];$row.People = $People[$i];$csv  = $row};$csv | Export-Csv ./newpeeps.csv -NoTypeInformation

However, this results in a csv with 2 columns - one 'rand' which looks right, and the other column is called people and has all of the information glommed together in one column. How can I just add this column to the csv?

CodePudding user response：

This might do the trick, since there is no need to do any modifications to the existing Csv you can treat it as a simple text file and just prepend the lines of the new file (randlist.txt) followed by a comma.

# the comma before `rand` is important, don't remove it!
$newData = , 'rand'   (Get-Content .\randlist.txt -ReadCount 0)
$csv = Get-Content .\allpeeps.csv -ReadCount 0

& {
    for($i = 0; $i -lt [math]::Max($newData.Count, $csv.Count); $i  ) {
        # `$csv[$i].TrimStart('"')` if you need to remove the leading double-quote
        $newData[$i]   ','   $csv[$i]
    }
} | Set-Content .\newData.csv # Use a test file here before replacing the original Csv