Import From Excel - missing data

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Import From Excel - missing data

Author

Topic

jsiedliski
Yak Posting Veteran

61 Posts

Posted - 2004-08-24 : 15:18:48

I know this has been posted before, but I wanted to re-post since the last thread did not seem to have a working solution.

I am running Win2003 Server - SQL Server 2K SP3a.

I am trying to import an Excel spreadsheet (simplified example)

Column A is Price and contains the following data:

call
call
call
530.00
640.00
call
call
67.00

The target column is VARCHAR.

When I import the data all the values with numeric data (prices) get removed and set to NULL. All I see in the column is 'call'. I do not understand why the numeric values are not getting in when I specified the target column to be VARCHAR?

Regarding the registry - HKLM\Software\Microsoft\Jet\4.0\Engines\Excel\TypeGuessRows

Setting TypeGuessRows to Zero results in only the text values getting imported.

Setting TypeGuessRows to 16 (max, I believe) resulst in only the numeric values getting imported.

How do I get all values to get imported? The strange thing is that I had no such issues back in April. My network admin thinks that the insallation of Winsows updates may have caused the change.

Does anyone have ideas on how to resolve this issue? If I can not fix the importing of the .xls, I am considering using .csv, which does not cause the same problem. But I would rather fix the .xls import since a lot of code would have to be changed.

Thanks in advance,

Jack

Here is a previous thread:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=29981&SearchTerms=import,excel,numeric

Here is the link to the Microsoft article:
http://support.microsoft.com/default.aspx?scid=kb;en-us;281517

:)

X002548
Not Just a Number

15586 Posts

Posted - 2004-08-24 : 16:15:13

Another reason to dislike DTS

I replicated your problem...

I changed it to a tab delimited file and it worked fine....

Brett

8-)

jsiedliski
Yak Posting Veteran

61 Posts

Posted - 2004-08-24 : 19:25:27

That makes me feel somewhat better that you can reproduce the issue.

But, aside from altering the original file, is there a way to make this import work in DTS?

Thanks,

Jack

:)

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-08-25 : 00:55:00

I messed around with this for awhile and couldn't find a way to fix it. I go the file to import fine when I put a ' in front of the numbers. It then recognized all the columns. Pretty crappy though.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

samsekar
Constraint Violating Yak Guru

437 Posts

Posted - 2004-08-25 : 09:14:16

I belive it can be done by setting the format for the column.
Insert a row after the heading. Enter char ' in that row for the column A. And then try to import, now the dts should treat all the values as char and import as it is. Worth a try!

- Sekar

X002548
Not Just a Number

15586 Posts

Posted - 2004-08-25 : 15:40:02

quote:
Originally posted by samsekar

I belive it can be done by setting the format for the column.
Insert a row after the heading. Enter char ' in that row for the column A. And then try to import, now the dts should treat all the values as char and import as it is. Worth a try!

- Sekar

Tried that...added a heading column....no luck

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2004-08-25 : 16:00:13

You want weird

in the sheet

0
call
call
call
530
640
call
call
67

in the table after the DTS

call
call
call
NULL
NULL
call
call
NULL
call

(9 row(s) affected)

Both have 9 rows...how did 0 become "call"

Man I really have to make up a list of reasons not to use DTS

Brett

8-)

jsiedliski
Yak Posting Veteran

61 Posts

Posted - 2004-08-25 : 19:03:56

Thank you all for your help.

I have thrown in the towel and added in a step into the procedure to first save the .xls as a .csv. It works when it's a .csv, just could not make it happen with .xls.

Thanks again,

Jack

:)

samsekar
Constraint Violating Yak Guru

437 Posts

Posted - 2004-08-26 : 04:38:02

Are you using datapump task to export. instead use the opendatasource method. It should work! it works for me!

SELECT [Value]FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source=\\Servername\foldername\filename.xls; Extended Properties=''Excel 8.0;IMEX=1;''')...[worksheet$]

- Sekar

ATJaguarX
Starting Member

4 Posts

Posted - 2004-09-02 : 12:21:26

My project is a little different where as I am reading data straight from an asp.net application. I am not using a SP. The key here is to set IMEX=1 in your extended properties of your connection string.

Even if you set your registry to ImportMixedTypes=Text, setting IMEX=1 in your connection string guarantees that Mixed Types come back as string.

I was experiencing the same difficulties as posted in this post. I was receiving Null values when I shouldn't have. I refused to try the CSV because I knew there had to be an answer.

I have been able to do this successfully using an Excel spreadsheet and avoiding the CSV all together.

Hopes this helps

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources