Best practices for cleaning import data?

[TerraEarth]

I was wondering, in your esteemed experiences, what is preferable when it comes to cleaning data imports: After or Before the import?

For example, I import a series of excel files from various users and use those to update live table data in the Access Database via a SQL INNER JOIN/UPDATE query. However, a lot of this data is not suitable for flowing into the database for many various reasons. For example, statuses might have been left blank, or improper codes used. It is difficult for me to control validation for these different sources across users so my only option at the moment is to clean the data before the import.



Do you guys think it is better to clean the data pre import or post? Cleaning it post would involve querying as well as updating the data (probably with sql or VBA) whereas cleaning it pre-import would involve using vba/R/python/etc. to tear things up and set things straight before funneling it in. Thoughts?

[ranman256]

I cleaned before import.
I had user select the XL file, then using VB code I had to open the file,
remove the 1st blank row so the headers would be 1st,
then some fields were both numeric and string so I had to convert the column to hard string.
Then it would import correctly.

[pbaldy]

You can also import into a local table, clean/manipulate the data, then import into the "real" table.

[JoeM]

Alternatively, here is a different option.

One option I have used is to import the data into a "temporary" holding table, which is structured very similar to the final desintaion table I am importing it to.
Then, you can create a Query on this "temporary" table, adding criteria to only select the records that you want to import to your final table.
Then, change this query to an Append Query to write those records to the final table.

If you then delete all the data out of the Temporary table afterwards, then you can re-use this process over and over (once you have set up the temporary table and query the first time, you should have to do that again - simply re-use it).

Just note that if you delete a lot of data out of Access tables, it is important to regularly "Compact and Repair" the database to purge those deleted records, and keep the database size down (even though you no longer see the records in the table after your delete them, they really aren't completely deleted from the database until they are purged like that).


EDIT: Just saw Paul's reply. So I just described in detail what he is recommending (I am just a slow typist, and he posted that while I was trying to type this all up!).