Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
database report simulation ??
#1
theory question:

i have two csv files. one contains cell phone billing info and is set up like this.

date,location of #,time,###-###-####,etc,etc

the other is a manually created "dictionary" of numbers that have appeared in the past and it is setup like this.

name, number, category

i would like to search the second file for the each of the numbers in the first, if it finds them, then output the billing info into a csv file and if there are ones that are not in the 2nd file output the billing info into another csv file.

i was thinking of using something like this to do it...

foreach
,get a number from the "dict" file
,,foreach
,,,get each line of the "billing file" and do a search for the number
,,,if it's in there
,,,,put the line into a "billing" variable to be written out later

but that just seems terribly unwieldy and system intensive...is there a better way or am i just trying to do something that really should just be done in a db?

thanks...
A new blog on QM coding and automation.

The Macro Hook
#2
I would at first parse first file and put numbers/lines into a map. Then parse second file and try to find numbers in the map. Finding a value in a map is much faster than using foreach and parsing same lines multiple times.

As a map, can be used Dictionary object from Scripting type library. Next QM version also will have its own string map (already done). Or you can use ARRAY instead. Not so fast, but much faster than foreach because to find a value you don't have to parse lines.

foreach line file1
,parse line
,add number to map

foreach line file2
,parse line
,if(find number in map) ;;much faster than using foreach
,,...
,else
,,...
#3
thanks!

where can i get info on how to setup/use maps. i cant find anything in the help or on msdn.
A new blog on QM coding and automation.

The Macro Hook
#4
Search for Dictionary object in MSDN library.

Example
Code:
Copy      Help
out
typelib Scripting {420B2830-E718-11CF-893D-00A0C9054228} 1.0

Scripting.Dictionary m._create

str file1=
;aaa, 123, aaa
;bbb, 234, bbb


str file2=
;ccc, 234, ccc
;ddd, 345, ddd


VARIANT vk vv
str line number s
ARRAY(str) a

foreach line file1
,if(number.gett(line 1 ",")<0) continue
,number.trim
,;out number
,vk=number; vv=line
,m.Add(vk vv)

foreach line file2
,if(number.gett(line 1 ",")<0) continue
,number.trim
,;out number
,vk=number
,if(m.Exists(vk))
,,s=m.Item(vk)
,,out s ;;found line from file1
,,;...
,;else
,,;;...
#5
thanks!


just an aside here....i didnt know you could set variable values like you did for file1 and file2...neat. Smile
A new blog on QM coding and automation.

The Macro Hook
#6
nice...i'm starting to get my head around this one.

thanks again.
A new blog on QM coding and automation.

The Macro Hook


Forum Jump:


Users browsing this thread: 1 Guest(s)