How to pass an Excel range as an argument to a SQL Server stored Procedure

[Belinda]

Hello All

I want to pass an Excel range data to a SQL Server stored procedure. I
am trying to pass the data in a Excel range in form of some VB array
to a stored procedure.

Can you kindly provide a simple sample code in VBA and in T-SQL stored
proc how I can do this.

Thanks
Belinda

 

[Bob Phillips]

Belinda,

Are you reading an Excel worksheet? If so, this is usually like

SELECT * FROM [Sheet1$];

If you want to get a specific range, it is best to create a workbook name
for that range, and then it acts as a pseudo-table

SELECT * FROM myExcelRange;
--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Wayne Snyder]

In order to do as Bob suggests, you must add the Excel spreadsheet as a
linked server ( this is doc'd in books on line.). THen use the 4 part name

select * from myexcel...namedrange


--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)

I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org

 

[Belinda]

Wayne & Bob

Many thanks for your tip on reading a Excel range. Please note I have
a number of users using Excel and will need to writeback to SQL Server
creating a linked server using OpenDataSource will need the Excel file
to be on the server where SQL Server is running. So that means I must
ensure all these Excel updates from Clients must be saved onto a Excel
file on the SQL Server or on a network share to be available to SQL
Server this will greatly reduce concurrency of users running this
Excel to SQL Server writeback app and also they would need a network
access to a shared drive or to the server where SQL Server is located.
I would like to read the range in VBA and pass it to a stored proc
through ADO that way my application will have concurrency and
scalability.

Can you please suggest how I can pass a Excel range from VBA to a
T-SQL stored proc.

Thanks
Belinda

 

[Bob Phillips]

Belinda,

What does the range hold, a set of dates, text, numbers, or what? What does
the SP expect, one at a time, an array? I have an example of reading from a
Sybase database using SPs, SQL Server uses Transact-SQL same as Sybase,
writing would be the same principle. Only change would be the connection
string.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[onedaywhen]

I don't think you can pass an array as a parameter of a stored
procedure. The usual advice is to use a delimited string i.e.
concatenate the range values using appropriate characters as
delimiters for column and row e.g.

"a1,b1,c1;a2,b2,c2;a3,b3,c3"

where , delimits columns and ; delimits rows (assumes there are no ,
nor ; in your data). Your stored proc should then parse the string to
retireve the cell values.

 

[George Hutto]

Can I do the reverse? INSERT INTO myexcel...namedrange SELECT x, y, z, FROM
mytable?

 

[onedaywhen]

Can I do the reverse? INSERT INTO myexcel...namedrange SELECT x, y, z, FROM
mytable?

Sure can. Run this query against a MS Jet connection:

INSERT INTO
[Excel 8.0;database=C:\myexcel.xls;].[namedrange]
SELECT x, y, z
FROM
[ODBC;Driver={SQL
Server};SERVER=MYSERVER;DATABASE=MyDB;UID=;Pwd=;].mytable

Note myexcel.xls should be closed to avoid the memory leak bug
(Q319998).

You can do something similar in SQL on the server side but I can't
remember the exact syntax off the top of my head.

--