Create new records from fields containing lists of data

[stevemartin]

I have a table "sourcedata" with four fields each with data as a comma-separated list. I need to create a table "newdata" with one record for each combination of these data. The data is from an outside source and represents "rooms", "lecturers", "subjects" and "week numbers".

Sample record from "sourcedata" is:
field1 A-307, B-405,
field2 jones 200,smith2,
field3 chinese ,japanese, korean,
field4 2,4-6,9,12-15, 17,19-23,35,42-49,52,

In each field the number of items could be none, one or many. In the first three fields there will be maximum of about four items. The fourth field contains numbers and ranges denoted by hyphens so in the sample it means weeknumbers 2,4,5,6,9,12,13,14,15,17,19,20,etc. In any field there could be spaces (in random positions).

Each record from "sourcedata" will be used to make many records in "newdata", with fields ROOM/TEACHER/SUBJECT/WEEKNR. From the sample, records in "newdata" will look like
A-307/jones 200/chinese/2
A-307/jones 200/chinese/4
A-307/jones 200/chinese/5
etc
B-405/jones 200/japanese/2
B-405/jones 200/japanese/4
etc
This sample would lead to 2x2x3x25=300 records in "newdata".

A new "sourcedata" table is provided at least daily, then reports from "newdata" are generated which provide tailored timetables.

Suggestions, please, for a method of converting "sourcedata" to "newdata", preferably using action scripting rather than xbasic.

Thanks, Steve Martin

[Tom Cone Jr]

Re: Create new records from fields containing lists of data

Steve, what's your budget?:)

Actually, a straightforward solution to this will require a script that uses a series of nested loops to parse each line, counting delimited entries, and then looping through them to populate fields & records. Puzzles within puzzles. Might be fun over the weekend. Will standby to see if an easier solution is suggested for you.

[stevemartin]

Re: Create new records from fields containing lists of data

Tom, are you saying it's a complicated task? Are you saying we should commision you (or someone) to do it?(!!) I was hoping that with some expert prompting I could create the necessary routines. As for budget, none has been assigned, it's a bit of pro bono work on my part.

Steve

PS Tom, I sent this before I saw your smiley and the rest of your post. In the words of the song "it don't pay to be too hasty".

[Tom Cone Jr]

Re: Create new records from fields containing lists of data

Steve, it's not rocket science, but it IS complicated. You're presenting a problem that (at least to me) seems like a set of nested puzzles. There's a straightforward solution, but it is apt to be a bit tedious to develop and validate. I'm serious though when I say I hope someone else has a better idea for you. If they don't I'll take a crack at it myself over the weekend.

Concerning the sample sourcedata you supplied, will the first and second row always contain the same number of entries? And, who teaches Korean? And, where is Korean taught? I need you to explain the "ETC" lines a bit further, I guess.

[Mike Wilson]

Re: Create new records from fields containing lists of data

Steve,
I am sure it is doable. I am certain it is not going to be with action scripting.

I agree with Tom. Looks like a fun puzzle. Definitely as Tom described, multiple loops. My first thought was arrays, and for-next loops... but that won't work. Certainly the the first step is to extrapolate your Field 4 into individual values.

I'm pondering that knowing how many eventual records there will be (your example 300), start with to a temporary table table, enter field1 first value * (n of field1 values/total records (150) , then same for field1 second value (150). Then with the first value in Field 2, concatonate it to the value of all 300 records, and same for same for field 2 value 2, and so on. Should be very doable.

[Mike Wilson]

Re: Create new records from fields containing lists of data

Need clarification:
Each of field 3 is attached to each of field2, which is attached to each of field 1. BUT it doesn't appear that each of field4 is attached to each of field 3 in your example. Please clarify.

[Stan Mathews]

Re: Create new records from fields containing lists of data

Mike,

I think arrays and for next loops could do it.

Steve,

I've attached some files for you to examine. Script.txt is the script that does the work. Source.txt is your original sample data.

field1 A-307, B-405,
field2 jones 200,smith2,
field3 chinese ,japanese, korean,
field4 2,4-6,9,12-15, 17,19-23,35,42-49,52,

Newsource.txt is source.text processed to have a comma after each field# which aids in separating the data by commas and not relying on spaces which are non-standardized in the file. The script does this part first.

Newdata.txt is the output I think you want in csv format. This file should be easily imported into a table with an operation.

This script only processes four lines but it should be fairly simple to create a while loop to process a whole source data file in four line segments.

forpars.zip is the whole operational database which contains the runnable script (didn't bother with the import operation.)

[Stan Mathews]

Re: Create new records from fields containing lists of data

The attached script seems to work on a multi-segment file....

field1 A-307, B-405,
field2 jones 200,smith2,
field3 chinese ,japanese, korean,
field4 2,4-6,9,12-15, 17,19-23,35,42-49,52,
field1 A-101, B-202, C-899,
field2 hanigsberg 207,cone9,
field3 basketweaving ,snorkeling, pole sitting,
field4 2,4-7,10,12-15, 17,19-23,35,42-57,71,

The source file should end with a comma, not a comma and a crlf. IOW if the last "line" is blank, you get an error (easily trapped). If any source file segments are improperly constructed, say

field1
field3
field2
field4

or

field1
field2
field3
field4
field1
field2
field3
field1
field2
field3
field4

the script cleans up where it finshed the last good segment and terminates.

I think.

[stevemartin]

Re: Create new records from fields containing lists of data

Many thanks Stan, Mike and Tom. It's getting late in London, so I'll look at it over the weekend.

Steve

[G Gabriel]

Re: Create new records from fields containing lists of data

a-You could do what Tom suggested. In this case, you need a script with 4 while loops. No problem. But maybe not very economic.

In the example you attached, that one record alone will produce 300 records.

Or:

b-You could append to 4 tables in a set:
Parent table is field1
Child table is field2
Grandchild table is field3
Great grandchild table is field 4

This way, and based on the example you provided:
table1 will have 2 records
table2 will have 2 records
table3 will have 3 records and
table4 will have 25 records

Total records in all tables: 32 as oppsed to 300!

Take your pick!

[G Gabriel]

Re: Create new records from fields containing lists of data

I had a deja vue feeling when I first read this thread. Something there in the back of my mind told me, I did something similar to this before, couldn't remember when what or how. But now it all came to me.

You could use multiple while loops, and there is nothing wrong with that, but there is an obscure function in alpha that might simplify your code tremendously. check it out:
<TBL>.POPULATE_FROM_STRING()

[Mike Wilson]

Re: Create new records from fields containing lists of data

Gabriel,
Hey, thanks for sharing that. I have an immediate use for it.