Null values importing from EXCELL file

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)

Null values importing from EXCELL file

Author

Topic

ds9
Starting Member

38 Posts

Posted - 2007-03-09 : 09:09:06

Hi there

Hi have a worksheet with one column that has fields with numbers and fields with text. I wish to import that data into a table (the destination column is Varchar type).
After creating the XLS connection type, I check the preview pane for the worksheet, the desired column is only showing the numbers (the rows with text are blank). Do you know what can I do to see all rows?

If I save the xls file as csv, and create a text type connection it shows all rows (numberas and text), but I wanted to keep the source file as xls.

Thanks
ds9

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-09 : 09:12:24

Change IMEX value.

Peter Larsson
Helsingborg, Sweden

ds9
Starting Member

38 Posts

Posted - 2007-03-09 : 15:18:46

Hi Peter

.... what is IMEX value? :-(

thanks

sshelper
Posting Yak Master

216 Posts

Posted - 2007-03-09 : 16:12:16

IMEX is one of the Extended Properties that you set in your connection string. Here's an example of the connection string for Excel:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1";

"IMEX=1;" tells the driver to always read "intermixed" (numbers, dates, strings etc) data columns as text.

SQL Server Helper
http://www.sql-server-helper.com

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-10 : 04:01:33

Also discussed here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926 (10 pages long !)

Peter Larsson
Helsingborg, Sweden

ds9
Starting Member

38 Posts

Posted - 2007-03-12 : 06:54:16

Thanks for the feedback. Went through th 11 pages long thread. Learned a lot already :-)
I'm still a little bit newbie to understand it all though.

I still don't know how to change the IMEX value in the DTS. I can change it if I save the dts as VBA file, but then I can't load it back to Enterprise Manager...

Again
Thanks for your help
ds9

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources