Windows Vista Forums
Vista Forums Home Join Vista Forums Donate Vista Tutorials Tags

Welcome to Vista Forums we are your forum to discuss Windows Vista x64 and x86 systems. Whether you need help or just want to post an idea you have on Vista, this is the forum for you.
Register at Vista forums...the world biggest Windows Vista resource Join Vista Forums Now

Go Back   Vista Forums > Microsoft Technical Newsgroups > PowerShell

Modify CSV, add fields

Closed Thread
 
Thread Tools Display Modes
Old 08-09-2007   #1 (permalink)
BZP
Guest


 

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.

Old 08-09-2007   #2 (permalink)
Shay Levi
Guest


 

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.



Old 08-09-2007   #3 (permalink)
Shay Levi
Guest


 

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.



Old 08-09-2007   #4 (permalink)
BZP
Guest


 

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.

Old 08-09-2007   #5 (permalink)
Flowering Weeds
Guest


 

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!



Old 08-10-2007   #6 (permalink)
Hal Rottenberg
Guest


 

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

Old 08-10-2007   #7 (permalink)
Kiron
Guest


 

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

Closed Thread

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Question on To and CC fields George Vista mail 1 3 Weeks Ago 04:35 PM
Fields to not appear properly in detail view Kelly S Vista mail 0 07-22-2008 10:36 AM
Webforms C#: Validating FormView fields c# junkie .NET General 0 04-15-2008 01:12 PM
Fields of Export Function are to close together Fuente Live Mail 3 12-02-2007 10:28 AM
parsing csv files with fields that may contain commas Frank PowerShell 6 07-12-2007 09:18 PM








Vistax64.com 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 2005-2008

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 47 48 49 50