Importing from Excel problems

What do others do if you need to import excel files into SQL Server?
My main problems are

1) zipcode formatting issues. If the column is a mix of zip and zip+4,
I have problems retrieving all zipcodes.

2) If the last column contains NULL no information is imported.

All this with using the Management console using Import data in SQL
Server 2005. I am simply trying to import the data into NEW databases.
The excel files vary in structure. Right now I am working on case by
case basis.

Does anyone see these types of problems?

What I am doing now is converting the excel file to a tab delimited
file and that seems to work.

TIA.

On Dec 11, 12:32 pm, scoots987 wrote:
> What do others do if you need to import excel files into SQL Server?
> My main problems are
>
> 1) zipcode formatting issues. If the column is a mix of zip and zip+4,
> I have problems retrieving all zipcodes.
>
> 2) If the last column contains NULL no information is imported.
>
> All this with using the Management console using Import data in SQL
> Server 2005. I am simply trying to import the data into NEW databases.
> The excel files vary in structure. Right now I am working on case by
> case basis.
>
> Does anyone see these types of problems?
>
> What I am doing now is converting the excel file to a tab delimited
> file and that seems to work.
>
> TIA.

Hi scoots987,

I usually use a dummy row in my excel files to force the correct data
types and column mappings (typically I import everything as text and
convert it downstream). One of the big problems with importing data
from an Excel file is that SQL Server (well .. the I think it's
actually the OLEDB driver) only looks at the first 8 rows of data to
determine what data types to use. To force it to look at more rows,
you need to change a couple registry settings, which in my experience
is usually off-limits in a managed production environment (check out
http://blog.lab49.com/?p=196 for info .. it's not a SQL blog, but it
explains the issue well).

Good luck!
J