Using a Function to Determine an Excel Worksheet Source

Question

Post reply

Using a Function to Determine an Excel Worksheet Source

cdun2

SSCertifiable

Points: 7893
More actions
February 26, 2007 at 8:56 am

#115992

Hello,
I have a linked server to an Excel Workbook where there are twelve Worksheets, each of which is named after a month of the year. In order to pull data from any one of the Worksheets, I would create a query (sample) as follows;
SELECT * FROM OPENQUERY(Charlie_file, 'SELECT * FROM [Feb$]')
Is there any way, either through a proc or table function that I could, say, send in a parameter representing the current month (MONTH(GETDATE())), and have that determine the Worksheet that the data is pulled from?
One way I can think of would be to take in the 'Month' parameter, and use IF to make twelve tests, each of which would contain a SELECT statement that pulled from one of the twelve sheets.
There would be a lot of repitition in that solution. Does anyone have a better idea?
Thank you for your help!
CSDunn

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Ken McKelvey SSCoach Points: 18856 More actions · Answer 1

You could try something like:

DECLARE @Query varchar(255)

SET @Query = 'SELECT * FROM [' + LEFT(DATENAME(month, GETDATE()), 3) + '$]'

SELECT * FROM OPENQUERY(Charlie_file, @Query)

cdun2 SSCertifiable Points: 7893 More actions · Answer 2

Thanks for your response. I tried to run that code and got a syntax error. I checked BOL, for 'OPENQUERY', and the fuction cannot take variables for its arguments.

I tried this way;

declare

@MonthVariable varchar(4)

set

@MonthVariable = LEFT(DATENAME(month, GETDATE()), 3)

Select

* from OpenQuery (Charlie_file, 'SELECT * FROM [' + @MonthVariable + '$]')

No luck. Am I stuck with twelve test in a proc?

Thanks again!

cdun2 SSCertifiable Points: 7893 More actions · Answer 3

I was able to come up with something using the Dynamic Properties Tranform in DTS. I created a table with twelve rows that contained a column of three letter identifiers for the month, and another column that contained the SELECT statement to be configured in the SELECT statement of the transform. Then I used a sql statement in the dynamic properties to pull the correct sql statement from the table, and assigned that to the sql statement in the transform.

Does that make sense?

Anyway, it seems to work well.

CSDunn