Need help getting data into a table

[Tommy Thompson]

I need some suggestions in how to unravel a text file.

This particular file is output from the Federal Reserve's wire transfer program. Each record is a variable length record (no problem). They use field identifiers that are structured like this {xxxx} where the xxxx is a 4 digit number. This code {XXXX} identifies a particular field depending on what the code is.
\
For example, a record might look something like this--{1000}20090605112243TFY{1500}Tommy Thompson{1510}915 Thompson Road{1520}Springville Tn{1530}38256

{1000}=Unique ID {1500}= Name {1510}= Address and so on.

Obviously, this is a variable length record with variable length fields.

Still no problem. I have been able to successfully parse the record and extract both the code {xxxx} and it's corresponding field. As of right now, I'm outputting a text file that looks like this. unique id,code,fieldname. I drop the brackets so the actual file would look like this.

20090605112243TFY,1500,Tommy Thompson
20090605112243TFY,1510,915 Thompson Road
Etc

There are only a certain number of the codes {xxxx} and I have the documentation from the Fed. There are probably 25 or 30 such unique codes.

Where I could use some help is how to get this info into a table. I would like to do it as efficiently as possible.

I would like to have a table that has fields for the unique ID and each of the possible codes (25-30) and some how post to it. Either from the text file I build, or a dbf table created by an import of the text file.

Any suggestions?

[MikeC]

Re: Need help getting data into a table

Tommy,
I think that if you could keep the entire record on one comma delimited line per record it would be much easier.

A couple of things that need to be known...
Does the data come across with All the codes even if no data for the code?
Does the data come across as single records or as you show after parsing as multiple lines for the same record?

IOW, need to know the consistency of the data--the original format AND also the exact format you want to end up with.

[Tommy Thompson]

Re: Need help getting data into a table

Tommy,
I think that if you could keep the entire record on one comma delimited line per record it would be much easier.

A couple of things that need to be known...
Does the data come across with All the codes even if no data for the code?

No, only the fields that happen to be used for a given record. There might be 10 codes in one record and 12 in another.

Does the data come across as single records or as you show after parsing as multiple lines for the same record?

It comes across as single records. I break out each record into a string and parse it. I have put the new text fileout as one string delimited by | (can't use commas since they are in the fields. ex Anywhere,TN) I also put it out the way described in my post. This is a temporary file.

IOW, need to know the consistency of the data--the original format AND also the exact format you want to end up with.

I cannot show the actual data for security reasons, but the file is a variable length record length with variable length fields. Each field preceding by a 6 character identifier. There is a fixed length unique ID at the beginning of each record. the rest of the record is code,field,code,field,code,field. There is not a consistent length since each record can contain any number of code,field combinations. All that said, I have that part worked out. The question is, is there some dynamic way to read and post. I know I can do 30 compares (if code = 1500, then do something) I was mainly looking for something more elegant.

The final table will look like this:

Unique_Code
Code1
Code2
.
.
.
Code30

[Tom Cone Jr]

Re: Need help getting data into a table

Instead of using a table for the code lookups, why not use an array. Create it once and use it over and over in order to translate a code number into a field name. Something like this:

dim codes[1500..2000] as C
codes[1500] = "Name"
codes[1510] = "Address"
codes[1520] = "City"

'translate code into fieldname:
?codes[1510]
= "Address"

It seems to me that if you could generate a string that contains the fieldnames and the field values you ought to be able to use the property_from_string() function to insert new records in your table. This will require careful formatting of the string.

If you wanted to work with your method for parsing the input string you could loop through each line of the text file you are creating, keeping track of the unique_id. Begin a new record when the id changes, after committing the previous record to disk. As each line is processed you'd translate the code into a fieldname using the previously define array. Here's an example using AlphaSports, showing how to use the fieldname you get from the array in the context of entering a new record.

t = table.open("customer")
dim fld_ptr as P
dim fld as C
fld = "lastname"   'fld is the field name obtained from codes array

t.enter_begin()
fld_ptr = t.field_get(fld)  'establish pointer to a field
fld_ptr.value_put("Smith")   'assign field value to the field pointer
t.enter_end(.t.)
?t.lastname
= "Smith               "

[Tim Kiebert]

Re: Need help getting data into a table

Since you know that each code belongs to a particular field you can loop through the file grabbing each line (as you probably already are) and use code similar to this

dim t as P
t=table.open("customer")
dim scource_line as C

[COLOR="Blue"]'start of loop[/COLOR]
scource_line = "{1000}20090605112243TFY{1500}Tommy Thompson{1510}915 Thompson Road{1520}Springville Tn{1530}38256"
scource_line = scource_line + "{"  [COLOR=Blue]'adds a { to delimit the last field value
[/COLOR]

t.enter_begin()
t.Company = extract_string(scource_line,"{1000}","{")
t.Lastname = extract_string(scource_line,"{1500}","{")
t.Bill_address_1 = extract_string(scource_line,"{1510}","{")
t.Bill_city = extract_string(scource_line,"{1520}","{")
t.Bill_postal_code = extract_string(scource_line,"{1530}","{")
[COLOR=Black]t.Phone = extract_string(scource_line,"{1540}","{")[/COLOR]   [COLOR=Blue]'there is no {1540} so nothing is entered[/COLOR]
[COLOR=RoyalBlue]'....additional lines for the rest of the codes[/COLOR]
t.enter_end()
[COLOR="Blue"]'end of loop[/COLOR]

t.close()

This adds a new record to the customer table in Alpha sports. If any of the codes are missing from the scource they simply won't be found and nothing is entered.

[Tommy Thompson]

Re: Need help getting data into a table

Many thanks to all of you. I believe I have what I need. I'll try to remember to post the final result in case anyone else runs into a similar situation.