Import spec vs code

  • Thread starter Thread starter Sandra
  • Start date Start date
S

Sandra

Here's hoping someone can help!

I have an Import spec set up, but I was wondering if there
is anyway to change this spec once set up?

I have a csv file that I import every month, but it may
change slightly each month. I would actually like to be
able to make changes without the user going into the back
end of the database. Is there code that can be executed to
import this data and set a spec?

Cheers!

Sandra
 
A spec is used for an unchanging file format.
You really can't have it both ways here!

Why does the file layout change?
Make it the same every week and you will be in business.
If some piece of data is omitted occassionally, then include a "place
holder" for it anyway.
e.g.
LastName,FirstName,MiddleName,Title
Fallon,Joe,,MVP

(Note the extra comma to hold the missing middlename.)
 
The reason data changes:

We run a market analysis of different insureres and
sometimes there are new schemes to add to our analysis and
sometimes schemes are deleted. New schemes are not
necessarily added to the end of the document.

Is there any code that can be used to prompt the user to
input the name of any new scheme or any deleted scheme,
then import the data?
 
Sorry, but I don't think you can automate that type of process.
You need consistent information.
 
I have found a way!!

Export one of the hidden tables that holds the data for
the specs, and you update it in Excel.
It's not a great solution, but better than having to set
up a new spec every time!

Thanks anyway!

Sandra
 
Hi Sandra,

The fact that the structure has to change from week to week suggests
that your database isn't normalised. If

(a) you can normalise your Access database in a way that lets it handle
the different schemes without changing its structure (which means that
adding a new scheme would mean adding records to existing tables, not
adding fields to existing tables), and

(b) even though the structure of the csv file may vary, it is consistent
at a deeper level (i.e. the structure may vary but always conforms to
the same underlying rules)

then it should be possible to write code that analyses each month's csv
file, parses the records and creates the corresponding records in your
Access database. But it may not be easy. Without the underlying
consistency, as Joe says, it's impossible.
 
Back
Top