View Full Version : Programming Puzzle 18 - Import Madness #2


Tom Cone Jr
07-20-2011, 11:33 AM
Puzzle 18

[ Thanks again to Andy Schone, for coming up with this one. ]

Your manager interrupted your coffee & danish this morning to complain
that your CSV import script has stopped working. She is sure nothing
has changed, and implies that your script is somehow responsible for
a total breakdown in civil order in the western hemisphere!

On her way out of your cubicle she gives you the attached CSV file
and says "Make it work".

Quick inspection of the CSV indicates that the unnamed source for the
data file has in fact changed the structure of each row. Each row now
include middle names, sometimes.

Your task grasshopper is this:

1) Modify your script ( puzzle 17 (http://msgboard.alphasoftware.com/alphaforum/showthread.php?94766-Programming-Puzzle-17-Import-Madness-1) ) so that it adds a Middle_name field
to your DBF if it does not already exist.

2) Modify your script so that the data from the new CSV is successfully
"imported" into your table. Careful. some records contain a middle
name, while others do not.

3) Make sure you "import" 100% of the available data in each row of the CSV,
placing each element into its correct field in your table.

4) As before prefix the name of your script with your initials, and attach
the exported version of your script here for our edification.

Hint: If the middle name is part of the record it is always the 2nd
field of the record.

Mike Wilson
07-20-2011, 03:57 PM
Here is my attempt. I imported the whole broken cvs file to a temp table, processed the records from the temp table and deleted the temp table. Pretty quick (~.36 sec) for these few records but may not be useful for thousands of records.

Tom Cone Jr
07-21-2011, 12:30 PM

That's an interesting approach. Presumably you let Alpha write some of the code for you by defining an import operation, and then capturing the xbasic code? Very clever, grasshopper!

Prof. Pickpicky has assigned 3 demerits however, for not commenting your code sufficiently. His standard is easy legibiliity six (6) months from now. Take heart, though, he's been persuaded to reconsider such judgments, if the code is resubmitted for his further consideration.

Thanks for participating!

-- tom

Mike Wilson
07-21-2011, 03:03 PM
OK, here is the newly commented script, Professor. Yes, I let Genie generate the import operation, converted it to xbasic and I used what I needed. Heck, I'm a two finger typist, why waste the time when Genie will do it for you?!

Tom Cone Jr
07-21-2011, 03:06 PM
Thanks, Mike.

I thought it worthwhile to mention using the xbasic which the operations genie builds in case newcomers didn't know about this handy little trick.

07-24-2011, 11:45 PM
Major Functions used: in addition to ones used in the first import madness

w_count() (http://support.alphasoftware.com/alphafivehelpv10/Functions/W_COUNT%28%29.htm)
is_one_of() (http://support.alphasoftware.com/alphafivehelpv10/Functions/IS_ONE_OF%28%29.htm)
a5.table_enum() (http://support.alphasoftware.com/alphafivehelpv10/Methods/A5.TABLE_ENUM%28%29.htm)
table.external_field_name_get() (http://support.alphasoftware.com/alphafivehelpv10/Methods/TABLE.EXTERNAL_FIELD_NAME_GET%28%29.htm)
a5_add_fields_to_table() (http://support.alphasoftware.com/alphafivehelpv10/Functions/A5_ADD_FIELDS_TO_TABLE%28%29.htm)
for each ... next (http://support.alphasoftware.com/alphafivehelpv10/Language/FOR_EACH_..._NEXT.htm)

Mike Wilson
07-25-2011, 03:39 PM
Nice work, Andy. I'd say that maybe the most important function you use you didn't list.... populate_from_string(). I don't use that, but now I think I might.

07-25-2011, 04:45 PM
I listed populate_from_string() in import madness #1. The functions listed here are the ones that I used in addition to the previous listing.