InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 14:31:19 GMT20http://www.sqlservercentral.com/Forums/Topic911600-144-1.aspxHelloI 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!Mon, 30 Aug 2010 09:20:49 GMTstuntwomanhttp://www.sqlservercentral.com/Forums/Topic911600-144-1.aspxThis works brilliantly, thank you so much! It solved a very messy problem for me.SimoneThu, 06 May 2010 11:32:37 GMTsburcombehttp://www.sqlservercentral.com/Forums/Topic911600-144-1.aspxHi,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.Thu, 06 May 2010 07:22:26 GMTwilliam.edehttp://www.sqlservercentral.com/Forums/Topic911600-144-1.aspxHi 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.Tue, 04 May 2010 03:04:28 GMTsqlusershttp://www.sqlservercentral.com/Forums/Topic911600-144-1.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. "I started to use SSIS but gave up. VBA in Excel is easy to learn and you can run an Excel processon the client computer fetching data or importing data from/to a remote server. :-)/GostaFri, 30 Apr 2010 23:50:58 GMTGosta Munktellhttp://www.sqlservercentral.com/Forums/Topic911600-144-1.aspx[quote][b]gary.humphryes (4/29/2010)[/b][hr]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.[/quote]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:[url]http://devcity.net/Articles/239/4/article.aspx[/url].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 :-)RichFri, 30 Apr 2010 06:35:18 GMTrmechaberhttp://www.sqlservercentral.com/Forums/Topic911600-144-1.aspxDon't worry Gary, it's just Excel, the rest of SSIS works (practically) fine. :-)Fri, 30 Apr 2010 00:52:08 GMTKoen Verbeeckhttp://www.sqlservercentral.com/Forums/Topic911600-144-1.aspxYes 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.Thu, 29 Apr 2010 16:28:21 GMTgary.humphryeshttp://www.sqlservercentral.com/Forums/Topic911600-144-1.aspxThe 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.Thu, 29 Apr 2010 14:07:34 GMTwarrensbhttp://www.sqlservercentral.com/Forums/Topic911600-144-1.aspxNice 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 [u]only[/u] thing that worked was to grab a pointer to Excel and kill the process.See, e.g., [url]http://www.velocityreviews.com/forums/t86434-p2-excel-interop-from-asp-net-process-cant-be-killed.html[/url]. 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,RichThu, 29 Apr 2010 13:32:44 GMTrmechaberhttp://www.sqlservercentral.com/Forums/Topic911600-144-1.aspxthis is simply and exampel how you import data from excel cells.Thu, 29 Apr 2010 07:09:27 GMTDinesh Asankahttp://www.sqlservercentral.com/Forums/Topic911600-144-1.aspxDineshYour example is interesting. Do you want to use Excel asan order entry form? In that case you have to secure thatthe user doesn't insert incorrect data. That can be accomplished withdynamic lists of values for each columns Customer name etc. The valueswill be fetched from tables in the database. Also all not used cells shouldbe 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 sheetwithout 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.ConnectionDim cmd As New ADODB.Command'Remote serverconn.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 workbookSet cmd.ActiveConnection = conncmd.CommandText = "Insert into orders " & _"values ('" & Range("CustomerName").Value & "','" & Range("Address").Value & "','" & _Range("SalesRep").Value & "'," & _Range("Ordernumber").Value & ",'" & Range("OrderDate").Value & "')"cmd.Executeconn.CloseSet cmd = NothingSet conn = NothingEnd Sub//GostaThu, 29 Apr 2010 07:03:37 GMTGosta Munktellhttp://www.sqlservercentral.com/Forums/Topic911600-144-1.aspxHi,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.Wed, 28 Apr 2010 22:48:15 GMTgary.humphryeshttp://www.sqlservercentral.com/Forums/Topic911600-144-1.aspx[quote][b]WayneS (4/28/2010)[/b][hr][quote][b]Ian Yates (4/28/2010)[/b][hr]Now if only they'd do one for FoxPro....... :) (we link to a number of third party systems that use FoxPro databases)[/quote]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...[/quote]If only life were that simple :-P 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.Wed, 28 Apr 2010 22:19:02 GMTIan Yateshttp://www.sqlservercentral.com/Forums/Topic911600-144-1.aspx[quote][b]Ian Yates (4/28/2010)[/b][hr]Now if only they'd do one for FoxPro....... :) (we link to a number of third party systems that use FoxPro databases)[/quote]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...Wed, 28 Apr 2010 21:17:19 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic911600-144-1.aspxSomeone 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)Wed, 28 Apr 2010 19:53:13 GMTIan Yateshttp://www.sqlservercentral.com/Forums/Topic911600-144-1.aspxThanks for the article.Wed, 28 Apr 2010 10:49:22 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic911600-144-1.aspxThanks 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.Wed, 28 Apr 2010 06:00:50 GMTbrian.francishttp://www.sqlservercentral.com/Forums/Topic911600-144-1.aspxThat's excellent - thanks for posting - look forward to trying it out.Wed, 28 Apr 2010 03:23:01 GMTjmiller 72604http://www.sqlservercentral.com/Forums/Topic911600-144-1.aspxyes this is working in 64 bit. in fact, all my development are happening in 64 bitWed, 28 Apr 2010 03:20:28 GMTDinesh Asankahttp://www.sqlservercentral.com/Forums/Topic911600-144-1.aspxHi - 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.Wed, 28 Apr 2010 03:12:53 GMTjmiller 72604http://www.sqlservercentral.com/Forums/Topic911600-144-1.aspxHi There,Nice article, I will look at using this in futurepreviously, 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 [code="sql"]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) across 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[/code]RegardsWarrenWed, 28 Apr 2010 02:18:52 GMTwarrensbhttp://www.sqlservercentral.com/Forums/Topic911600-144-1.aspxThank 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.//GostaWed, 28 Apr 2010 02:13:11 GMTGosta Munktellhttp://www.sqlservercentral.com/Forums/Topic911600-144-1.aspxHi 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. RgdsDanielWed, 28 Apr 2010 02:07:16 GMTdaamruthhttp://www.sqlservercentral.com/Forums/Topic911600-144-1.aspxHi 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.Wed, 28 Apr 2010 02:03:53 GMTsarc007http://www.sqlservercentral.com/Forums/Topic911600-144-1.aspxThanks 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)Wed, 28 Apr 2010 01:51:07 GMTSQL-DBAhttp://www.sqlservercentral.com/Forums/Topic911600-144-1.aspxNice article. I will never ever use the first option as it is too rigid, but the second one is very interesting and very promising.Wed, 28 Apr 2010 00:37:02 GMTKoen Verbeeckhttp://www.sqlservercentral.com/Forums/Topic911600-144-1.aspxComments posted to this topic are about the item [B]<A HREF="/articles/Integration+Services+(SSIS)/70024/">How to Read Excel Cells from SQL Server Integration Services</A>[/B]Tue, 27 Apr 2010 21:48:54 GMTDinesh Asanka