How to set as variable csv column using powershell?

Question

I have csv file like this

ID Name
4  James
6  John
1  Cathy

I want to save those file as .cmd with this format

SET NUMBER1=4
SET NUMBER2=6
SET NUMBER3=1

The total of ID in the csv file is not always 3. If the ID more than 3, it means my cmd file be like this

SET NUMBER1=4
SET NUMBER2=6
SET NUMBER3=1
SET NUMBERN=N

Anyone can help please. I really new in powershell, really need help and advice please. Thanks

$ID = Import-Csv .\Data.csv | Select-Object -ExpandProperty ID
$ID.Count 

ForEach ( $id in $ID ) {

}

I am stuck here

this >>> 1ForEach ( $id in $ID )` <<< won't work since the current item $VarName is the same as the collection $VarName. [*grin*] you need _different names in a foreach loop. ///// however, that is not what i asked ... what are you trying to achieve with this? what is the goal? — Lee_Dailey
– Lee_Dailey, Commented Aug 14, 2019 at 12:32
The goal is I can creat a cmd file contain of this ‘SET NUMBER1=4 SET NUMBER2=6 SET NUMBER3=1 SET NUMBERN=N’ its like @f6a4 answer below — Cheries
– Cheries, Commented Aug 14, 2019 at 13:13
i am well aware of what ... it's the WHY that i am oh-so-curious about. [grin] there are ways to set environment vars from inside PoSh ... but i am so very, very curious as to why are you setting those env vars? — Lee_Dailey
– Lee_Dailey, Commented Aug 15, 2019 at 0:39

AdminOfThings · Accepted Answer · 2019-08-14 12:49:29Z

3

An alternative approach is below if your headers are always present in the file. It doesn't matter what the delimiter is as long as it isn't a number. Your delimited data in the sample is not consistent. Otherwise, Import-Csv would be a safer option.

$fileData = Get-Content file.csv
$output = for ($i = 1; $i -lt $fileData.count; $i++) {
        "SET NUMBER{0}={1}" -f $i,($fileData[$i] -replace "(?<=^\d+).*")
      }
$output | Out-File file.cmd

Explanation:

The format operator (-f) is used to help construct the output strings. The ID numbers are selected using regex by replacing everything that comes after the beginning digits on each line.

answered Aug 14, 2019 at 12:49

AdminOfThings

25.2k4 gold badges22 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Cheries Over a year ago

Hi @AdminOfThings, Thank you for your help. I tried it but the cmd file output be like this : SET NUMBER1="4", James SET NUMBER2="6",John SET NUMBER3="1",Cathy My expectation only like this SET NUMBER1=4 SET NUMBER2=6 SET NUMBER3=1

f6a4 · Accepted Answer · 2019-08-14 12:16:47Z

2

Try this:

# set current directory to script directory
Set-Location $PSScriptRoot

# import csv-file, delimiter = space
$content = Import-Csv 'test.csv' -Delimiter ' '
$output  = ''

# create output lines
for( $i = 1; $i -le $content.Count; $i++ ) {
    $output += 'SET NUMBER' + $i.ToString() + '=' + $content[$i-1].ID.ToString() + [environment]::NewLine
}

# output to file
$output | Out-File 'result.bat' -Force

answered Aug 14, 2019 at 12:16

f6a4

1,8021 gold badge11 silver badges16 bronze badges

2 Comments

Cheries Over a year ago

it return an error

You cannot call a method on a null-valued expression. At D:\folder\script.ps1:48 char:5 +     $output += 'SET NUMBER' + $i.ToString() + '=' + $content[$i-1].ID ... +     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~     + CategoryInfo          : InvalidOperation: (:) [], RuntimeException     + FullyQualifiedErrorId : InvokeMethodOnNull

f6a4 Over a year ago

remove -Delimiter ' '

Collectives™ on Stack Overflow

How to set as variable csv column using powershell?

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related