variable file name in openrowset

  • 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$]');

  • 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.

  • 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)

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

  • 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...)

  • 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