Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Stored Procedure and Excel


Stored Procedure and Excel

Author
Message
wallydelgado
wallydelgado
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
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,....
BigGrin
Chad Crawford
 Chad Crawford
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2438 Visits: 18651
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
Harold Buckner
Harold Buckner
Old Hand
Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)

Group: General Forum Members
Points: 317 Visits: 420
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$]')
ssagrawal
ssagrawal
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 57
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
RandMan-585256
RandMan-585256
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 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
AndrewSQLDBA
AndrewSQLDBA
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1008 Visits: 3427
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
MLetkeman
MLetkeman
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
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
winston.snyder
winston.snyder
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 48
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
Rich Mechaber
Rich Mechaber
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1089 Visits: 3661
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
MLetkeman
MLetkeman
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search