473,324 Members | 2,313 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

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 2974
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Little PussyCat | last post by:
Hello, I nee to write something that will transfer excel data into an SQL Server table. I have for another database application I wrote have it importing Excel spreadsheet data using cell by...
9
by: Edward S | last post by:
I budget for a Project in an Excel sheet as illustrated below. The months below are usually a 2 year period i.e. 24 months, though it could be over 24 months depending upon a Project. I then...
7
by: Darren | last post by:
I have been attempting to create a reservation planning form in excel that imports Data from an Access database and inserts that information automaticly into the correct spreed sheet and the...
2
by: nutthatch | last post by:
I want to be able to import an Excel spreadsheet into Access 2K using the macro command Transferspreadsheet. However, the file I am importing (over which I have no control) contains some records...
5
by: hharriel | last post by:
Hi, I am hoping someone can help me with an issue I am having with excel and ms access. I have collected data (which are in individual excel files) from 49 different school districts. All...
1
by: ltikusis | last post by:
I am currently using the trial version of MapPoint 2006 and I am having problems mapping data from Access queries. When importing a query, MapPoint does not match any of the fields to the map. I've...
2
by: WODJ | last post by:
Folks, I hope someone out there can please help me, not much hair left with this one. I'm importing excel data into a SQL server database using an Oledb connection in C#. The problemm is that...
3
by: D.Stone | last post by:
I'm trying to import an Excel spreadsheet into an existing Access table using Office 2003. Ultimately, the plan is to do it programmatically using TransferSpreadsheet, but to check that the file...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.