Importing from Excel problems

[scoots987]

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.

[jhofmeyr]

On Dec 11, 12:32 pm, scoots987 <scoots...@gmai l.comwrote:

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

[aj]

I've recently had great experience importing Excel into the DB using
SSIS (SQL Server Integration Services) and the OLE DB Excel Connection.
It has transforms and all sorts of goodies to make the import easy and
omplete.

HTH

aj

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.

[Jack Vamvas]

Yes , these problems are common . Transforming to a tab delimited is a good
idea.
You could automate this and set it up as a job - dts

--

Jack Vamvas
_______________ _______________ _____
Search IT jobs from multiple sources- http://www.ITjobfeed.com


"scoots987" <sc*******@gmai l.comwrote in message
news:7d******** *************** ***********@s12 g2000prg.google groups.com...

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.