How to Read Excel Cells from SQL Server Integration Services

  • Dinesh Asanka

    SSChampion

    Points: 11058

    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/

  • Koen Verbeeck

    SSC Guru

    Points: 258941

    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: 3004

    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

    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

    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: 5973

    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

    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

    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

    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

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

  • brian.francis

    SSC Enthusiast

    Points: 125

    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: 281210

    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

    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: 95341

    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

    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.

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

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