Windows Vista Forums
Vista Forums Home Join Vista Forums Windows 7 Forum Vista Tutorials Tags
Welcome to Windows Vista Forums. Our forum is dedicated to helping you find solutions with any problems, errors or issues you are experiencing with Windows Vista. The Vista forum also covers news and updates and has an extensive Windows Vista tutorial section that covers a wide range of tips and tricks.

Go Back   Vista Forums > Misc Newsgroups > PowerShell

Vista - Modify CSV, add fields

Reply
 
Old 08-09-2007   #1 (permalink)
BZP


 
 

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.


My System SpecsSystem Spec
Old 08-09-2007   #2 (permalink)
Shay Levi


 
 

Re: Modify CSV, add fields

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.



My System SpecsSystem Spec
Old 08-09-2007   #3 (permalink)
Shay Levi


 
 

Re: Modify CSV, add fields

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.



My System SpecsSystem Spec
Old 08-09-2007   #4 (permalink)
BZP


 
 

Re: Modify CSV, add fields

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.

My System SpecsSystem Spec
Old 08-09-2007   #5 (permalink)
Flowering Weeds


 
 

Re: Modify CSV, add fields


"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!



My System SpecsSystem Spec
Old 08-10-2007   #6 (permalink)
Hal Rottenberg


 
 

Re: Modify CSV, add fields

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

My System SpecsSystem Spec
Old 08-10-2007   #7 (permalink)
Kiron


 
 

Re: Modify CSV, add fields

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

My System SpecsSystem Spec
Reply

Thread Tools


Similar Threads
Thread Forum
SQL Compare Date Fields using Where VB Script
My doucments display fields General Discussion
Cursor jumps fields --Tab Key? Help! General Discussion
Question on To and CC fields Vista mail
parsing csv files with fields that may contain commas PowerShell


Vista Forums is an independent web site and has not been authorized,
sponsored, or otherwise approved by Microsoft Corporation.
"Windows Vista", the Start Orb, and related materials are trademarks of Microsoft Corp.
© Designer Media Ltd

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46