importing from Excel, IMEX ignored

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 2005 Forums

SSIS and Import/Export (2005)

importing from Excel, IMEX ignored

Author

Topic

jackie_sean
Starting Member

4 Posts

Posted - 2009-02-13 : 11:41:08

i am trying to use OPENROWSET (or OPENDATASOURCE) to get data from an excel file located on C:\book1.xsl - Office 2003

To simplify, my excel has one column that has lots of numeric values but also some 2342-23423 for example. When importing data in SQL Server 2005 everything that is not numeric becomes NULL.


SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;IMEX=1;HDR=YES;Database=C:\book1.xls;', 
'select * from [Sheet1$]')

The excel file has 1051 lines. When testing I've tried doing the same thing with less data (i.e.) 10 rows and mixed values on the 'numeric' column and the result is good as expected: I get numeric and non-numeric values properly imported.

I have no idea what am I doing wrong. Any help, please.
----------------------

This is the column that comes with NULL for non numeric values:

KOO
407486406
457737161
441193430
454922543
501429163
511982765
534860697
534860697
457737161
441193430
554712786
233-592992608
233-592992802
454922543
501429163
511982765
534860697
534860697
T0-256256
T0-256256
T0-256256
T0-256256
T0-256256
T0-256256
511982765

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-13 : 12:20:55

Can you do with SSIS package with DataType Conversion Task in Between Excel Source and OLEDB Destination.You can use varchar data type .

jackie_sean
Starting Member

4 Posts

Posted - 2009-02-13 : 12:24:36

Thanks for answering, yes, this is how I initially started with SSIS and doing Conversion but it didn't work at all.
I have posted my column, if you can imported and get the right results please let me know. I am out of ideas.

quote:
Originally posted by sodeep

Can you do with SSIS package with DataType Conversion Task in Between Excel Source and OLEDB Destination.You can use varchar data type .

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-13 : 12:26:05

Here is what you need:
http://www.mssqltips.com/tip.asp?tip=1393

jackie_sean
Starting Member

4 Posts

Posted - 2009-02-13 : 13:13:47

Tried and failed, it's quite different from what I have, in the article it's about unicode to string transformation.
I think my case is different, it's numeric to varchar if I may say.

Actually I have no idea what it is and no idea why it doesn't work.
Appreciate your help sodeep.

If anyone has other ideas please help.

quote:
Originally posted by sodeep

Here is what you need:
http://www.mssqltips.com/tip.asp?tip=1393

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-13 : 16:43:21

What datatype you used for SQL Server in Data Conversion Task?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources