variable file name in openrowset

Question

variable file name in openrowset

jeandlauro

SSC Eights!

Points: 807
More actions
January 24, 2014 at 10:58 am

#281632

does anyone know if it's possible to pull from a file with a variable name?
the file name is: DUMP_DAILY_SLA_ROLLING_TIMELINES_DLR_20140124
where the date changes daily.
I have
declare @curdate nvarchar(8)
set @curdate = FORMAT(sysdatetime(),'yyyyMMdd')
print @curdate;
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 8.0;Database=c:\users\JL04638\desktop\Data_Dump_Local\DUMP_DAILY_SLA_ROLLING_TIMELINES_DLR_%@curdate%.xls',
'SELECT * FROM [DUMP_DAILY_SLA_ROLLING_TIMELINE$]');

Viewing 6 posts - 1 through 5 (of 5 total)

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

pietlinden SSC Guru Points: 63198 More actions · Answer 1

I don't see any reason why you couldn't create a @FileName variable or similar and just build the string in pieces, and then use it in a loop to process the contents of a directory... You could do it in SSIS, but that's overkill.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 2

In other words, the OPENROWSET that you've created will need to be converted to Dynamic SQL.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

jeandlauro SSC Eights! Points: 807 More actions · Answer 3

what do you mean exactly. I can't figure out why what i wrote isn't working. plz hlp.

pietlinden SSC Guru Points: 63198 More actions · Answer 4

Does this work? The SQL returns what I want it to...

DECLARE @sql varchar(1000); -- just a variable to stuff the completed SQL statement into.

declare @curDate nvarchar(8)

declare @filePath VARCHAR(500)

set @curDate = FORMAT(sysdatetime(),'yyyyMMdd')

set @filePath = 'c:\users\JL04638\desktop\Data_Dump_Local\DUMP_DAILY_SLA_ROLLING_TIMELINES_DLR_' + @curDate + '.xls'

set @sql = 'SELECT * FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'',''Excel 8.0;Database=' + @filePath + '''' + ';' + '''' +

'SELECT * FROM [DUMP_DAILY_SLA_ROLLING_TIMELINE$]' + '''' + ');'

print @sql;

When I run mine, I get this:

SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 8.0;Database=c:\users\JL04638\desktop\Data_Dump_Local\DUMP_DAILY_SLA_ROLLING_TIMELINES_DLR_20140128.xls';'SELECT * FROM [DUMP_DAILY_SLA_ROLLING_TIMELINE$]');

I guess I'm really rusty at T-SQL, because this took me several tries. (In other words, unless you're really good at string manipulation in T-SQL, this isn't that easy. Or if you're new to T-SQL.) Takes a while to get used to.

If this was something I had to do all the time, I might write a function to generate the string - only because it becomes self-contained and I can forget how to use it. (Of course, I would have to break off the customizable pieces and add variables for them...)

jeandlauro SSC Eights! Points: 807 More actions · Answer 5

thanks for your help pietlinden...

still can't seem to get it to work so I just wrote a batch file to delete the files date in the title each day in the actual folder.