VLOOKUP

[PhilBall]

Hi

I work for a Payroll Bureau and we are about to start receiving electronic file from HMRC to update tax codes. The files will all have a very similar filename. The main way to differentiate is the PAYE reference stored within the file. I have written an AutoIT script that will extract the PAYE reference and change the filename accordingly.

We reference each payroll with a 3 digit code. I have a list of which PAYE reference relates to which 3 digit code (In Access and/or excel/csv) and would rather reference the PAYE reference to this data and change the filename using the 3 digit shortcode instead but I am unsure where to start.

I've looked at some of the VLOOKUP postings on here and I do not understand them at all.

Can anyone help, please?

I have attached the script for reference

; Shows the filenames of all files in the current directory.
$search = FileFindFirstFile("Y:\IN\TEST\*.*")  

; Check if the search was successful
If $search = -1 Then
    MsgBox(0, "Error", "No files/directories matched the search pattern")
    Exit
EndIf

While 1
    $file1 = FileFindNextFile($search) 
    If @error Then ExitLoop
    $filename = StringLeft($file1,4)

if $filename = "HMRC" Then
    ; Read in lines of text until the EOF is reached
    $file = FileOpen("Y:\IN\TEST\" & $file1, 0)

    $line = FileReadLine($file,4)
    $payeref1 = StringMid($line, 11, 10)
    $payeref =StringStripWS($payeref1 , 2)
    FileClose($file)
    FileMove("Y:\IN\TEST\" & $file1, "Y:\IN\TEST\"& $payeref &"-HMRC-EDI-DATA.gff",9)
    
    EndIf

WEnd

; Close the search handle
FileClose($search)

The string I wish to compare to the database/csv is $payeref

RENAME INCOMING FILE WITH PAYE REF.au3

Any help or guidance would be very much appreciated

Thanks, Phil

[PsaltyDS]

Since you reference VLOOKUP, do you have Excel available? If so, the fastest way may be to load the csv file into Excel with the _Excel* functions, then either actually run a VLOOKUP query in the script, or read the whole thing into a handy array with _ExcelReadSheetToArray() and do your lookups in memory.

[ResNullius]

SmOke_N did a VLookup function for .csv files here:

http://www.autoitscript.com/forum/index.php?showtopic=78776&st=0&p=568573&#entry56857

[PhilBall]

Since you reference VLOOKUP, do you have Excel available? If so, the fastest way may be to load the csv file into Excel with the _Excel* functions, then either actually run a VLOOKUP query in the script, or read the whole thing into a handy array with _ExcelReadSheetToArray() and do your lookups in memory.

All sounds wonderful. How on earth do I do that? Lookups in memeory after reading the excel sheet to an array, that is?

Thanks

[PsaltyDS]

Not tested, but I believe you can Google up the syntax and do something like $oExcel.Application.VLookup(...).

The other way was with existing Excel.au3 UDF functions. I just meant get all the data from the sheet into an array with _ExcelReadSheetToArray() and then using array references to make use of the data. Find the index cell with _ArraySearch() and just do the math for the offset in the array to read what you wanted from the array.

[PhilBall]

Not tested, but I believe you can Google up the syntax and do something like $oExcel.Application.VLookup(...).

The other way was with existing Excel.au3 UDF functions. I just meant get all the data from the sheet into an array with _ExcelReadSheetToArray() and then using array references to make use of the data. Find the index cell with _ArraySearch() and just do the math for the offset in the array to read what you wanted from the array.

Well I managed to get the _ExcelReadSheetToArray() to work, but not the _ArraySearch().

#Include <Excel.au3>
$sFilePath1 = "Y:\IN\PAYE1.CSV"
$oExcel = _ExcelBookOpen($sFilePath1)

#Include <Array.au3>
$aArray = _ExcelReadSheetToArray($oExcel) ;Using Default Parameters
_ExcelBookClose($oExcel) ; And finally we close out
_ArrayDisplay($aArray, "Array using Default Parameters")


#include <Array.au3>
$sSearch = "PAYE"
If @error Then Exit

$iIndex = _ArraySearch($aArray, $sSearch, 0, 0, 0, 1)

If @error Then
    MsgBox(0, "Not Found", '"' & $sSearch & '" was not found in the array.')
Else
    MsgBox(0, "Found", '"' & $sSearch & '" was found in the array at position ' & $iIndex & ".")
EndIf

The csv file has:

T_Reference,Shortcode_ID,Company Name

PAYD,ZZ1,

PAYE,ZZ2,

Any thoughts?

Thanks, Phil

[PsaltyDS]

Works for me:

#include <Array.au3>

Global $aArray[4][4] = [[3, 3], _
        ["", "T_Reference","Shortcode_ID","Company Name"], _
        ["", "PAYD","ZZ1"], _
        ["", "PAYE","ZZ2"]]
_ArrayDisplay($aArray, "$aArray")

$sSearch = "PAYE"
$iIndex = _ArraySearch($aArray, $sSearch, 2, 0, 0, 1)
If @error Then
    ConsoleWrite('Not Found:  "' & $sSearch & '" was not found in the array.' & @LF)
Else
    ConsoleWrite('Found:  "' & $sSearch & '" was found in the array at position ' & $iIndex & "." & @LF)
    ConsoleWrite('Data:  [' & $iIndex & '][1] = ' & $aArray[$iIndex][1] & '; [' & $iIndex & '][2] = ' & $aArray[$iIndex][2] & @LF)
EndIf

Result:

Found:  "PAYE" was found in the array at position 3.
Data:  [3][1] = PAYE; [3][2] = ZZ2

Where you did _ArrayDisplay(), note that row 0 and column 0 do not contain data. This was done intentionally because Excel coordinates are 1-based, but AutoIt array indexes are 0-based. Not using the 0 indexes allows the indexes and the row/col numbers to match.

Note also that I used $iStart = 2 in the _ArraySearch(). Row 0 is not used, row 1 contains headers, so row 2 is the first row of data to search.

Edited February 5, 2010 by PsaltyDS

[PhilBall]

Very strange, I ran your code and got the following result:

>"C:\Program Files\AutoIt3\SciTE\AutoIt3Wrapper\AutoIt3Wrapper.exe" /run /prod /ErrorStdOut /in "Y:\IN\testfrom web.au3" /autoit3dir "C:\Program Files\AutoIt3" /UserParams    
+>09:37:46 Starting AutoIt3Wrapper v.2.0.0.3    Environment(Language:0409  Keyboard:00000809  OS:WIN_XP/Service Pack 3  CPU:X64 OS:X86)
>Running AU3Check (1.54.14.0)  from:C:\Program Files\AutoIt3
+>09:37:46 AU3Check ended.rc:0
>Running:(3.3.0.0):C:\Program Files\AutoIt3\autoit3.exe "Y:\IN\testfrom web.au3"    
Not Found:  "PAYE" was not found in the array.
+>09:37:48 AutoIT3.exe ended.rc:0
+>09:37:49 AutoIt3Wrapper Finished
>Exit code: 0    Time: 3.566

Do you think there is something wrong with my AutoIT?

[PhilBall]

Very strange, I ran your code and got the following result:

>"C:\Program Files\AutoIt3\SciTE\AutoIt3Wrapper\AutoIt3Wrapper.exe" /run /prod /ErrorStdOut /in "Y:\IN\testfrom web.au3" /autoit3dir "C:\Program Files\AutoIt3" /UserParams    
+>09:37:46 Starting AutoIt3Wrapper v.2.0.0.3    Environment(Language:0409  Keyboard:00000809  OS:WIN_XP/Service Pack 3  CPU:X64 OS:X86)
>Running AU3Check (1.54.14.0)  from:C:\Program Files\AutoIt3
+>09:37:46 AU3Check ended.rc:0
>Running:(3.3.0.0):C:\Program Files\AutoIt3\autoit3.exe "Y:\IN\testfrom web.au3"    
Not Found:  "PAYE" was not found in the array.
+>09:37:48 AutoIT3.exe ended.rc:0
+>09:37:49 AutoIt3Wrapper Finished
>Exit code: 0    Time: 3.566

Do you think there is something wrong with my AutoIT?

I uninstalled and re installed with the latest version and it worked.

Thanks, Phil

[jchd]

Glad to see you have it working.

BTW, you could have considered creating an SQLite database for storing your data. I can see only advantages over other solutions.

[PhilBall]

Not tested, but I believe you can Google up the syntax and do something like $oExcel.Application.VLookup(...).

The other way was with existing Excel.au3 UDF functions. I just meant get all the data from the sheet into an array with _ExcelReadSheetToArray() and then using array references to make use of the data. Find the index cell with _ArraySearch() and just do the math for the offset in the array to read what you wanted from the array.

Thanks very much for your help. As you may tell, I am a little out of my depth.

I have now got the Position of the text I was looking up from the _ArraySearch() Function, but am stumped as to how I can use that to return the text in the same row, the next cell over. I understand the math of it, but cannot find the right function to use to return the text.

Can you help?

Thanks, Phil

[PhilBall]

Glad to see you have it working.

BTW, you could have considered creating an SQLite database for storing your data. I can see only advantages over other solutions.

I would be completely clueless with what to do and where to start or even anything to do with that!!

[jchd]

Can you post an example file together with the type(s) of lookup you need to perform? Grossly masquerade sensitive data, if any.

[PhilBall]

Can you post an example file together with the type(s) of lookup you need to perform? Grossly masquerade sensitive data, if any.

I am hoping to search incoming files from HMRC and find the PAYE ref as per $payeref in the RENAME INCOMING FILE WITH PAYE REF.au3 but instead of renaming the file like that, using the PAYE ref to lookup in the PAYE1.csv and returning the 3 digit code that represents that ref. i.e. if the PAYE ref is PAYD the the filename will become ZZ1-HMRC-EDI-DATA.gff and if it is PAYE then the filename will become ZZ2-HMRC-EDI-DATA.gff.

The HMRC.zip contains the 2 test files received from HMRC.

When we actually use this the csv file will contain over 1000 PAYE ref (all unique) and the corresponding 3 digit shortcode.

I hope that is as clear as mud!!

Thanks, Phil

PAYE1.zip

RENAME INCOMING FILE WITH PAYE REF.au3

OPEN EXCEL.au3

HMRC.zip

[jchd]

I hope that is as clear as mud!!

It really is! Is it a Brit thing, or what? <pun intended>

I'll look closely at what you just submitted, but only after a couple of hours as I've something "cooking" here which will need attention before it get too late.

I just took a global look.

Quick question: are your input files (hence the reference --> code) permanent or semi-permanent, or is it something that you just process on the fly and forget at once?

My understanding: your receive HMRC files from time to time. They contain complex formatted data. Within this data, there is a reference (payref) at line 4 field 11,10 (which you grab and use in your rename script). You attribute a 3 character (not 3 digit) internal code and rename the original file with a name including this code, in order to lacate it later. You want to access the reference --> code table to do so.

Another quick question: why don't you store in a table (SQL or not is not really the question) the reference along with the original filename untouched? Or are your ZZ1, ZZ2 codes used elsewhere?

E.g. a table like that:

Reference | HMRC filename | your code (if you need it)
----------|----------------------|---------------------------
PAYD | HMRC-EDI-DATA~000000 | ZZ1
T11026 | HMRC-EDI-DATA~000001 | ZZ2
...

Is how I depict the context anywhere close to your actual problem?

Edit: add markup to prevent space contraction <g> I give up, this formatting is way too shitty.

Edited February 8, 2010 by jchd

[PhilBall]

It really is! Is it a Brit thing, or what? <pun intended>

I'll look closely at what you just submitted, but only after a couple of hours as I've something "cooking" here which will need attention before it get too late.

I just took a global look.

Quick question: are your input files (hence the reference --> code) permanent or semi-permanent, or is it something that you just process on the fly and forget at once?

My understanding: your receive HMRC files from time to time. They contain complex formatted data. Within this data, there is a reference (payref) at line 4 field 11,10 (which you grab and use in your rename script). You attribute a 3 character (not 3 digit) internal code and rename the original file with a name including this code, in order to lacate it later. You want to access the reference --> code table to do so.

Another quick question: why don't you store in a table (SQL or not is not really the question) the reference along with the original filename untouched? Or are your ZZ1, ZZ2 codes used elsewhere?

E.g. a table like that:

Reference | HMRC filename | your code (if you need it)
----------|----------------------|---------------------------
PAYD | HMRC-EDI-DATA~000000 | ZZ1
T11026 | HMRC-EDI-DATA~000001 | ZZ2
...

Is how I depict the context anywhere close to your actual problem?

Edit: add markup to prevent space contraction <g>

Hi

Thanks

You pretty much have it. This is something that will get quite busy once it goes live and I estimate us receiving 100 of these files each day. It's to do with individual employee's taxes rates, we process about 40,000 peoples salaries each month.

The PAYE ref and 3 digit shortcode are actually stored in an access database if that can be used better than a CSV? The HMRC files will come in with a generic name and the only way to differentiate is to read the PAYE ref in the file. Each one designates a different business/client.

The PAYE reference and 3 digit shortcode are semi permanent in that they do change but not very often. Plus new ones are added all the time.

Cheers, Phil

[jchd]

Ok. You didn't answer: are the 3-char short codes used elsewhere or only to locate files and why can't you use the payref instead (you told me the payref is unique)?

The simpler things are, the less room exist for things to break down.

Also: how many people will be using the table (or base or CSV or whatever) at a given time? Reading? Updating?

[PhilBall]

Ok. You didn't answer: are the 3-char short codes used elsewhere or only to locate files and why can't you use the payref instead (you told me the payref is unique)?

The simpler things are, the less room exist for things to break down.

Also: how many people will be using the table (or base or CSV or whatever) at a given time? Reading? Updating?

Sorry, yes, the 3 digit codes is the unique code we use to identify each company in our payroll software and therefore is used to identify the company throughout our administration and file structure here.

[PhilBall]

Sorry, yes, the 3 digit codes is the unique code we use to identify each company in our payroll software and therefore is used to identify the company throughout our administration and file structure here.

Worked it out for myself:

#Include <Excel.au3>
$sFilePath1 = "Y:\IN\PAYE1.CSV"
$oExcel = _ExcelBookOpen($sFilePath1)

#Include <Array.au3>
$aArray = _ExcelReadSheetToArray($oExcel) ;Using Default Parameters
_ExcelBookClose($oExcel) ; And finally we close out
_ArrayDisplay($aArray, "Array using Default Parameters")



$sSearch = "PAYE"
If @error Then Exit

$iIndex = _ArraySearch($aArray, $sSearch)

If @error Then
    MsgBox(0, "Not Found", '"' & $sSearch & '" was not found in the array.')
Else
    MsgBox(0, "Found", '"' & $sSearch & '" was found in the array at position ' & $iIndex & ".")


    $sEnd = "2"
    $payeref = $aArray[$iIndex][$sEnd]
    msgbox(0, "PAYE Ref", $payeref)

EndIf

[jchd]

I'm back just now. Good to see the lookup works. How are you going to insert the 100 or so new references each day?

And (sorry if I insist):

How many people will be using the table (or Access base or CSV or whatever) at a given time?

Reading it?

Updating it?

Do you assign the internal company 3 char code manually or is there another lookup elsewhere? <-- Just curious