SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


My favorite way to access Excel files


My favorite way to access Excel files

Author
Message
GabyYYZ
GabyYYZ
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1793 Visits: 2336
Comments posted to this topic are about the item My favorite way to access Excel files

Gaby
________________________________________________________________
"In theory, theory and practice are the same. In practice, they are not."
- Albert Einstein

m.schmidbauer
m.schmidbauer
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 55
Dear Gaby,

i know this really comfortable feature and i use it a lot.
Good to know that it also works with SQL 2008 since we are actually using SQL2005.
But as far is i know it does not work under 64 Bit Environments because MSDASQL is not available there.
Is this true ?
Do you know a workaround so that spreedsheets still can be queried directly with Transact SQL under 64-Bit?

Best Regards
Markus
GabyYYZ
GabyYYZ
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1793 Visits: 2336
m.schmidbauer (2/4/2009)
Dear Gaby,

i know this really comfortable feature and i use it a lot.
Good to know that it also works with SQL 2008 since we are actually using SQL2005.
But as far is i know it does not work under 64 Bit Environments because MSDASQL is not available there.
Is this true ?
Do you know a workaround so that spreedsheets still can be queried directly with Transact SQL under 64-Bit?

Best Regards
Markus


To be honest, I'm not sure. We don't yet have any production servers in SQL 2008 yet, was mainly using the management studio portion. Anyone out there know the details on this?

Gaby
________________________________________________________________
"In theory, theory and practice are the same. In practice, they are not."
- Albert Einstein

clou-951938
clou-951938
Valued Member
Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)

Group: General Forum Members
Points: 54 Visits: 72
That's right. The 64 bit system won't support Jet 4.0. I am quite sufferring from that problem too. So far, I couldn't any reliable solution to work around it yet. You can try a group of system stored procedures such as sp_OACreate, sp_OAMethod, sp_OAGetProperty and sp_OASetProperty. But, trust me it's difficult to use if you are not very familiar with VBA programming.
BrianAltmann
BrianAltmann
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1183 Visits: 195
We recently ran into this problem too.
We reviewed some posts suggesting you can develop SSIS packages and run them in a 32 bit instance of the SSIS engine in the 64-bit platform (also installing the 32-bit Jet 4.0 engine, of course).
But given the policy constraints on our production servers we went with the simpler solution of exporting to .csv and bulk inserting the data. In our case, we were reading the data into temporary tables at the beginning of each procedure so this was not such a big change.

Brian
clou-951938
clou-951938
Valued Member
Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)

Group: General Forum Members
Points: 54 Visits: 72
If the uploaded raw files can be transferred to .csv or .psv files, it should be a good solution. I use SSIS to handle many complicated .csv and .psv uploading procedures very successfully. Yes, in many cases, you can use 32 bit version DTExec.exe from 64 bit environment to handle the simple Excel spreadsheets (a pure tabulate format) by using SSIS. But it's not easy to handle a multi-sheets and free form format (for example, it contails titles, header or some formulated cells) Excel files. You need to access the data in a specific sheet and range in that .xls files. I think the CLR and sp_OA series OLEDB automation system stored procedures could be the ultimate solutions, if you need to work on the Excel file in very details such as sheet by sheet, range by range and cell by cell.
Jacob Luebbers
Jacob Luebbers
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1350 Visits: 1215
Gaby,
You may also want to amend the article to include the Excel 2007 methodology. Eg. using the Microsoft.ACE.OLEDB.12.0 provider with the Office 2007 System Driver installed.

And re: the 64-bit platform question, there is no neat way to use Jet of any version on an x64/ia64 instance. Either do the import from application code using the same OLEDB driver running on an x86 box or a seperate SQL Server instance on an x86 box. In a pinch you could probably run 32-bit application code on the x64 SQL server as it has access to 32-bit OLEDB drivers.

Regards,

Jacob
GabyYYZ
GabyYYZ
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1793 Visits: 2336
Jacob Luebbers (2/19/2009)
Gaby,
You may also want to amend the article to include the Excel 2007 methodology. Eg. using the Microsoft.ACE.OLEDB.12.0 provider with the Office 2007 System Driver installed.


Thanks Jacob...haven't used Excel 2007 much yet but will have to try that. Anyone out there tried it with 2007 yet out of curiousity and, except for the driver modification, did it behave?

Gaby
________________________________________________________________
"In theory, theory and practice are the same. In practice, they are not."
- Albert Einstein

Manie Verster
Manie Verster
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2267 Visits: 1030
Gaby, this would be a very nice feature if only I can use it. I am on a computer with Vista Home Basic, Office 2007 and SQL Server 2008 and I get the following error:

The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error. The provider did not give any information about the error.


can anyone please help?

:-PManie Verster
Developer
Johannesburg
South Africa

I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)
gsc_dba
gsc_dba
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2297 Visits: 2040
Please read this MS article: http://support.microsoft.com/kb/814398

I experienced this same error and the article helped solve the problem.

gsc_dba
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search