Data Validation Tools (ETL Tools) for SQL Server
I have some data in Excel and need to be imported into a database. Is there any tool out there that can inspect and possibly clear the data? Does Red Gate have such a tool?
The login will be Excel. Given table constraints, for example. CHECK, UNIQUE KEY, date and time format, NOT NULL. Desire's output should at least show which rows are having problems and then fix some trivial error automatically, like fill in the default for NULL columns, automatically fix the date and time format.
I know using Python can create a script like this. But just wondering what is the popular way to do this. Thanks.
a source to share
Typically, you load the staging table and do a check. The staging table will usually have more varchar columns that represent the "real" table, all nullable values, no limit, and so on.
Example of searching for strings with duplicates
SELECT COUNT(*), UniqueKey FROM StagingTable GROUP BY UniqueKey HAVING COUNT(*) > 1
Then you run non-null sort, validation code, etc. in sequence
Then, when you are satisfied, you will load the real table from your staging table.
a source to share