Home > Net >  Using Import-csv and store multiple values of a column as a variable
Using Import-csv and store multiple values of a column as a variable

Time:11-03

I have a csv file that has a column named 'art':

sequence art last  first  address city st     zip
-------- --- ----  -----  ------- ---- --     ---
1        3S  Doe   John   123     Any  Street AnyTown
6        3S  Doe   John   128     Any  Street AnyTown
2        OG  Dow   Jane   124     Any  Street AnyTown
7        OG  Dow   Jane   129     Any  Street AnyTown
3        OGF Cool  Joe    125     Any  Street AnyTown
8        OGF Cool  Joe    130     Any  Street AnyTown
4        SLV Cool  Carol  126     Any  Street AnyTown
9        SLV Cool  Carol  131     Any  Street AnyTown
5        SMP Bravo Johnny 127     Any  Street AnyTown
10       SMP Bravo Johnny 132     Any  Street AnyTown

I am looking for a way to store the values of the 'art' column into variables. Bear with me as I am still learning PowerShell. This is the code I have so far:

If (Test-Path D:\mhWork\_Auto\_Print\c1478_tpl_snap\data\Art_cProofs.csn){
$source=Get-ChildItem "D:\mhWork\_Auto\_Print\c1478_tpl_snap\data\Art_cProofs.csn" | select  -Last 1
$artTag = 'D:\mhWork\_Auto\_Print\c1478_tpl_snap\data\Art_cProofs.csn'
$artTag1 = Import-Csv $artTag | Select-Object -ExpandProperty art -First 1
$artTag2 = Import-Csv $artTag | Select-Object -ExpandProperty art -Second 2
$artTag3 = Import-Csv $artTag | Select-Object -ExpandProperty art -Third 3
$artTag4 = Import-Csv $artTag | Select-Object -ExpandProperty art -Fourth 4
$artTag5 = Import-Csv $artTag | Select-Object -ExpandProperty art -Fifth 5
$JobID = "TPL_"   ($aJobNum)

If (Test-Path cProofs0001.pdf){
Rename-Item cProofs0001.pdf "$($JobID)_$($artTag1)_cProofs.pdf"}
If (Test-Path cProofs0002.pdf){
Rename-Item cProofs0002.pdf "$($JobID)_$($artTag2)_cProofs.pdf"}
If (Test-Path cProofs0003.pdf){
Rename-Item cProofs0003.pdf "$($JobID)_$($artTag3)_cProofs.pdf"}
If (Test-Path cProofs0004.pdf){
Rename-Item cProofs0004.pdf "$($JobID)_$($artTag4)_cProofs.pdf"}
If (Test-Path cProofs0005.pdf){
Rename-Item cProofs0005.pdf "$($JobID)_$($artTag5)_cProofs.pdf"}
}

As you can see by the 'art' column, there are 5 different values. The code works fine until I get past $artTag1. The desired result would rename the below files.

cProofs0001.pdf - to - 123456_TPL_3S_cProofs.pdf
cProofs0002.pdf - to - 123456_TPL_OG_cProofs.pdf
cProofs0003.pdf - to - 123456_TPL_OGF_cProofs.pdf
cProofs0004.pdf - to - 123456_TPL_SLV_cProofs.pdf
cProofs0005.pdf - to - 123456_TPL_SMP_cProofs.pdf

I'm thinking that I might need to use get-content or a for-each loop.

CodePudding user response:

# load the entire csv into a variable
$csvFilepath = 'D:\mhWork\_Auto\_Print\c1478_tpl_snap\data\Art_cProofs.csn'
$csvContent = Import-Csv -Path $csvFilepath

# get the values of the art column like you did with -ExpandProperty
$artValues = $csvContent | Select-Object -ExpandProperty art

# de-duplicate the list
$artTags = $artValues | Group-Object | Select-Object -ExpandProperty Name

# use array indexing to reference values in list
$JobID = "TPL_"   ($aJobNum) # unclear where $aJobNum comes from

If (Test-Path cProofs0001.pdf){
Rename-Item cProofs0001.pdf "$($JobID)_$($artTags[0])_cProofs.pdf"}
If (Test-Path cProofs0002.pdf){
Rename-Item cProofs0002.pdf "$($JobID)_$($artTags[1])_cProofs.pdf"}
If (Test-Path cProofs0003.pdf){
Rename-Item cProofs0003.pdf "$($JobID)_$($artTags[2])_cProofs.pdf"}
If (Test-Path cProofs0004.pdf){
Rename-Item cProofs0004.pdf "$($JobID)_$($artTags[3])_cProofs.pdf"}
If (Test-Path cProofs0005.pdf){
Rename-Item cProofs0005.pdf "$($JobID)_$($artTags[4])_cProofs.pdf"}
}

It's not clear to me what is the relationship between cProofs0001.pdf & etc. and the values in the art column. The only thing that determines that cProofs0001.pdf is associated to 3S is the order of the rows in the .csv file. Will that order always be the same?

  • Related