Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

How to Read Excel Cells from SQL Server Integration Services Expand / Collapse
Author
Message
Posted Wednesday, April 28, 2010 6:00 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, December 6, 2012 7:04 AM
Points: 5, Visits: 183
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.
Post #911815
Posted Wednesday, April 28, 2010 10:49 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 9:02 PM
Points: 21,295, Visits: 14,987
Thanks for the article.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #912192
Posted Wednesday, April 28, 2010 7:53 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 30, 2014 10:08 PM
Points: 1,038, Visits: 444
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)



Post #912478
Posted Wednesday, April 28, 2010 9:17 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:04 PM
Points: 6,582, Visits: 8,861
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
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, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #912499
Posted Wednesday, April 28, 2010 10:19 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 30, 2014 10:08 PM
Points: 1,038, Visits: 444
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.



Post #912518
Posted Wednesday, April 28, 2010 10:48 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 9, 2013 6:15 PM
Points: 4, Visits: 79
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.
Post #912531
Posted Thursday, April 29, 2010 7:03 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, July 27, 2014 1:57 PM
Points: 204, Visits: 1,328
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



Post #912791
Posted Thursday, April 29, 2010 7:09 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Sunday, July 27, 2014 10:38 PM
Points: 912, Visits: 209
this is simply and exampel how you import data from excel cells.








My Blog: http://dineshasanka.spaces.live.com/
Post #912800
Posted Thursday, April 29, 2010 1:32 PM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, July 17, 2014 2:01 PM
Points: 687, Visits: 3,002
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
Post #913221
Posted Thursday, April 29, 2010 2:07 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, June 11, 2012 8:59 PM
Points: 88, Visits: 33
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.
Post #913257
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse