How to Read Excel Cells from SQL Server Integration Services

  • gary.humphryes

    SSC Enthusiast

    Points: 148

    Hi,

    Yes its so much simpler, but I came across an issue opening Excel on the server in that there is no known way to close the application afterwards and the server is left with a mounting pile of Excel apps every time the import is run that would have to be manually killed off as processes. This may have been because I wasn't referencing the assembly (please let me know if this is the case) but in the end I reverted to pulling in the spreadsheet directly and then obtaining cell values via the ASP page through a dataset.

  • Gosta Munktell

    SSCertifiable

    Points: 5973

    Dinesh

    Your example is interesting. Do you want to use Excel as

    an order entry form? In that case you have to secure that

    the user doesn't insert incorrect data. That can be accomplished with

    dynamic lists of values for each columns Customer name etc. The values

    will be fetched from tables in the database. Also all not used cells should

    be locked.

    Do you have examples in SSIS which will insert and extract data from the same Excel sheet?

    Also I recommend named cells as you can change the layout of the sheet

    without changing the code.

    I include a small code example.

    The database can be hosted on a remote server and the user runs Excel on a local computer. The example is not aimed for have multi user situations.

    The simple code is a part of a Module in Excel (VBA).

    You need a reference to an ActiveX Data Object.

    Sub test()

    Dim conn As New ADODB.Connection

    Dim cmd As New ADODB.Command

    'Remote server

    conn.Provider = "sqloledb"

    conn.Properties("Network Library").Value = "DBMSSOCN"

    conn.Open "Data source=name_of_server;User id=something;Database=something;Password=something"

    Sheets("Order").Select 'Sheet name in the workbook

    Set cmd.ActiveConnection = conn

    cmd.CommandText = "Insert into orders " & _

    "values ('" & Range("CustomerName").Value & "','" & Range("Address").Value & "','" & _

    Range("SalesRep").Value & "'," & _

    Range("Ordernumber").Value & ",'" & Range("OrderDate").Value & "')"

    cmd.Execute

    conn.Close

    Set cmd = Nothing

    Set conn = Nothing

    End Sub

    //Gosta

  • Dinesh Asanka

    SSChampion

    Points: 11058

    this is simply and exampel how you import data from excel cells.




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

  • Rich Mechaber

    SSChampion

    Points: 10935

    Nice article, but I have to say, my only experience with the Primary Interop Assembly (there's a reason that abbreviates to PIA!) with VB.NET was not pleasant:

    Excel process could NOT be terminated gracefully.

    I spent several hours looking online and trying increasingly intrusive methods to kill Excel after the .NET code was done with the Excel.Application. The only thing that worked was to grab a pointer to Excel and kill the process.

    See, e.g., http://www.velocityreviews.com/forums/t86434-p2-excel-interop-from-asp-net-process-cant-be-killed.html. As a comment there says,

    "Two years and a new version of Visual Studio, Office and .Net later, and this thread is still being used !"

    I don't know if you've had any problems like this, but it sounds as if at least one poster, gary.humphryes, did.

    Yours,

    Rich

  • WarrenSB

    Old Hand

    Points: 356

    The issue with excel not closing correctly when accessed programmatically is one of the most common reasons that I have found that server admins don't want devers to utilise the runtime within a server environment.

    Utilising the ACE driver via SSIS currently on Office 12, you need to run the packages in 32 bit mode on 64 bit servers. there is however as discussed previously the Office 14 64 ACE driver. - currently beta but it should be released soon.

    The additional benefit on the ACE driver is that you are writing SQL code and as my example showed previously you can incorporate all the complexities that you want right down to joining you data queries from excel with tables from other sources at the same time.

  • gary.humphryes

    SSC Enthusiast

    Points: 148

    Yes Rich, I spent days trying to crack this and in the end had to cut my losses and battle the SSIS import which in itself proved ridiculously difficult. This is largely do to the inability to set Excel metadata on import which then truncates all text fields to 255 characters. A dark art combination/permutation of IMEX=0/IMEX=1 in the source file connection string and/or padding the source spreadsheet with hidden dummy large text fields eventually got the data in, but as newbie to the asp.net/SSIS environment I hear myself repeatedly asking the same question "why should it ever be this hard?". Not exactly an inspiring start to this environment but at least it's good to know I'm not alone on this one.

  • Koen Verbeeck

    SSC Guru

    Points: 258965

    Don't worry Gary, it's just Excel, the rest of SSIS works (practically) fine. 🙂

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

  • Rich Mechaber

    SSChampion

    Points: 10935

    gary.humphryes (4/29/2010)


    Yes Rich, I spent days trying to crack this and in the end had to cut my losses and battle the SSIS import which in itself proved ridiculously difficult. This is largely do to the inability to set Excel metadata on import which then truncates all text fields to 255 characters. A dark art combination/permutation of IMEX=0/IMEX=1 in the source file connection string and/or padding the source spreadsheet with hidden dummy large text fields eventually got the data in, but as newbie to the asp.net/SSIS environment I hear myself repeatedly asking the same question "why should it ever be this hard?". Not exactly an inspiring start to this environment but at least it's good to know I'm not alone on this one.

    Gary, if you want, I can send you the code I used - mostly downloaded from others - to kill the Excel process. I didn't (and don't) like forcing an application closed like that, but in the end, it worked. Here's another good page summarizing the issue and how to address it:

    http://devcity.net/Articles/239/4/article.aspx.

    I feel your pain about Excel imports. If Excel and SQL Server weren't both MS products, I could understand the mismatches being a problem. MS clearly intended Excel to be a source/destination for SSIS: they didn't do their homework on this, IMO.

    That said, once you get it working, it's a good thing to be able to pull in data from Excel. One of the things I use it for is when I need a department to review data for mass updates: I create an Excel file with all the information they need, I add a new column that they use to tell me the row should or should not be updated, then I pull the file back into a staging table with SSIS so I can update the correct data. Saves everyone a lot of time, and I have their Excel file for CYA 🙂

    Rich

  • Gosta Munktell

    SSCertifiable

    Points: 5973

    "I feel your pain about Excel imports. If Excel and SQL Server weren't both MS products, I could understand the mismatches being a problem. MS clearly intended Excel to be a source/destination for SSIS: they didn't do their homework on this, IMO. "

    I started to use SSIS but gave up. VBA in Excel is easy to learn and you can run an Excel process

    on the client computer fetching data or importing data from/to a remote server. 🙂

    /Gosta

  • sqlusers

    SSCrazy

    Points: 2463

    Hi Dinesh,

    Nice Article!!!

    Is it possible to use cell name rather than B3,B4? Why i am asking is, because if the position of the cell has changed in future then it will not taken values from the excel. If we can place cell name it will be so easy if sometimes cell position gets changed.

    Please advice me if i am wrong.

  • william.ede

    SSC Enthusiast

    Points: 159

    Hi,

    Sorry to be a bit late joining the thread. I do quite a bit with excel and have found the best way to pick arbitrary cell values is to use a script task. I am using VB so I not sure of the syntax for C# for this.

    I use set Option Strict Off, then define the variables I need for the access as objects (this will be latebinding so there aren't tool tips) then get a reference to Excel by using createobject("Excel.Application"). You can get help with code by writing a sub using the objects you want in the Excel VB Editor and pasting it in and modifying it. However you will need to use the full VBA expression to read from the cell as the default won't be recognised (i.e. sheet.range("A1").Value not sheet.range("A1")) but when writing to it the later will work.

    This can be run in a try catch block for error handling. Usually in the catch block I use the code

    If Not wb Is Nothing Then wb.Close(False) (wb being the workbook object). This will close the excel file without saving changes, objects will need to be disposed of properly too. The parameter false is needed to stop excel launching the Do you want to save changes? dialog box.

    Another point, you will need to use the actual integer values for Excel Enumerations where needed as the enumeration won't be recognised in the script task using this method.

    I use Script tasks to get and set specific cell values as well as to loop through each sheet in a workbook to save it as a csv file are then loaded using SSIS. This avoids the Excel ADO problem with columns containing mixed datatypes.

  • sburcombe

    SSCrazy

    Points: 2450

    This works brilliantly, thank you so much! It solved a very messy problem for me.

    Simone

    Simone
  • stuntwoman

    SSC Rookie

    Points: 27

    Hello

    I just can't get this to work when I run the package as a job on the server. I don't seem to be able to add the COM references to the script component, I only get the option of .NET - is this the trouble?

    Can anyone shed any light on this? The reason I am trying it out is I have to pick about 50 cells of data from all over a spreadsheet and this seems like a good method. it works locally anyway!

Viewing 13 posts - 16 through 28 (of 28 total)

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