Jump to content

Read CSV File column


 Share

Recommended Posts

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.

Link to comment
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 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

  • Moderators

Remo1075,

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

M23

Public_Domain.png.2d871819fcb9957cf44f4514551a2935.png Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind

Open spoiler to see 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

 

Link to comment
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.

Link to comment
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)

Link to comment
Share on other sites

  • Moderators

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

Public_Domain.png.2d871819fcb9957cf44f4514551a2935.png Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind

Open spoiler to see 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

 

Link to comment
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

Link to comment
Share on other sites

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
Link to comment
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 "

:)
 

Link to comment
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
 Share

×
×
  • Create New...