Post reply

DTS to get latest XLS in folder

  • July 31, 2006 at 1:22 pm

    #173656

    I am creating a DTS package that needs to get the latest file in a folder and use it to update a table.  The file is an excel (.xls) with a column that is a number that needs to be treated like text so it keeps leading zeros (0000001234).  I found the following and used it

    http://www.sqlservercentral.com/columnists/djackson/importingthelatestfileinafolderviadts.asp

     but it only uses Text File(Source)  When I tried the same with MS Excel, I never get a file.  This file is dropped out in a shared folder nightly by the corp office and they only send in xls format.

    Any suggestions?

    Changinagain

  • July 31, 2006 at 2:35 pm

    #652464

    Fairly easy.  Here are some high level steps:

    1. Select the results of "xp_cmdshell 'dir c:\import'" into a temp table. (you'll have to fool with the command line switches).  Select the top 1 file name from the temp table by order by created date desc.  Put this in a global variable.
    2. Set the excel connection using the import folder and the global variable.
    3. Import the data from excel to a work table
    4. Run your update statement to update your database.

  • July 31, 2006 at 3:15 pm

    #652475

    Thanks, I'll give it a try.  Still new a a lot of this so I may be back with ???

    Changinagain

  • August 2, 2006 at 10:09 am

    #652881

    This will do it. Just needs tweaking with your directory and file name. This example for a sql dump file:

    ---- Determine lastest file

    declare @FileName varchar(100)

    create table #dbdmp ( FileName varchar (100) )

    insert into #dbdmp

    exec master..xp_cmdshell 'dir /B c:\mssql\backup\filename*.DMP'

    select TOP 1 @FileName = FileName from #dbdmp

    ORDER BY FileName desc

    ------------------------------
    The Users are always right - when I'm not wrong!

  • August 2, 2006 at 10:19 am

    #652885

    Thanks, that really helped.  A very clear directive.  I understand much better

    Changinagain

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

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