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

Stored Procedure and Excel Expand / Collapse
Author
Message
Posted Friday, August 1, 2008 3:56 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, April 6, 2009 7:35 PM
Points: 10, Visits: 51
I need to know how to make a stored procedure that can read excel file and then load the data of the excel to a table..


Thanks, I am only just a newbie,....
:D
Post #544952
Posted Friday, August 1, 2008 5:01 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, December 19, 2014 4:08 PM
Points: 2,396, Visits: 18,069
There are a few options, but using a SP may not be the best for you. Take a look at this article: http://support.microsoft.com/kb/321686. If the Excel file is always the same one and it needs to be updated, it may be best to set up a dataconnection in Excel where the data flows back and forth dynamically. If it's only going one way (to MSSQL) but the file is always the same and it needs to be triggered from the SQL Server, a linked server or distributed query might work well. If you need to sync with several Excel files, give SSIS a look.

Hope this helps!
Chad
Post #545540
Posted Wednesday, December 2, 2009 5:52 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 10:01 AM
Points: 311, Visits: 399
Will this work for you?

select *
into SQLServerTable FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;HDR=YES',
'SELECT * FROM [Sheet1$]')
Post #827364
Posted Wednesday, December 2, 2009 11:27 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, November 20, 2014 2:42 AM
Points: 15, Visits: 54
before running this query you need to configure with the following query
sp_configure 'show advanced options', 1
reconfigure
go
sp_configure 'Ad Hoc Distributed Queries', 1
reconfigure

--sushil
Post #827954
Posted Thursday, December 3, 2009 8:06 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, February 24, 2010 7:54 AM
Points: 24, Visits: 389
before running this query you need to configure with the following query
sp_configure 'show advanced options', 1
reconfigure
go
sp_configure 'Ad Hoc Distributed Queries', 1
reconfigure



Can you provide an explanation for running the procedures you mention.
I find it beneficial to understand why there is a need to do so.

Thanks!

EDITED for clarity



"There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers
Post #828188
Posted Thursday, December 3, 2009 9:19 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: Friday, November 7, 2014 2:00 PM
Points: 979, Visits: 3,364
To keep things really simple, I would create a simple SSIS package. That way I could schedule it to fire when needed, or even have it fire manually from within SSMS, or even from a web page.

No need to get too deep with it, that is exactly what ETL, and SSIS are designed to do.

Andrew SQLDBA
Post #828296
Posted Saturday, August 7, 2010 10:29 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 16, 2011 11:39 AM
Points: 2, Visits: 14
Hi.

Jumping in because I have a similar requirement. I need to import data from an Excel spreadsheet. It will always have the same name and path. The data needs to be brought into SQL Server 2005. But the import gets triggered from a command button on a form in Access 2007 project (adp linked to SQL Server).

I don't know anything about linked servers, and prefer to avoid it, since this program will be deployed to various clients, and I have no control over their settings.

My approach is to write vba code that will run the stored procedure (with connection, not docmd.....).

My questions are:

Is the syntax for Excel (5.0, 8.0, 11.0 whatever) truly version specific? e.g. if the spreadsheet is created in Excel 2007 and saved as an earlier version, will this still work?

At the end of the stored proc, I'd like to run sp_configure again and change the settings back to the default (where adHoc is not allowed). Do I just change the 1 to 0 on those lines?

Thanks so much for the info on this, I've been hunting for two hours for this info.

Molly
Post #965624
Posted Sunday, August 15, 2010 11:31 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, December 31, 2012 6:13 AM
Points: 15, Visits: 47
Hi Molly,

Search over at www.ozgrid.com/forum for ado (ActiveX Data Objects) by XLDennis (Dennis Wallentin)
I believe all of his code samples are written to access Jet 4 database, but you should be able to easily change that for SQL

Post back if you need help
Best,
Winston
Post #969546
Posted Friday, August 27, 2010 7:21 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, November 26, 2014 9:31 AM
Points: 717, Visits: 3,037
I often use something along the lines of this to read in Excel data. The IMEX=1 trick will save you a lot of grief from SQL incorrectly "guessing" what type of data to read in from Excel.

/*
-- NOTES
-- N.B. "IMEX=1" in connection string for OPENROWSET prevents this problem: If 1st row in XL file is blank, then ALL rows will have NULL.
*/

USE dev;
GO

SELECT *
FROM OpenRowset(
'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=\\myserver\SQLServerFiles\XL-Import-Test.xls',
'select * from ReportData' -- Where ReportData is a Named Range in XL
) -- will be called F1, F2, F3, ... if there are no headers.

--If it's just a sheet, try

SELECT *
FROM OpenRowset(
'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=\\myserver\SQLServerFiles\XL-Import-Test.xls',
'select * from [Sheet1$]'
)



Per BOL: "OPENROWSET can be used to access remote data from OLE DB data sources only when the DisallowAdhocAccess registry option is explicitly set to 0 for the specified provider, and the Ad Hoc Distributed Queries advanced configuration option is enabled. When these options are not set, the default behavior does not allow for ad hoc access."

You can turn this on with the Surface Area Configuration Manager or with the sp_configure command, per ssagrawal's post above.

This should get you started,
Rich
Post #976367
Posted Sunday, August 29, 2010 12:11 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 16, 2011 11:39 AM
Points: 2, Visits: 14
Hey, all.

Just back from holidays and catching up.

Winston, I've added the site to my resources list, I can just see surfing for additional tips.

And Rich, great code. I've copied it locally, and it sure came in handy.

Looks like we have a working solution, so thanks for all the help.

Cheers,

M
Post #977094
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse