** changed text 0103
Hi
Here a simple example of AHK's powerfull build-in posssibilities to read, compare and select text data, and transform them into fields and records in Excel (when there is a match with criteria).
AHK has perfect tools to analyse, organise and process text in a relative simple way.
Here, I want to build the records and fields from a <space> or <tab> separated txt file - with much 'junk' in it.
Textcontent is analysed to determine the beginning and end of a record,
and to retrieve and process the fields you want in the database.
Fields are appended <tab> separated to varString, till the end of the 'recordfieldrecording' is reached.
It contains some extra coding, to build the outputstring till the last field of the recording is met, because the 'decisionfield' (has the record to be put in the database or not ?) is in the middle of varString in construction.
It is nicer, to create a inputform with several boxes and options, that cover different situations to retrieve the fields and records you want, and process them further.
Of course, this work can be done in Excel VBA also, but that takes a lot more work than done with AHK.
And in AHK the script can easily be adjusted with more powerfull AHK automation instruments (like using your installed administration tools).
f.i. to analyse inputtext from your interactive webpages, and further process it with administration pgms, with the advanced automated using of controls, AHK makes possible.
In this example you have to set every criterion in the code, except the minimum calltimeduration, that's asked for in an inputbox.
**************************
We all like to check our long telephone bills for high (call to mobile)expensives - if you have older children using your telephone, this is often an urgent matter.
I'm using this script for checking my children and evnt. billing them
grtz
Code:
#NoEnv ; Recommended for performance and compatibility with future AutoHotkey releases.
SendMode Input ; Recommended for new scripts due to its superior speed and reliability.
SetWorkingDir %A_ScriptDir% ; Ensures a consistent starting directory.
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
/*
criterium MinTijd < Tijdduur
criterium = criterion
INPUT: read teleponebill.txt file, <tab> or<space> separated.
PROCESS & OUT: identiy fields by properties (that will vary according to your bill format) and match with criterium (minutes calltime) to combine event. in a record with columns in xls.
if match also compare with tel.book to investigate what numbers cost you that much (to make it possible to identify the caller), also xls output
*/
FileSelectFile, Rekening, 3, , Open tekstbestand, Tekst Documenten (*.txt; *.doc)
if Rekening =
{
MsgBox, geen bestand geselecteerd
Return
}
/*
Set criterium MinTijd: minimum calltime to check and put in same format as Timeduration without ':' in the bill
*/
InputBox, MinTijd, te controleren beltijd, Vul de minimum beltijd in:
if ErrorLevel
Exitapp
; is niet echt nodig maar wel mooier // nicer - same format
MinTijd := SubStr("000000" . MinTijd, -5)
/* explicit chars, (had some trouble with StringTrim, for removing <space> , lenght keeps the same)
*/
CR := Chr(13)
LF := Chr(10)
DP := Chr(58)
SL := Chr(47)
Logaan = 0
Vergelijk = 0
Loop, read, %Rekening%
{
LineNumber = %A_Index%
Loop, parse, A_LoopReadLine, %A_Tab%%A_Space%
{
Tekst = %A_LoopField%
/*
the sections before in this bill are 'national'and 'international', and after '0800' - the 2nd term 'Mobiel' marques the beginning of the section I need
*/
If Tekst contains Mobiel
telMobiel += 1
If telMobiel < 2
continue
GoSub, Identificeer
/*
Slash gives problem with nl timenotation in Excel
*/
If SlashGevonden > 0
StringReplace, Tekst, Tekst, %SL%, _, All
/*
append '-' after 06, cause Excel removes preceding zeros in integers
*/
If is06 = 1
StringReplace, Tekst, Tekst, 06, 06-
/*
remove ':' (%DP%) from ##:##:## timeduration format in the field, to make calculation for match with MinTijd, the minimum calltime to check
*/
If DP2Gevonden > 0
{
StringReplace, Tijdduur, Tekst, %DP%, , All
TekstT = %Tijdduur%
If TekstT > %MinTijd%
{
Logaan = 1
}
Else
{
Logaan = 0
continue
}
}
; SKIP SOME useless text fields, that you don't want to appear in the Excel report, and break when section 0800 is reached
If Tekst = Mobile
continue
If Tekst contains Mobiel
continue
If Tekst contains nummer
continue
;markeert nieuw gedeelte 0800 sectie
If Tekst contains 0800
break
/*
if match, compare with tel.book with xls output, to check what numbers were called, to identify the caller
*/
If (Logaan = 1 AND is06 = 1)
GoSub, Vergelijken
/*
if match, go on in the loop till the end of the recordfields is met, append Totstring to xls and empty string (to reuse the variable again in the next loop),
else if nothingknown (criterium not met yet)-> append to Totstring till criterium is met,
else (criteriumfield met and no match) empty the string and go on looping.
the field with format ##:## (not ##:##:##) marques the beginning of a new record in the bill.txt file
*/
If (DPGevonden > 0 AND DP2Gevonden = 0)
{
If Logaan = 1
{
FileAppend, %totTekst%, 06rekening.xls
}
totTekst = ""
StringReplace, totTekst, totTekst, "", %CR%%LF%%Tekst%%A_TAB%
}
Else
StringReplace, totTekst, Tekst, %Tekst%, %totTekst%%Tekst%%A_Tab%
; end loops
}
}
ExitApp
;;;;;;;;;;;;;;;;;;;;COMPARE WITH TEL.BOOK.txt ;;;;;;
; input Tekst, TekstT, telefoonnummers.txt
; output 06vergelijk
Vergelijken:
Fileread, Telefoonnummers, telefoonnummers.txt
Loop, parse, Telefoonnummers, `n, `r
{
Nummer = %A_LoopField%
StringTrimLeft, Stripje, Tekst, 3
IfInString, Nummer, %Stripje%
{
FileAppend, %Tekst%%A_Tab%%TekstT%sec.%A_Tab%%Nummer%%CR%%LF%, 06vergelijk.xls
}
}
;}
Return
;;;;;;;;;;;;;;;;;;;;; SOME CHECKS TO IDENTIFY TEXTvar PROPERTIES IN THE BILL AND USE THEM to build fields and records;;;;;;;;
; input Tekst
Identificeer:
DPGevonden = 0
DP2Gevonden = 0
KommaGevonden := 0
SlashGevonden = 0
is06 = 0
DPGevonden := RegExMatch(Tekst, ":")
DP2Gevonden := RegExMatch(Tekst, ":.*:")
KommaGevonden := RegExMatch(Tekst, ",")
SlashGevonden := RegExMatch(Tekst, "/")
Lengte := StrLen(Tekst)
If Lengte = 10
{
StringLeft, is06, Tekst, 2
If is06 = 06
is06 = 1
}
return
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
Login
Register
FAQ
Search
