Bob Bender
asked on
Best approach for convert Excel sheets with "junk data" to SQL 2008?
Really asking about data types to use for DATE fields that have nulls, "--" and N/A comments in a date field.
I can import, but everything comes in as VARCHAR 50. Can a date be stored as VARCHAR 8 and cast in your C# code to handle it?
What is the best appproach? Conversion of "--" to a dummy date like MS DOS did with 01-01-80 for things that existed before the PC came out? Ort am I stuck with "Garbage In" and at a loss to convert?
Tips?!??! Pointers?>!?!?
Bob
I can import, but everything comes in as VARCHAR 50. Can a date be stored as VARCHAR 8 and cast in your C# code to handle it?
What is the best appproach? Conversion of "--" to a dummy date like MS DOS did with 01-01-80 for things that existed before the PC came out? Ort am I stuck with "Garbage In" and at a loss to convert?
Tips?!??! Pointers?>!?!?
Bob
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
My staging tables have identity columns. I put errors, if any, into a separate table, with the staging table name and identity value. This keeps a history of load errors and allows the staging table to be cleared for re-use if needed.
>Then write T-SQL with various updates to convert "--" to NULL, handle the dummy date,
My usual process is to create a Stored Procedure that does all this, including ISDATE(), ISNUMERIC, etc.
Also my staging table usually has two extra columns, validation_errors and validation_description, which I populate if any row fails one of these tests. Then the final pump is a SELECT * WHERE validation_errors = 0, and ultimately I kick off a message whenever there are validation errors for somebody to fix.