Read CSV File column

[Remo1075]

Hi,

I am looking for help on reading a CSV file, specifically I need to read one column of data under the heading Personnel Number.

I thought about using the Excel.au3 set of UDF's but the script would run on a server and I wont have exel installed on it.

There will be new numbers added to this column daily, my goal is to search only this column for new additions and then be able to call each new number against a variable.

eg, 2 new numbers found.

$var1

$var2

These numbers represent a personal ID that exists in active directory, the account would then have xyz performed on it.

poll AD for $var1 then perform xyz on the account

repeat for $var2.

Thanks for any help.

[water]

The following >function (see post #3) reads a CSV file into a 2D array.

My AD UDF then will help with the AD stuff.

[Melba23]

Remo1075,

If you use the Beta version then _FileReadToArray will read the file directly into a 2D array.

M23

[czardas]

If your csv contains fields enclosed in double quotes you could try >CSVSplit.

>Example

[Zedna]

If you need to read only 1 column then I think that the fastest way will be using StringRegExp() which can return 1D array with desirred column.

[mikell]

I agree 

Something like this should work

#Include <Array.au3>

$txt = FileRead("1.csv")
$s = ";"   ; separator
$col = 3   ; column to get

$res = StringRegExp($txt, '(?m)^(?:[^' & $s & ']' & $s & '){' & $col-1 & '}([^' & $s & '])', 3)
 _ArrayDisplay($res)

Edited October 29, 2014 by mikell

[Remo1075]

Thanks to all for the quick replies. 

I thought that I might have to use an array, I have not used them before. I tried using the code from mikell, but am struggling to make sense of the regex.

I only get a returned result when using $col = 1 and then I don't get back what is actually in column 1.

The results I need are all in column 7 and are only numbers, except for the top cell which is the heading.

[mikell]

That's probably because this regex is a sort of 'generic' try, it's difficult to be more precise as I totally ignore how the lines of the csv are build and their content

Could you post 2 sample lines of this csv ?

[Remo1075]

Sure, here it is. I had to rename it to test.txt from test.csv as it doesn't allow me to upload csv files.

I am moving the regex around, I need column G.

What I have so far, but is also returns values in other columns.

$res = StringRegExp($txt, 'd{5}', 3)

test.txt

[mikell]

OK, the delimiter is a comma so this simplified one should work

#Include <Array.au3>

$txt = FileRead("test.txt")
$col = 7   ; column to get

$res = StringRegExp($txt, '(?m)^(?:.*?,){' & $col-1 & '}([^\r\n,]*)', 3)
 _ArrayDisplay($res)

Edit: typo

Edited October 30, 2014 by mikell

[AndyG]

#include <Array.au3>

$txt = FileRead("1.csv")
ConsoleWrite('@@ Debug(' & @ScriptLineNumber & ') : $txt = ' & $txt & @CRLF & '>Error code: ' & @error & @CRLF) ;### Debug Console
$s = ","                               ; separator
$column = "G"

$col = Asc(StringUpper($column)) - 64  ; column to get
$pattern = '(?m)^(?:[^' & $s & ']*' & $s & '){' & $col - 1 & '}([^' & $s & ']*)'

$res = StringRegExp($txt, $pattern, 3)
_ArrayDisplay($res)

[Melba23]

My turn!

#include <Array.au3>
#include <File.au3>

Global $aArray

_FileReadToArray("test.txt", $aArray, $FRTA_NOCOUNT, ",")
$aPersonnelNumber = _ArrayExtract($aArray, 1, Default, 6, 6)
_ArrayDisplay($aPersonnelNumber, "", Default, 8

M23

[mikell]

AndyG,

Doesn't work for the last col

#include <Array.au3>

$txt = FileRead("test.txt")
ConsoleWrite('@@ Debug(' & @ScriptLineNumber & ') : $txt = ' & $txt & @CRLF & '>Error code: ' & @error & @CRLF) ;### Debug Console
$s = ","                               ; separator
$column = "T"  ; 20 last col

$col = Asc(StringUpper($column)) - 64  ; column to get
$pattern = '(?m)^(?:.*?' & $s & '){' & $col-1 & '}([^\r\n' & $s & ']*)'

$res = StringRegExp($txt, $pattern, 3)
_ArrayDisplay($res)

Melba,

Heavyyyy script 

BTW on this way using StringSplit was enough

[Remo1075]

I have managed to simplify it by just deleting the original test.csv file and have a new one generated each day with new results in it.

So I don't need a 2nd csv file.

I have substituted the numbers in the column for ip addresses to make it easier to understand what I'm trying to do.

Only problem I have now is it tries to ping Personnel. because it finds it in the array.

Can I exclude this some how?

#Include <Array.au3>

Global $txt = FileRead(@ScriptDir & 'test.csv')
$col = 7   ; column to get

$array = StringRegExp($txt, '(?m)^(?:.*?,){' & $col-1 & '}([^rn,]*)', 3)
 ;_ArrayDisplay($array)

For $i = 0 To UBound($array) - 1
    ;MsgBox(0, "Output", $array[$i] & @CR)

    RunWait(@ComSpec & " /k " & "ping " & $array[$i])
Next

Edited October 30, 2014 by Remo1075

[mikell]

For $i = 1 To UBound($array) - 1

Edit

Don't forget to put some error checking before the loop

If IsArray($array) AND UBound($array)>1 Then ...

Edited October 30, 2014 by mikell

[Remo1075]

Thanks, for the tips.

 I don't understand the regex below can you explain it for me at all.

(?m)^(?:.*?,)
{' & $col-1 & '} ;This is clear, look in column 7
([^\r\n,]*)

[AndyG]

 

 I don't understand the regex below can you explain it for me at all.

http://regex101.com

After inserting the pattern into the input-field, the explanation shows you instantly, how the RegEx works...

Understanding this is an other question

[mikell]

Remo1075,

(?m)^(?:.*?,){6}([^rn,]*)   =>  gets col 7

(?m)^  =  the multiline mode makes ^ match at start of each line
(?:.*?,){6}  =  0 or more characters and a comma, 6 times, non-capturing group
([^rn,]*)  =  0 or more characters which are not a newline or a comma, capturing group

So in non-klingon language the expression means :
"From the beginning of each line, find a sequence with 0 or more characters and a comma 6 times but don't keep them, then find a sequence with 0 or more characters which are not a newline or a comma and get it "

 

[Remo1075]

Thanks a lot, you've been a great help.