Read a Csv file with powershell and capture corresponding data

Question

Using PowerShell I would like to capture user input, compare the input to data in a comma delimited CSV file and write corresponding data to a variable.

Example:

A user is prompted for a “Store_Number”, they enter "10".
The input, “10” is then compared to the data in the first position or column of the CSV file.
Data, such as “District_Number” in the corresponding position / column is captured and written to a variable.

I have gotten this method to work with an Excel file (.xlsx) but have found it to be terribly slow. Hoping that PowerShell can read a CSV file more efficiently.

Link to an example CSV file here:

Store_Number,Region,District,NO_of_Devices,Go_Live_Date
1,2,230,10,2/21/2013
2,2,230,10,2/25/2013
3,2,260,12,3/8/2013
4,2,230,10,3/4/2013
5,2,260,10,3/4/2013
6,2,260,10,3/11/2013
7,2,230,10,2/25/2013
8,2,230,10,3/4/2013
9,2,260,10,5/1/2013
10,6,630,10,5/23/2013

score 25 · Accepted Answer · answered May 22 '13 at 04:14

25

What you should be looking at is Import-Csv

Once you import the CSV you can use the column header as the variable.

Example CSV:

Name  | Phone Number | Email
Elvis | 867.5309     | Elvis@Geocities.com
Sammy | 555.1234     | SamSosa@Hotmail.com

Now we will import the CSV, and loop through the list to add to an array. We can then compare the value input to the array:

$Name = @()
$Phone = @()

Import-Csv H:\Programs\scripts\SomeText.csv |`
    ForEach-Object {
        $Name += $_.Name
        $Phone += $_."Phone Number"
    }

$inputNumber = Read-Host -Prompt "Phone Number"

if ($Phone -contains $inputNumber)
    {
    Write-Host "Customer Exists!"
    $Where = [array]::IndexOf($Phone, $inputNumber)
    Write-Host "Customer Name: " $Name[$Where]
    }

And here is the output:

I Found Sammy

answered May 22 '13 at 04:14

Austin T French

5,022
1
22
40

1

AthomSfere, thanks for the reply. I copied your code exactly changing only the path to the file and go no output at all. Also what does the "+=" do? I tried googling it but found to reference. – May 23 '13 at 15:40
@squishy79 Is the header row on your CSVs exactly the same too? – Austin T French May 30 '13 at 16:51
@squishy79 the += adds an object to an array. As for the output, try removing the if statement and instead echoing out the entire array, do you get output? – Austin T French Jun 02 '13 at 22:32
Yes, echoing the array produced output. – Jun 04 '13 at 19:25
Can append your question then with your code, and the sample table you are using? – Austin T French Jun 04 '13 at 19:28
it looks like your code will work. I just need to build around it for my specific application. Thanks a ton!!! – Jun 05 '13 at 13:20
@ Austin T French : To overstate the obvious - Its 2 arrays that the indexes match up just like they do in the CVS each column is a array. – Minerbob Jun 02 '18 at 12:41

score 14 · Answer 2 · answered Jun 13 '17 at 07:47

Old topic, but never clearly answered. I've been working on similar as well, and found the solution:

The pipe (|) in this code sample from Austin isn't the delimiter, but to pipe the ForEach-Object, so if you want to use it as delimiter, you need to do this:

Import-Csv H:\Programs\scripts\SomeText.csv -delimiter "|" |`
ForEach-Object {
    $Name += $_.Name
    $Phone += $_."Phone Number"
}

Spent a good 15 minutes on this myself before I understood what was going on. Hope the answer helps the next person reading this avoid the wasted minutes! (Sorry for expanding on your comment Austin)

Good catch! I understand what ez4sheezee was trying to solve in his answer now. — Austin T French, Jun 14 '17 at 16:06

score 8 · Answer 3 · edited Dec 16 '13 at 23:40

So I figured out what is wrong with this statement:

Import-Csv H:\Programs\scripts\SomeText.csv |`

(Original)

Import-Csv H:\Programs\scripts\SomeText.csv -Delimiter "|"

(Proposed, You must use quotations; otherwise, it will not work and ISE will give you an error)

It requires the -Delimiter "|", in order for the variable to be populated with an array of items. Otherwise, Powershell ISE does not display the list of items.

I cannot say that I would recommend the | operator, since it is used to pipe cmdlets into one another.

I still cannot get the if statement to return true and output the values entered via the prompt.

If anyone else can help, it would be great. I still appreciate the post, it has been very helpful!

Wow, old random comment but this looks like a better question than answer. Short answer though, if your file is using the | pipe as your delimiter then it isn't technically a CSV but a PSV (Pipe Separated Values) and you would have to explicitly state the delimiter. Same with if you used tab, colon, semi-colon etc.. — Austin T French, Apr 07 '15 at 13:17

Read a Csv file with powershell and capture corresponding data

3 Answers3

Linked

Related