Link to home
Start Free TrialLog in
Avatar of Bob Bender
Bob BenderFlag for United States of America

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
ASKER CERTIFIED SOLUTION
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks for the split.  Good luck with your project.  -Jim

>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.
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.