validating excel -> access imports

  • Thread starter Thread starter eric theise
  • Start date Start date
E

eric theise

From time to time, we receive datasets from an external
organization in excel format. We want to run a series of
validations and conversions on this data before we save it
as a "trusted" data table.

Is it reasonable to ask what "best practices" are in this
situation? Should we be doing these validations in queries
or should we be doing something programmatic with VBA as
part of the import procedure?

Some of the problems we encounter:
Dates are stored in triplets of MM, DD, YY; we want to test
for invalid dates (February 31st) and combine them into
MM/DD/YYYY.

Dates of the same type of event vary between numerical
format and text format (eg, EVENT1MM and EVENT2MM differ).

Binary (0,1) and categorical data also vary between
numerical format and text format.

We want to do range checking, possibly generate some simple
statistical measures, and do some other calculations. None
of these data are updated once they're in our table, but
the table is subjected to logical queries and is exported
to excel and on to SPSS for further, detailed statistical
analysis.

Thanks for any insights.

--Eric
 
If the file is always in the same format (Big IF there)
then I would simply import the file to a staging table in Access.
(A staging table is a real Access table that you clear out, load with data
and then move to a production table.)

Then I would run my validation tests/code against the staging table.
If all tests are passed then I would use an Append query to move the good
data to the real table.
 
Back
Top