So I need to import data from Excel into SQL Server. I whip up a new table, knock out a quick package, run the package, and look at the data. Looks fine, okay. Email the customer so they can look at the table. Two days later I get the email from the customer saying, “How come these rows have null values in the somekeyidentifier column? The data is in the Excel source.”
And thats when I realize that #1, I need to enforce some constraints when I’m whipping up new tables. And #2, Excel is not very good at guessing what datatypes I have. You see, an intelligent data profiling tool samples data when determining datatypes. Excel just looks at the top X rows and just ignores every row that doesn’t match that datatype.
The ever helpful Allan Mitchell at SQLDTS.com has some obscure tips for dealing with this.
Tags: Excel