Here is my suggestion for data scrubbing. I suggest that you import the data into your app in a two step process. You make a table which has all the correct field names matching your import and you call that a scratch file. I think that's an old data processing term so call it what you want, but it just means a temporary table.
So you bring your raw data into this temporary table, and you use formulas to make any corrections or to detect any formatting errors. And then when the data is scrubbed, then you push a button which will run a saved table to table import and bring the clean data into your main table.
So the idea is anything you can correct with formulas like capitalization or trimming off leading and trailing spaces you do that by formula in the scratch table and then it'll come in clean into your main table.
What I like to do for clients is to create an admin import control record with which sits in a dedicated table with just one record in it. Because that will be Record ID number 1 you could connect that to the scratch file table by a reference field in the scratch table formula of 1. So then the admin table knows whether the scratch file has data or not.
Then you can put a URL Formula button there for example to purge the scratch file.
Another button to import into the scratch table.
And another button to copy the scratch file into the main table. You can also have summary fields to count the number of invalid records and prohibit the import if the scratch table is not all clean.
Feel free to post back if you have any questions about this approach.
