 |
|
|
powershell import-csv and multidimensional arrays
Last Post 29 Jun 2010 01:35 PM by cameronove. 7 Replies. |
Sort:
|
|
 Prev Next |
You are not authorized to post a reply. |
|
| Author |
Messages |
 |
jlo
New Member
Posts:5
 |
| 29 Jun 2010 09:34 AM |
|
<!-- google_ad_section_end -->
<!-- google_ad_section_start -->im trying to import multidimensional arrays using import-csv but its not working properly...
---csv contents
psn,TYPE,asset,23-Jun,24-Jun,25-Jun
jlo,"(cd,album)",nin,TRUE,TRUE,TRUE
abc,tape,"(metallica, rage)",TRUE,TRUE,FALSE
aaa,cd,rhcp,TRUE,FALSE,TRUE
"(jlo, aaa)",concert,santana,FALSE,TRUE,FALSE
---
$a= @()
$a+=Import-Csvc:\temp\test.csv
$a[0][0] #doesnt work
$a[0,1] # lists entirety of lines 1 and 2 and not row 1 column 1 = (cd,album)
$a[0].Type #shows both values
$a | Where-Object {$_.psn -match"jlo"-and$_.type -match"cd"} #filters accuratly but not splitting TYPE field
$a | Where-Object {$_.psn -match"jlo"-and$_.type -match"cd"} | select psn, type |foreach {$_.type -split (", ")} | % {$_.trim("()")} # splits but doesnt pass the rest of the info down the pipelines.
---
#goal 1: split multidimensional array by individual internal objects and output along with rest of the line.
#jlo, cd, nin, true...
#jlo, album, nin, true...
#abc, tape, metallica, true,true...
#abc, tape, rage, true, true
---
#goal 2: - split all and search accordingly
$date=Get-Date-Formatdd-MMM"6/24/2010"
$a | Where-Object {$_.type -match"concert"-and$_."$date"-match"true"} | select psn, type, asset#filters but doesnt split
#psn TYPE asset
#--- ---- -----
#jlo concert santana
#aaa concert santana
---
Thank you for the help!
|
|
|
|
|
PoSherLife
Basic Member
Posts:364
|
| 29 Jun 2010 09:42 AM |
|
You can't trim () because it does not exist. We can use RegEx and replace ( and ), then split the data.
$a | ? {$_.psn -match "jlo" -and $_.type -match "cd"} | select psn, type | % {($_.type -replace "`(|`)" "") -split (", ")} |
|
| When at first you don't succeed Step-Into
http://theposherlife.blogspot.com
http://www.jandctravels.com |
|
|
jlo
New Member
Posts:5
|
| 29 Jun 2010 09:53 AM |
|
Thank you for the amazingly quick response.
the line didn't work. i believe it may have been missing the comma between the quote pairs.
$a | ? {$_.psn -match "jlo" -and $_.type -match "cd"} | select psn, type | % {($_.type -replace "`(|`)", "") -split (", ")}
this answer only resulted in:
(cd,album)
which is still not split up or sent with the other info.
Thanks again.
|
|
|
|
|
PoSherLife
Basic Member
Posts:364
|
| 29 Jun 2010 10:14 AM |
|
I used the wrong escape char. RegEx users \ instead of ` as PoSh does.
$a | ? {$_.psn -match "jlo" -and $_.type -match "cd"} | select psn, type | % {($_.type -replace "\(|\)", "") -split (", ")} |
|
| When at first you don't succeed Step-Into
http://theposherlife.blogspot.com
http://www.jandctravels.com |
|
|
jlo
New Member
Posts:5
|
| 29 Jun 2010 12:22 PM |
|
ok. that did remove it properly but is doesn't pass the other info down the pipeline to match the goal results. this result is the same as the last command i have just above the goal 1 line
$a | Where-Object {$_.psn -match"jlo"-and$_.type -match"cd"} | select psn, type |foreach {$_.type -split (", ")} | % {$_.trim("()")}
actually mine splits the 2 different words to 2 different lines but again i don't think it passes other info down the pipeline to put along with these results.
|
|
|
|
|
cameronove
Basic Member
Posts:332
|
| 29 Jun 2010 12:26 PM |
|
Here is code to produce a csv file that meets goal 1.
$a = Import-Csv H:\scripts\PowerShell\sandbox\multicsv\test.csv
$accum = @("psn,type,asset,23-Jun,24-Jun,25-Jun")
foreach($line in $a){
$psn = ($line.psn -replace "\(|\)","") -split ",\s*"
$type = ($line.type -replace "\(|\)","") -split ",\s*"
$asset = ($line.asset -replace "\(|\)","") -split ",\s*"
$psncount = $psn.count
$typecount = $type.count
$assetcount = $asset.count
for($i=0;$i -lt $psncount;$i++){
for($j=0;$j -lt $typecount;$j++){
for($k=0;$k -lt $assetcount;$k++){
$accum += @("$($psn[$i]),$($type[$j]),$($asset[$k]),$($line.'23-Jun'),$($line.'24-Jun'),$($line.'25-Jun')")
}
}
}
}
$accum >test2.csv
This is the output (minus the colorization):
___________________________________________________________________________________________
PS H:\scripts\PowerShell\sandbox\multicsv> import-csv .\test2.csv | ft
psn type asset 23-Jun 24-Jun 25-Jun
--- ---- ----- ------ ------ ------
jlo cd nin TRUE TRUE TRUE
jlo album nin TRUE TRUE TRUE
abc tape metallica TRUE TRUE FALSE
abc tape rage TRUE TRUE FALSE
aaa cd rhcp TRUE FALSE TRUE
jlo concert santana FALSE TRUE FALSE
aaa concert santana FALSE TRUE FALSE
|
|
|
|
|
jlo
New Member
Posts:5
|
| 29 Jun 2010 01:06 PM |
|
i think that does work for goal 1.
for clarity, the concepts is:
add the regex replacement to any line that may have multiple entries,
do a "for" nesting loop of all entries that also may have been split,
throw all results back into a combined array and output results.
does that sound about right?
this will in reality be for large csv files that will all be different and somewhat random so i am trying to buildin some modularity.
|
|
|
|
|
cameronove
Basic Member
Posts:332
|
| 29 Jun 2010 01:35 PM |
|
Well yes that is the basic idea.
However, I would actually do this different if it really needs to be going over multiple large files with no distinct pattern. For example instead of using a static number of nested 'for' loops I would use a recursive function that called it self for every field that had multiple values. I'd pull the headers out and place them in an array the I could iterate through so that it could handle dynamically different csv files. I'd also collect everything in an object array instead of creating a static string array. It'd be more code (and more difficult to get my little brain around) but I could see it working then on any file you put through it...
|
|
|
|
|
| You are not authorized to post a reply. |
|
Active Forums 4.3
|
|
 |