Sign in to follow this  
Followers 0
Remo1075

Read CSV File column

19 posts in this topic

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.

Share this post


Link to post
Share on other sites



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

My AD UDF then will help with the AD stuff.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Remo1075,

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

M23


Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind._______My UDFs:

Spoiler

ArrayMultiColSort ---- Sort arrays on multiple columns
ChooseFileFolder ---- Single and multiple selections from specified path treeview listing
Date_Time_Convert -- Easily convert date/time formats, including the language used
ExtMsgBox --------- A highly customisable replacement for MsgBox
GUIExtender -------- Extend and retract multiple sections within a GUI
GUIFrame ---------- Subdivide GUIs into many adjustable frames
GUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView items
GUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeView
Marquee ----------- Scrolling tickertape GUIs
NoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxes
Notify ------------- Small notifications on the edge of the display
Scrollbars ----------Automatically sized scrollbars with a single command
StringSize ---------- Automatically size controls to fit text
Toast -------------- Small GUIs which pop out of the notification area

 

Share this post


Link to post
Share on other sites

#6 ·  Posted (edited)

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 by mikell

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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 ?

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

#10 ·  Posted (edited)

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 by mikell

Share this post


Link to post
Share on other sites

#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)

Share this post


Link to post
Share on other sites

My turn! :D

#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

Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind._______My UDFs:

Spoiler

ArrayMultiColSort ---- Sort arrays on multiple columns
ChooseFileFolder ---- Single and multiple selections from specified path treeview listing
Date_Time_Convert -- Easily convert date/time formats, including the language used
ExtMsgBox --------- A highly customisable replacement for MsgBox
GUIExtender -------- Extend and retract multiple sections within a GUI
GUIFrame ---------- Subdivide GUIs into many adjustable frames
GUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView items
GUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeView
Marquee ----------- Scrolling tickertape GUIs
NoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxes
Notify ------------- Small notifications on the edge of the display
Scrollbars ----------Automatically sized scrollbars with a single command
StringSize ---------- Automatically size controls to fit text
Toast -------------- Small GUIs which pop out of the notification area

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

#14 ·  Posted (edited)

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 by Remo1075

Share this post


Link to post
Share on other sites

#15 ·  Posted (edited)

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 by mikell

Share this post


Link to post
Share on other sites

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,]*)

Share this post


Link to post
Share on other sites

 

 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 o:)

Share this post


Link to post
Share on other sites

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 "

:)
 

1 person likes this

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0