Vista - splitting csv fields into extra columns delimmited a period charac

Hi,

I am trying to split a column in a csv file that is delimmited by a period
(.) character. Each value will then be moved into new columns. Example: -

Current Data
==========
col1
----
a.b.c
test1.test2.test3
apples.oranges.pears

Output Data
========

col1 col2 col3
---- ---- ----
a b c
test1 test2 test3
apples oranges pears

I am fairly new to powershell so am not sure how to achieve this. I have
only managed to create the required columns for this but not yet populated
them with the necessary values.

Any help into this will be greatly appreciated.

Use the string split() method with the dot sign

$row="a.b.c"
$arr = $row.split(".")
$arr
a
b
c

$arr.length
3

$arr[0]
a

$arr[1]
b

(...)



Shay
http://scriptolog.blogspot.com

Thanks for the reply.

I am still a little unclear on how to actually set the other columns to
contain the correct values.
Splitting a string via the split method does work as should, but with the
current code i would have to update the $row variable for EVERY row (not
sure how to pull out the value of the field in a row though), split and add 3
values to an array and load each array value to specified columns (when i
tried to do this i ended up with a column containing the same value for every
row....basically couldnt get it to appear correctly).

Also is there a more efficent way to achive the same functionality?

Any followup responses into this would be helpful.

"Shay Levi" wrote:

Try this

# csv file
a.b.c
test1.test2.test3
apples.oranges.pears


$csv = get-Content c:\scripts\test.csv | foreach {

$obj = new-object psobject;
$cols = $_.split(".");

for($i=0; $i -lt $cols.length; $i++){

add-member -inp $obj NoteProperty "col$i" $cols[$i];
}
$obj;

}

$csv


col0 col1 col2
---- ---- ----
a b c
test1 test2 test3
apples oranges pears




Shay
http://scriptolog.blogspot.com

Faisal wrote:Hi Faisal,

for my own learning purposes I tweaked Shays solution a bit,
to better fit your specs with an existing col1 and numbering col1 to col3.

$csv = import-csv .\test.csv
$csv
|
| col1
| ----
| a.b.c
| test1.test2.test3
| apples.oranges.pears

$csv = $csv | foreach {
$obj = new-object psobject;
$cols = $_.col1.split(".");
for($i=1; $i -lt $cols.length+1; $i++){
add-member -inp $obj NoteProperty "col$i" $cols[$i-1];
}
$obj;
}
$csv | ft -aut
|
| col1 col2 col3
| ---- ---- ----
| a b c
| test1 test2 test3
| apples oranges pears


HTH

--
Greetings
Matthias

Hi thanks for the great help so far.

I have adapted the code provided to work for other column names. I have
come across a new problem although the situation is a little different. The
problem now is that other columns in the csv file are completely overwritten,
even if they were not apart of the column that is split. Example: -

columnone column2
a.b.c col2a
test1.test2.test3 col2b
apples.oranges.pears col2c


The code i currently am using is: -

$csv = import-csv sample.csv;
$csv = $csv | foreach {
$obj = new-object psobject;
$cols = $_.columnname.split(".");

for ($i=1; $i -lt $cols.length+1; $i++){
if ($i -eq 1) {add-member -inp $obj noteproperty "first" $cols[$i-1];}
elseif ($i -eq 2) {add-member -inp $obj noteproperty "second" $cols[$i-1];}
elseif ($i -eq 3) {add-member -inp $obj noteproperty "third" $cols[$i-1];}
}
$obj;
}
$csv

Is there a way to keep the other column from being overwritten and still
have the new columns appear as normal?

Thanks again for all the support!

Import-CSV doesn't have a delimiter parameter, It only accepts comma delimited
files.
To overcome this limitation, follow Desmond Lee trick.
You can read it here http://www.leedesmond.com/weblog/?p=155

1. Get the content of the CSV file
2. Replace current delimiter with a comma
3. Save changes
4. Import file using Import-CSV

$s1 = Get-Content "file.csv"
$s2 = $s1 -replace ".",","
$s2 | Out-File "file.csv" -encoding ASCII
$s3 = Import-Csv "file.csv"

Now you have a full working object you can play with.



Shay
http://scriptolog.blogspot.com

# Sample data
@'
field0, field1, field2
sphere,a.b.c,circle
cube,test1.test2.test3,square
pyramid,apples.oranges.pears,triangle
'@ | out-file data.csv -encoding ASCII

# data as three-column table
import-csv data.csv | format-table -auto

# send data through the pipe
# first field remains intact
import-csv data.csv | select field0,
# split second field
# create new fields for each element
# using calculated properties/fields
@{name = 'field1'; expression = {$_.field1.split('.')[0]}},
@{name = 'field2'; expression = {$_.field1.split('.')[1]}},
@{name = 'field3'; expression = {$_.field1.split('.')[2]}},
# change fith field's header
@{name = 'field4'; expression = {$_.field2}} |
# export the new data to a file
export-csv newData.csv -encoding ASCII -noType

# data as five-column table
import-csv newData.csv | format-table -auto

# assign newdata to a variable
$csv = import-csv newData.csv

###

For an explanation on calculated property/field:

(get-help select-object).description
((get-help select-object).examples).example[3]

--
Kiron

Perhaps Microsoft's Log Parser 2.2

@"
sphere,a.b.c,circle
cube,1.2.3,square
pyramid,apples.orange.pears,triangle
"@ | LogParser.exe "SELECT Field1, `
EXTRACT_TOKEN(Field2,0,'.') AS NewField2, `
EXTRACT_TOKEN(Field2,1,'.') AS [New Next Field Two], `
EXTRACT_TOKEN(Field2,2,'.') AS NewLastField2, `
Field3 FROM STDIN " -i:csv -headerRowff -statsff

Just another way!