Welcome to dbForumz.com!
FAQFAQ      ProfileProfile    Private MessagesPrivate Messages   Log inLog in

Importing from Excel problems

  
   Database Forums (Home) -> General Discussions RSS
Next:  Importing text with formatting  
Author Message
scoots987

External


Since: Nov 25, 2007
Posts: 8



(Msg. 1) Posted: Tue Dec 11, 2007 4:32 am
Post subject: Importing from Excel problems
Archived from groups: comp>databases>ms-sqlserver (more info?)
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.

 >> Stay informed about: Importing from Excel problems 
Back to top
Login to vote
jhofmeyr

External


Since: Nov 15, 2007
Posts: 68



(Msg. 2) Posted: Tue Dec 11, 2007 6:19 am
Post subject: Re: Importing from Excel problems [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
On Dec 11, 12:32 pm, scoots987 <scoots... DeleteThis @gmail.com> 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

 >> Stay informed about: Importing from Excel problems 
Back to top
Login to vote
aj

External


Since: Jun 02, 2004
Posts: 57



(Msg. 3) Posted: Tue Dec 18, 2007 2:25 pm
Post subject: Re: Importing from Excel problems [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
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.
 >> Stay informed about: Importing from Excel problems 
Back to top
Login to vote
Jack Vamvas

External


Since: Aug 13, 2007
Posts: 60



(Msg. 4) Posted: Fri Jan 11, 2008 2:59 pm
Post subject: Re: Importing from Excel problems [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
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" <scoots987.TakeThisOut@gmail.com> wrote in message
news:7d46ad3b-34a8-432e-826e-1b884854b9be@s12g2000prg.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.
 >> Stay informed about: Importing from Excel problems 
Back to top
Login to vote
Display posts from previous:   
   Database Forums (Home) -> General Discussions All times are: Pacific Time (US & Canada) (change)
Page 1 of 1

 
 You can post new topics in this forum
You can reply to topics in this forum
You can edit your posts in this forum
You can delete your posts in this forum
You can vote in polls in this forum



[ Contact us | Terms of Service/Privacy Policy ]