database report simulation ??

[ken gray]

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...

[Gintaras]

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
,,...

[ken gray]

thanks!

where can i get info on how to setup/use maps. i cant find anything in the help or on msdn.

[Gintaras]

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
,,;;...


[ken gray]

thanks!


just an aside here....i didnt know you could set variable values like you did for file1 and file2...neat.

[ken gray]

nice...i'm starting to get my head around this one.

thanks again.