468,242 Members | 1,605 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,242 developers. It's quick & easy.

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.
Dec 11 '07 #1
3 2735
On Dec 11, 12:32 pm, scoots987 <scoots...@gmail.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
Dec 11 '07 #2
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.
Dec 18 '07 #3
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*******@gmail.comwrote in message
news:7d**********************************@s12g2000 prg.googlegroups.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.

Jan 11 '08 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by Little PussyCat | last post: by
7 posts views Thread by Darren | last post: by
5 posts views Thread by hharriel | last post: by
3 posts views Thread by D.Stone | last post: by
reply views Thread by kermitthefrogpy | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.