How to Read Excel Cells from SQL Server Integration Services

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Dinesh Asanka » How to Read Excel Cells from SQL Server...

28 posts, Page 2 of 3

««1 2 3 »»

How to Read Excel Cells from SQL Server Integration Services

Rate Topic

Display Mode

Topic Options

Author

Message

brian.francis

Posted Wednesday, April 28, 2010 6:00 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, December 06, 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

SQLRNNR

Posted Wednesday, April 28, 2010 10:49 AM

SSCoach

Group: General Forum Members
Last Login: Yesterday @ 10:25 AM
Points: 18,754, Visits: 12,337

Thanks for the article.

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

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

Ian Yates

Posted Wednesday, April 28, 2010 7:53 PM

Ten Centuries

Group: General Forum Members
Last Login: Sunday, September 16, 2012 3:26 AM
Points: 1,038, Visits: 443

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

WayneS

Posted Wednesday, April 28, 2010 9:17 PM

SSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 9:12 AM
Points: 6,370, Visits: 8,235

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

Ian Yates

Posted Wednesday, April 28, 2010 10:19 PM

Ten Centuries

Group: General Forum Members
Last Login: Sunday, September 16, 2012 3:26 AM
Points: 1,038, Visits: 443

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)

If only life were that simple Tongue

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

gary.humphryes

Posted Wednesday, April 28, 2010 10:48 PM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, December 04, 2012 8:38 PM
Points: 4, Visits: 74

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

Gosta Munktell

Posted Thursday, April 29, 2010 7:03 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 1:00 PM
Points: 195, Visits: 1,252

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

Dinesh Asanka

Posted Thursday, April 29, 2010 7:09 AM

SSC Eights!

Group: General Forum Members
Last Login: Friday, February 22, 2013 9:30 PM
Points: 912, Visits: 198

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

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

Post #912800

rmechaber

Posted Thursday, April 29, 2010 1:32 PM

SSChasing Mays

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 7:43 AM
Points: 656, Visits: 2,921

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

warrensb

Posted Thursday, April 29, 2010 2:07 PM

SSC 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 »

28 posts, Page 2 of 3

««1 2 3 »»

Permissions