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 has some obscure tips for dealing with this.


Leave a Reply

You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>