import issue

Question

import issue

sahana

SSCrazy

Points: 2530
More actions
August 31, 2004 at 3:30 pm

#59406

I am importing an excel file into the DB and one of the column in the excel sheet has the data shown below.
1111
2222
nnnnn
llll
3kkk
4fff
yyyy
xxxx
WHen i import this data i could not get the data nnnnn,llll,yyyy,xxxx into my db.
And after few attemts i found that if i have the first element in the list start with an alphabet its importing all the information start with alphabets(nnnnn,llll,yyyy,xxxx).
If the first element in the list start with a numeric value its only importing the following values(1111,2222,3kkk,4fff).
But how can i import all the records into my DB.
Thanks.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Aaron Templeton SSCarpal Tunnel Points: 4655 More actions · Answer 1

I don't have a direct answer for you, but check your delimiter to make sure its detecting columns correctly. Check the data type detected on the SQL table to make sure its compatible. Check the transform script to see if it is doing something strange. As far as I can tell what you have should work in a normal world.

If all else fails use a macro or something in excel to prefix that column with a unique constant, increase the SQL column size to support the new data length, import the data, trim the constant. There shouldn't be a need to do that, though.

babuganesh2000 Hall of Fame Points: 3123 More actions · Answer 2

This is pretty straigt forward. I have also faced the same kind of problem.

Open the excel sheet, Copy the content of the cell and paste it to notepad,Select the column,Right click, Select Format Cell,

Go to the first tab, select text and click ok. Then copy the content from the notepad and paste it to the excel sheet.

Then run the import process. It will do the required operation.

Thanks,

Ganesh

garylzon Old Hand Points: 377 More actions · Answer 3

http://support.microsoft.com/default.aspx?scid=kb;en-us;321686&Product=sql2k states:

When the Jet Provider determines that an Excel column contains mixed text and numeric data, the Jet Provider selects the "majority" data type and returns non-matching values as NULLs.For additional information about how to work around this problem, click the article number below to view the article in the Microsoft Knowledge Base:

194124 PRB: Excel Values Returned as NULL Using DAO OpenRecordset (http://support.microsoft.com/default.aspx?scid=kb;EN-US;194124)

Moral of the story : Only store one data type in each excel column!

johncyriac SSCommitted Points: 1542 More actions · Answer 4

This problem is can be sorted out by either

Select the column in excel right click ,select format cells, in category list select text press ok. This will solve the problem .
If you are using DTS wizard you are free to select the column data type
One more tip , just keep some alpha text filed in the first cell this will result in considering the entitre column as text (or varchar)

regards

john

SQLArch Ten Centuries Points: 1322 More actions · Answer 5

I had the problem other way. when importing impoted text but not intergers

eg.

abcd

efgh

ijk

2003

2004

www

2003 and 2004 did not get imported. Used Access to import first then into SQL worked fine.

RTSQL Old Hand Points: 344 More actions · Answer 6

I would suggest creating a DTS package in this scenario since this needs some tweaking to the extended properties of the excel connection. You can let the import wizard create the DTS package and select save option instead or run immediately. Once the DTS package is created follow these steps:

open the package

Select the menu option "package->disconnected edit"

Expand the connection object

Expand the excel connection object

Expand "OLE DB Properties"

Click on the "Extended Properties"

On the right hand side you will see the text for the "Value" is "Excel 8.0;HDR=YES;"

Change to "Excel 8.0;HDR=YES;IMEX=1"

IMEX=1 lets the excel treat the columns as mixed datatype. This will let you download all the data. I hope this helps. Let me know.

Thanks

Ramesh

johncyriac SSCommitted Points: 1542 More actions · Answer 7

Good Idea ,

Once in bluemoon import means ,just save the file in dbase format and then import

regards

john

Iulia Sabau Newbie Points: 9 More actions · Answer 8

The solution you gave here with using IMEX=1 argument to the Jet connection string, can also be applied to MSDASQL.1 connection string?

Because I tried to do this and it did't work.

My connection string looks like this:

Provider=MSDASQL.1;Persist Security Info=False;Data Source=Excel Files;Initial Catalog="D:\excel.xls"

Thanks, Iulia

sukhoi971 SSCarpal Tunnel Points: 4833 More actions · Answer 9

i guess u need a product update, go to microsoft.com and

look for updates, mdac, jet engine or whatever,

usually such types of unusal errors get solved by updates

i had faced tons of unusall errorss

<a href="http://www.websolsoftware.com"> For IT jobs click here</a>

*Sukhoi*[font="Arial Narrow"][/font]