one of the Excel Row is coming out NULL

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)

one of the Excel Row is coming out NULL

Author

Topic

umertahir
Posting Yak Master

154 Posts

Posted - 2009-04-02 : 06:01:12

Hi All,

I have a linked server established to read data from Ms Excel (.xls) file.

The data in excel file is as follows:
Columns: A B C D E F G H ......
Row1: Langhill Herd - Calving Records
Row2:
Row3: Freeze_Brand Ear_Tag Lact_No Calving_Date Calving_Wgt
Row4: 1703 602027 2 30-Jan-09 652

When I fetch the data into SQL using the following query it NULLs out some of the cells from Row3:


SELECT *
FROM  FileUploadLocationForLanghill...Sheet1$ AS Sheet1$_1

Result:
Langhill Herd - Calving Records F2 F3 F4 F5
------------------------------- -- -- -- --
NULL NULL NULL NULL NULL
NULL Ear_Tag NULL NULL NULL
1703 602027 2 2009-01-30 00:00:00.000 652

Code of my linked server is:


/****** Object:  LinkedServer [FileUploadLocationForLanghill]    Script Date: 04/02/2009 11:01:25 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'FileUploadLocationForLanghill', @srvproduct=N'Excel', @provider=N'Microsoft.Jet.OLEDB.4.0', @datasrc=N'D:\EvaluationsUnit\Langhill\LinkedServerFile\LinkedServerFile.xls', @provstr=N'Excel 8.0; IMEX=0'
 /* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'FileUploadLocationForLanghill',@useself=N'False',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

GO
EXEC master.dbo.sp_serveroption @server=N'FileUploadLocationForLanghill', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'FileUploadLocationForLanghill', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'FileUploadLocationForLanghill', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'FileUploadLocationForLanghill', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'FileUploadLocationForLanghill', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'FileUploadLocationForLanghill', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'FileUploadLocationForLanghill', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'FileUploadLocationForLanghill', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'FileUploadLocationForLanghill', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'FileUploadLocationForLanghill', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'FileUploadLocationForLanghill', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'FileUploadLocationForLanghill', @optname=N'use remote collation', @optvalue=N'true'

umertahir
Posting Yak Master

154 Posts

Posted - 2009-04-02 : 09:09:22

Ok I have found a work around to the problem:

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.

If you use OPENROWSET query or linked server the idea is to use add the option IMEX=1. However, IMEX = 0 is used when you want to write something back to the Excel file.

OPENROWSET Query:

select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;database=d:\EvaluationsUnit\Langhill\LinkedServerFile\LinkedServerFile.xls; HDR=NO; IMEX=1;',' select * from [Sheet1$]') as a

Linked Server Code:

/****** Object:  LinkedServer [FileUploadLocationForLanghillTest]    Script Date: 04/02/2009 14:08:22 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'FileUploadLocationForLanghillTest', @srvproduct=N'Excel', @provider=N'Microsoft.Jet.OLEDB.4.0', @datasrc=N'D:\EvaluationsUnit\Langhill\LinkedServerFile\LinkedServerFile.xls', @provstr=N'Excel 8.0; IMEX=1'
 /* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'FileUploadLocationForLanghillTest',@useself=N'False',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

GO
EXEC master.dbo.sp_serveroption @server=N'FileUploadLocationForLanghillTest', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'FileUploadLocationForLanghillTest', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'FileUploadLocationForLanghillTest', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'FileUploadLocationForLanghillTest', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'FileUploadLocationForLanghillTest', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'FileUploadLocationForLanghillTest', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'FileUploadLocationForLanghillTest', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'FileUploadLocationForLanghillTest', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'FileUploadLocationForLanghillTest', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'FileUploadLocationForLanghillTest', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'FileUploadLocationForLanghillTest', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'FileUploadLocationForLanghillTest', @optname=N'use remote collation', @optvalue=N'true'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources