January 24, 2014 at 10:58 am
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$]');
January 26, 2014 at 2:50 pm
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.
January 26, 2014 at 7:58 pm
In other words, the OPENROWSET that you've created will need to be converted to Dynamic SQL.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 28, 2014 at 12:47 pm
what do you mean exactly. I can't figure out why what i wrote isn't working. plz hlp.
January 28, 2014 at 1:28 pm
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...)
January 29, 2014 at 8:21 am
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.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply