Modify CSV, add fields

Hello,

I start with PS, and ... hum, difficult for me

I have a CSV file, like this :

Field1;Field2;Field3;Field4
John;PATTY;555-12345;NY
Homer;MAC CULLY;555-32147;NH
Selma;BOUVIER;555-12378:KS

And i want to parse and modify it, this is the target :

Field0;Field1;Field2;Field3;Field4;Field5
John PATTY;John;PATTY;555-12345;NY;JP
Homer MAC CULLY;Homer;MAC CULLY;555-32147;NH;HM
Selma BOUVIER;Selma;BOUVIER;555-12378;KS;SB

So, add two fields which are given by other fields in the CSV

Field0 = Field1 + Field2

Field5 = Initials of Field0

Shall i use import csv ? Or can i use a foreach command piped with a
type MyFile.csv ?

Thanks for you help.

--
P.A.

Try this:


$csv = import-csv .\test.csv
$csv
$csv | add-Member -memberType noteProperty -name Field6 -value "" -force;
$csv | gm
# you should see a new note property names field6

$csv | % {$_.Field6 = $_.Field1+$_.Field2}
$csv


Shay
http://scriptolog.blogspot.com



> Hello,
>
> I start with PS, and ... hum, difficult for me
>
> I have a CSV file, like this :
>
> Field1;Field2;Field3;Field4
> John;PATTY;555-12345;NY
> Homer;MAC CULLY;555-32147;NH
> Selma;BOUVIER;555-12378:KS
> And i want to parse and modify it, this is the target :
>
> Field0;Field1;Field2;Field3;Field4;Field5
> John PATTY;John;PATTY;555-12345;NY;JP
> Homer MAC CULLY;Homer;MAC CULLY;555-32147;NH;HM
> Selma BOUVIER;Selma;BOUVIER;555-12378;KS;SB
> So, add two fields which are given by other fields in the CSV
>
> Field0 = Field1 + Field2
>
> Field5 = Initials of Field0
>
> Shall i use import csv ? Or can i use a foreach command piped with a
> type MyFile.csv ?
>
> Thanks for you help.
>
> --
> P.A.

Note that Import-Csv imports comma-separated value items, so replace
";" with a comma.


Shay
http://scriptolog.blogspot.com



> Try this:
>
> $csv = import-csv .\test.csv
> $csv
> $csv | add-Member -memberType noteProperty -name Field6 -value ""
> -force;
> $csv | gm
> # you should see a new note property names field6
> $csv | % {$_.Field6 = $_.Field1+$_.Field2}
> $csv
> Shay
> http://scriptolog.blogspot.com
>> Hello,
>>
>> I start with PS, and ... hum, difficult for me
>>
>> I have a CSV file, like this :
>>
>> Field1;Field2;Field3;Field4
>> John;PATTY;555-12345;NY
>> Homer;MAC CULLY;555-32147;NH
>> Selma;BOUVIER;555-12378:KS
>> And i want to parse and modify it, this is the target :
>> Field0;Field1;Field2;Field3;Field4;Field5
>> John PATTY;John;PATTY;555-12345;NY;JP
>> Homer MAC CULLY;Homer;MAC CULLY;555-32147;NH;HM
>> Selma BOUVIER;Selma;BOUVIER;555-12378;KS;SB
>> So, add two fields which are given by other fields in the CSV
>> Field0 = Field1 + Field2
>>
>> Field5 = Initials of Field0
>>
>> Shall i use import csv ? Or can i use a foreach command piped with a
>> type MyFile.csv ?
>>
>> Thanks for you help.
>>
>> --
>> P.A.

On 9 août, 12:38, Shay Levi <n...@addre.ss> wrote:
> Try this:
>
> $csv = import-csv .\test.csv
> $csv
> $csv | add-Member -memberType noteProperty -name Field6 -value "" -force;
> $csv | gm
> # you should see a new note property names field6
>
> $csv | % {$_.Field6 = $_.Field1+$_.Field2}
> $csv
>
> Shayhttp://scriptolog.blogspot.com
>
>
>
> > Hello,
>
> > I start with PS, and ... hum, difficult for me
>
> > I have a CSV file, like this :
>
> > Field1;Field2;Field3;Field4
> > John;PATTY;555-12345;NY
> > Homer;MAC CULLY;555-32147;NH
> > Selma;BOUVIER;555-12378:KS
> > And i want to parse and modify it, this is the target :
>
> > Field0;Field1;Field2;Field3;Field4;Field5
> > John PATTY;John;PATTY;555-12345;NY;JP
> > Homer MAC CULLY;Homer;MAC CULLY;555-32147;NH;HM
> > Selma BOUVIER;Selma;BOUVIER;555-12378;KS;SB
> > So, add two fields which are given by other fields in the CSV
>
> > Field0 = Field1 + Field2
>
> > Field5 = Initials of Field0
>
> > Shall i use import csv ? Or can i use a foreach command piped with a
> > type MyFile.csv ?
>
> > Thanks for you help.
>
> > --
> > P.A.- Masquer le texte des messages précédents -
>
> - Afficher le texte des messages précédents -

Thanks Shay !

--
P.A.

"BZP"

>
> I have a CSV file, like this :
>
> Field1;Field2;Field3;Field4
> John;PATTY;555-12345;NY
> Homer;MAC CULLY;555-32147;NH
> Selma;BOUVIER;555-12378:KS
>
> And i want to parse and modify it, this
> is the target :
>
> Field0;Field1;Field2;Field3;Field4;Field5
> John PATTY;John;PATTY;555-12345;NY;JP
> Homer MAC CULLY;Homer;MAC CULLY;555-32147;NH;HM
> Selma BOUVIER;Selma;BOUVIER;555-12378;KS;SB
>

> So, add two fields which are given by
> other fields in the CSV
>
> Field0 = Field1 + Field2
>
> Field5 = Initials of Field0
>

Well using Microsoft's Log Parser 2.2
(the file parsing tool):

PS> get-content "semiColon.csv"
Field1;Field2;Field3;Field4
John;PATTY;555-12345;NY
Homer;MAC CULLY;555-32147;NH
Selma;BOUVIER;555-12378;KS
PS>

And into the requested new file:

PS> LogParser.exe "SELECT STRCAT(STRCAT(field1,' '),field2) AS
Field0,field1,fie
ld2,field3,field4,STRCAT(SUBSTR(field1,0,1),SUBSTR(field2,0,1)) AS
Field5 INTO '
newSemiColon.csv' FROM
'semiColon.csv'" -i:tsv -iSeparator:";" -headerRown -st
atsff -o:tsv -headersn -oSeparator:";"
PS>

PS> get-content "newSemiColon.csv"
Field0;Field1;Field2;Field3;Field4;Field5
John PATTY;John;PATTY;555-12345;NY;JP
Homer MAC CULLY;Homer;MAC CULLY;555-32147;NH;HM
Selma BOUVIER;Selma;BOUVIER;555-12378;KS;SB
PS>

And for a real csv file:

PS> LogParser.exe "SELECT STRCAT(STRCAT(field1,' '),field2) AS
Field0,field1,fie
ld2,field3,field4,STRCAT(SUBSTR(field1,0,1),SUBSTR(field2,0,1)) AS
Field5 INTO '
realNewSemiColon.csv' FROM
'semiColon.csv'" -i:tsv -iSeparator:";" -headerRown
-statsff -o:csv -headersn
PS>

PS> get-content "realNewSemiColon.csv"
Field0,Field1,Field2,Field3,Field4,Field5
John PATTY,John,PATTY,555-12345,NY,JP
Homer MAC CULLY,Homer,MAC CULLY,555-32147,NH,HM
Selma BOUVIER,Selma,BOUVIER,555-12378,KS,SB
PS>

Just another way!

On Aug 9, 6:56 am, Shay Levi <n...@addre.ss> wrote:
> Note that Import-Csv imports comma-separated value items, so replace
> ";" with a comma.

1# $a = @"
>> John;Smith;555-1212
>> Mike;Smith;555;1212
>> "@
>>
2# $a
John;Smith;555-1212
Mike;Smith;555;1212
3# $b = $a | foreach-object { $_ -replace(";",",") }
4# $b
John,Smith,555-1212
Mike,Smith,555,1212

Sample data:

${c:data.csv}
Field1;Field2;Field3;Field4
John;PATTY;555-12345;NY
Homer;MAC CULLY;555-32147;NH
Selma;BOUVIER;555-12378;KS

Modifying it:

${c:data.csv} = ${c:data.csv} -replace ';', ','
$csv = import-csv data.csv
$csv | Add-Member NoteProperty Field0 ''
$csv | Add-Member NoteProperty Field5 ''
$csv | % {$_.field0 = "$($_.field1) $($_.field2)"; $_.field5 =
"$($_.field1[0])$($_.field2[0])"}
$csv | select (0..5 | % {"field$_"}) | export-csv data.csv -NoType


Modified:

${c:data.csv}
Field0,Field1,Field2,Field3,Field4,Field5
"John PATTY",John,PATTY,555-12345,NY,JP
"Homer MAC CULLY",Homer,"MAC CULLY",555-32147,NH,HM
"Selma BOUVIER",Selma,BOUVIER,555-12378,KS,SB

--
Kiron