import cvs then autopopulate fields -script

[sheridanla]

I have a cvs file to import into my database. No problem there. BUT, I want to take the imported data and compare it to existing data and autopopulate or autofill the information fields not contained in the imported data.

for example:

data comes in with item_number, item_description, etc.

existing data has information like assignment_client, usage, image.

I would like it to compare the image_number with existing records and fill in the remaining information.

any help on creating a script for this or finding one that might be of use to me? or someone want to let me hire them to create this?

Thanks..

[Fenton]

The basic problem with trying to do this as a 1-step Import, using the "Matching records" option, is that a blank field in the imported data will wipe out existing data in that field. So that is out.

Which means you have to import the csv data into a separate table. Then you need an absolutely reliable relationship between them, based on a primary key (often unavailable in the csv), or failing that, using a multiple criteria relationship, with a combination of fields that produces a unique match. Let's say the relationship is named "Main".

Then run a Loop, starting at the 1st record of the imported data (which are the found set). Each record will either have a match to an (and only 1) record in the existing table, or it will not. If not, it's new; I think I'd just ignore it (for import later), and Go to Record [ Next; exit after last ] to continue within the loop.

As far as checking/setting the fields, I think I'd do it the tedious way, with a bunch of If tests.

If [ not IsEmpty (Address) ]

Set Field [ Main::Address; Address ]

End If

If [ not IsEmpty (Street) ]

Set Field [ Main::Street; Street ]

End If

etc. for all relevant fields

When you've done the fields for this record, Omit Record. This places you on the next record.

Tedious. But you're just looping through the imported records. You don't have to go to Main to do this.

When you're done setting the fields, omitting records as you go, you'll be left with a found set of new records (if any). Then you can go to the Main layout,* and Import from the csv table. If successful, you can then delete All the csv records.

*Actually you don't have to go to the Main layout, you can just specify the Main table as the target in the Import step dialog.

[P.S. Highly advised to practice on another file and/or backup your file before trying this.]

[Fenton]

Actually, upon rereading your post, it may be that NONE of the fields are the same. In that case a 1-step Import, using the Matching Records option, with a unique key (or set of fields), would be all you'd need.