April 28, 2010 at 10:48 pm
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.
April 29, 2010 at 7:03 am
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
April 29, 2010 at 7:09 am
this is simply and exampel how you import data from excel cells.
My Blog:
April 29, 2010 at 1:32 pm
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
April 29, 2010 at 2:07 pm
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.
April 29, 2010 at 4:28 pm
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.
April 30, 2010 at 12:52 am
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
April 30, 2010 at 6:35 am
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
April 30, 2010 at 11:50 pm
"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
May 4, 2010 at 3:04 am
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.
May 6, 2010 at 7:22 am
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.
May 6, 2010 at 11:32 am
This works brilliantly, thank you so much! It solved a very messy problem for me.
Simone
August 30, 2010 at 9:20 am
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 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply