That lazy method can work a lot of the time, but I know it won't in some cases. Example - US has a postal code system that has two common components - ZIP and ZIP+4. Zip is a 5 digit code. ZIP+4 has a format of #####-####. In Excel, if this column is just left as General, the underlying JET provider reading the sheet interprets this sheet based on what it sees in the column. In this case, that PostalCode column is treated as a FLOAT for import purposes. All of those ZIP+4 values are lost because they can't convert to a FLOAT type, regardless of your target column.

Admittedly, I have not tried this in SQL2008's import. If that's changed, great, but the way I understand it is that we can't actually override the way JET works in any easy manner. There are some properties you can set at the driver level, but not through the Wizard. You can read through the table, but IIRC, that results in a "majority wins" type of determination for data types.

In any case, Excel just isn't a friendly source for a variety of reasons if you're trying to handle a bunch of mixed data. When you're dealing with purely numeric or character data, it's not too bad. Once you start mixing in data types within a column, you're going to have issues.