How to Read Excel Cells from SQL Server Integration Services

Question

How to Read Excel Cells from SQL Server Integration Services

Dinesh Asanka

SSChampion

Points: 11058
More actions
April 27, 2010 at 9:48 pm

#232115

Comments posted to this topic are about the item How to Read Excel Cells from SQL Server Integration Services

My Blog: http://dineshasanka.spaces.live.com/

Viewing 15 posts - 1 through 15 (of 27 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 1

Nice article. I will never ever use the first option as it is too rigid, but the second one is very interesting and very promising.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

SQL-DBA Hall of Fame Points: 3051 More actions · Answer 2

Thanks for the article. It seems way too common to get data 1) in Excel (ugh) and 2) completely formatted with no usable way to extract the data. I usually tell users to put the data in columnar format. Now I guess there are other options, but I'm not sure I am going to tell them that...;O)

sarc007 SSC Enthusiast Points: 110 More actions · Answer 3

Hi I have a question what if there is picture in the excel file how do you pick the picture from the file. Can you shed some light on it.

daamruth Mr or Mrs. 500 Points: 537 More actions · Answer 4

Hi there,

nice article. Will bookmark it 😉

The only thing that comes up to my mind is that using excel on the server even through the assembly is never a good idea, since there is no way to debug and errors cannot be seen. Instead use something like syncfusion or so.

Rgds

Daniel

Gosta Munktell SSCertifiable Points: 6004 More actions · Answer 5

Thank you Dinesh.

If it comes to read individual cells I think it is much easier to use Excel,

VBA, ADO and SQL . I do have of code examples if anybody is interested.

//Gosta

WarrenSB Old Hand Points: 356 More actions · Answer 6

Hi There,

Nice article, I will look at using this in future

previously, I have used the ACE driver to access Excel in the past with pivots to switch the data between rows and columns, the benefit of using the ACE driver is that you can link the queries etc to other SQL statements. also you only need to install the ACE driver on the server and not the Excel runtime - an element that many of the server admins that I have worked with didn't want on their servers.

Also with the ACE or OLEDB jet providers, you can specify specific cells and tabs within a spreadsheet.

below is my query that returns the same result as the one the article

select a.[Customer Name],

a.[Address],

a.[Sales Rep],

b.[Order Number],

b.[Order Date]

from (

select [Customer Name],

[Address],

[Sales Rep]

from (SELECT *

FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',

'Excel 12.0 Xml;HDR=No;Database=C:\Projects\KPI Demo\IT 2009 Tester.xls;IMEX=1',

'SELECT * FROM [Department Details$A3:B5]')) as sourcetable

PIVOT

(MAX(F2)

for F1 in ([Customer Name],[Address],[Sales Rep])

) as PivotTable) a

cross join (

select null as [Customer Name],

[Order Number],

[Order Date]

from (SELECT *

FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',

'Excel 12.0 Xml;HDR=No;Database=C:\Projects\KPI Demo\IT 2009 Tester.xls;IMEX=1',

'SELECT * FROM [Department Details$D3:E4]')) as sourcetable

PIVOT

(MAX(F2)

for f1 in ([Order Number],[Order Date])

) as PivotTable) b

Regards

Warren

jmiller 72604 SSC Veteran Points: 233 More actions · Answer 7

Hi - does this work with 64-bit editions of SQL Server? I found much to my frustration that it wasn't possible to set up a spreadsheet as a linked server in SQL2005-64, and my investigation at the time only found the solution of going via a 32-bit SQL Server installation, which isn't great.

Just wondered if this could be another way round the problem.

Dinesh Asanka SSChampion Points: 11058 More actions · Answer 8

yes this is working in 64 bit. in fact, all my development are happening in 64 bit

My Blog: http://dineshasanka.spaces.live.com/

jmiller 72604 SSC Veteran Points: 233 More actions · Answer 9

That's excellent - thanks for posting - look forward to trying it out.

brian.francis SSC Enthusiast Points: 125 More actions · Answer 10

Thanks for the info. If you have control over the spreadsheet design, it works well to put in a hidden sheet with formulas that put the data into columns. But it's always nice to know different ways to skin a cat.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 11

Thanks for the article.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Ian Yates SSCoach Points: 19738 More actions · Answer 12

Someone asked about 64-bit...

Linked servers from SQL Server still won't work if SQL is 64-bit as you're trying to have it use 32-bit OLEDB/ODBC drivers. There's no thunking like there was with Win32/16.

SSIS is the nice workaround as, even on a 64-bit machine, you can run your packages using a 32-bit runtime if desired. This allows for connections to FoxPro databases, etc.

Actually, read http://blogs.msdn.com/psssql/archive/2010/01/21/how-to-get-a-x64-version-of-jet.aspx as it gives a bit of an overview and a link to the new Office 2010-based ACE driver that is x64 (the link is to the beta - I guess there's now an RTM version available?). That ACE driver should let you access Access and Excel files, although I haven't tried it.

Now if only they'd do one for FoxPro....... 🙂 (we link to a number of third party systems that use FoxPro databases)

WayneS SSC Guru Points: 95409 More actions · Answer 13

Ian Yates (4/28/2010)
Now if only they'd do one for FoxPro....... 🙂 (we link to a number of third party systems that use FoxPro databases)

You do know that a FoxPro dbf is just a flat file with a header? It's been a decade since I've worked with it, but I remember the company having problems exporting data from a mainframe computer into a FoxPro dbf, and having issues. We ended up exporting it into a flat file, and appending the header.

So, if you can strip off the header, you can then just do a bulk import...

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Ian Yates SSCoach Points: 19738 More actions · Answer 14

WayneS (4/28/2010)
Ian Yates (4/28/2010)
Now if only they'd do one for FoxPro....... 🙂 (we link to a number of third party systems that use FoxPro databases)
You do know that a FoxPro dbf is just a flat file with a header? It's been a decade since I've worked with it, but I remember the company having problems exporting data from a mainframe computer into a FoxPro dbf, and having issues. We ended up exporting it into a flat file, and appending the header.
So, if you can strip off the header, you can then just do a bulk import...

If only life were that simple 😛 Thanks for the suggestion, but we need to read from several tables at the same time, usually picking out just a couple of records (there's a trigger initiated by the user which tells us which record to examine) and bog standard SQL queries are easier to debug. I don't want to have to parse the index files, the blob files (*.fpt I think), etc to read out different bits of data.

We also do frequent full imports as well so your approach could work there, but I'd probably just use SSIS in that case if linked servers weren't available.

Someone had suggested that the Sybase Advantage Anywhere driver could read FoxPro files. It seems that it can, but it isn't x64 either and I never got it to work anyway.