Microsoft.Jet VS Microsoft.Ace for OPENROWSET

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

Transact-SQL (2008)

Microsoft.Jet VS Microsoft.Ace for OPENROWSET

Author

Topic

umertahir
Posting Yak Master

154 Posts

Posted - 2012-06-12 : 09:30:54

Hi,

I have got the following queries to fetch data from excel spreadsheet:

Fetching data using Microsoft.Ace:



SELECT	*
FROM  OPENROWSET('Microsoft.Ace.OLEDB.12.0',          'Excel 12.0; Database=C:\Fileloader\Landcatch\Imported\LandCatchImport.xls; HDR=no; IMEX=1;', ' SELECT * FROM [Sheet1$]') AS a

Result:


Programme	Site	YearGroup	BlupID	NULL
Scottish	Ormsary	1997	97Sen_0344	NULL

Fetching data using Microsoft.Jet:



SELECT	*
FROM  OPENROWSET('Microsoft.Jet.OLEDB.4.0',          'Excel 8.0; Database=C:\Fileloader\Landcatch\Imported\LandCatchImport.xls; HDR=no; IMEX=1;', ' SELECT * FROM [Sheet1$]') AS a

Result:


Programme	Site	YearGroup	BlupID	PitTag
Scottish	Ormsary	1997	97Sen_0344	rtrveere

Why is Microsoft.ACE not displaying all the columns?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-12 : 16:32:34

hmm? as i see its displaying more details than ACE

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

umertahir
Posting Yak Master

154 Posts

Posted - 2012-06-13 : 04:34:21

quote:
Originally posted by visakh16

hmm? as i see its displaying more details than ACE

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Yes, the last column is being read in as NULL by ACE whereas it does have a text in there.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-06-13 : 06:37:58

Visakh's point seems to be that JET is returning all the data, and ACE is not.

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

umertahir
Posting Yak Master

154 Posts

Posted - 2012-06-13 : 06:48:48

That is exactly what my original question is, that why is JET displaying all but ACE is failing to do so.

quote:
Originally posted by DonAtWork

Visakh's point seems to be that JET is returning all the data, and ACE is not.

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

prett
Posting Yak Master

212 Posts

Posted - 2012-06-13 : 07:24:41

The main difference between Microsoft.Jet.OLEDB.4 & Microsoft.ACE.OLEDB.12 is that the Microsoft.Jet.OLEDB.4.0 provider is used with 32-bit SQL Server for Excel 2003 files & the Microsoft.ACE.OLEDB.12.0 provider is used with 64-bit SQL Server for any Excel files or 32-bit SQL Server for Excel 2007 files.

Ref: http://www.excel-sql-server.com/excel-import-to-sql-server-using-distributed-queries.htm

umertahir
Posting Yak Master

154 Posts

Posted - 2012-06-13 : 10:14:10

So it means that Microsoft.ACE.OLEDB.12.0 should be more efficient than Microsoft.Jet.OLEDB.4.0 which is clearly not the case here.

quote:
Originally posted by prett

The main difference between Microsoft.Jet.OLEDB.4 & Microsoft.ACE.OLEDB.12 is that the Microsoft.Jet.OLEDB.4.0 provider is used with 32-bit SQL Server for Excel 2003 files & the Microsoft.ACE.OLEDB.12.0 provider is used with 64-bit SQL Server for any Excel files or 32-bit SQL Server for Excel 2007 files.

Ref: http://www.excel-sql-server.com/excel-import-to-sql-server-using-distributed-queries.htm

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-13 : 10:35:26

whats the version of your excel file?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

umertahir
Posting Yak Master

154 Posts

Posted - 2012-06-13 : 10:51:44

Microsoft Office Excel 2003 Worksheet

quote:
Originally posted by visakh16

whats the version of your excel file?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

prett
Posting Yak Master

212 Posts

Posted - 2012-06-14 : 02:47:30

The Microsoft.Jet.OLEDB.4.0 Provider is used to import-export data with Microsoft Excel 97-2003 workbooks. Microsoft.ACE.OLEDB.12.0 is used to import-export data with Microsoft Excel 2007/2010 workbooks like *.xlsx, *.xlsm, *.xlb.

You can check detail information from here: http://www.microsoft.com/en-us/download/details.aspx?id=13255

umertahir
Posting Yak Master

154 Posts

Posted - 2012-06-14 : 11:54:54

As there are all sorts of different version of spreadsheet expected to be imported in, so I am using Microsoft.ACE which should work effectively for .xls as well ass .xlsx files.

quote:
Originally posted by prett

The Microsoft.Jet.OLEDB.4.0 Provider is used to import-export data with Microsoft Excel 97-2003 workbooks. Microsoft.ACE.OLEDB.12.0 is used to import-export data with Microsoft Excel 2007/2010 workbooks like *.xlsx, *.xlsm, *.xlb.

You can check detail information from here: http://www.microsoft.com/en-us/download/details.aspx?id=13255

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-14 : 12:31:15

can you try this too?


SELECT	*
FROM  OPENROWSET('Microsoft.Ace.OLEDB.12.0',          'Excel 12.0 Xml; Database=C:\Fileloader\Landcatch\Imported\LandCatchImport.xls; HDR=no; IMEX=1;', ' SELECT * FROM [Sheet1$]') AS a

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

umertahir
Posting Yak Master

154 Posts

Posted - 2012-06-15 : 12:02:13

[code]
SELECT *
FROM OPENROWSET('Microsoft.Ace.OLEDB.12.0', 'Excel 12.0 Xml; Database=C:\Fileloader\Landcatch\Imported\LandCatchImport.xls; HDR=no; IMEX=1;', ' SELECT * FROM [Sheet1$]') AS a
[/code]

Result:

[code]
Programme Site YearGroup BlupID NULL
Scottish Ormsary 1997 97Sen_0344 NULL
[/code]

quote:
Originally posted by visakh16

can you try this too?

[code]
SELECT *
FROM OPENROWSET('Microsoft.Ace.OLEDB.12.0', 'Excel 12.0 Xml; Database=C:\Fileloader\Landcatch\Imported\LandCatchImport.xls; HDR=no; IMEX=1;', ' SELECT * FROM [Sheet1$]') AS a
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources