Problems importing from Business Search Pro

[Introfield]

My client wishes to regularly import a "campaign" from Thompson Business Search Pro. I have two problems... A date field in the destination (Access 2000) table is a required field. BSP export file does not contain such a field (I am happy for current date to be inserted at import).
My second problem is that field names differ.
I currently perform the export/import via Excel and use an Excel macro to clean up the data (and insert the required date field) before sending to Access. This works but is not ideal - I'd like to skip the Excel bit.
Also, the BSP campaign seems to extract a couple of extra fields every now and them (presumably when there is data in that field).

 

[B827]

I would import the data directly into a Access table then use a combination of a Query and functions to add the Date and clean the data and append to the current table. You would delete the previous load before loading the new data. This method would require regular compacting of the database.

The only concern is the "now and then" data. Does the data occur in every extract but only some entries or does it only occur in certain extracts? Sandy

 

[Introfield]

Hi Sandy
Thanks for your response.
If I used another Access database to perform the tasks(delete previous/import.csv/clean up and add date) before appending data will it avoid the need for regular compacting? My users are beginners (at best) and they don't have on site support at any level.
"Now and then" data... it's the quantity of fields that is irregular. One example of this is: If I extract records for customers that use only Address1 and Address2 it does not extract an empty field called Address3. Therefore I cannot predict how many fields will be imported each time.