Jump to content

import cvs then autopopulate fields -script


This topic is 6076 days old. Please don't post here. Open a new topic instead.

Recommended Posts

  • Newbies

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

This topic is 6076 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.