Post reply

Import file with date-time stamp naming convention

  • September 3, 2002 at 3:12 pm

    #105189

    Hello everyone. I'm new at this and I've been looking for a way to import a delimited text file into SQL Server and think that DTS is probably the route to go. Anyway, our new data feeds are uniquely identified by a date-time stamp, making their filename format the following: FILENAMEYYYYMMDDHHMMSS.DAT

    How do I alter the DTS script to pick up the latest and greatest file without having to manually enter each time?

    Thanks for your help.

    -- Shogo

  • September 16, 2002 at 9:21 am

    #436596

    I was able to solve this problem with a fairly simple VBScript. The script finds the files and renames the files without the date-time stamp. Here's the code if anyone is interested:

    Function Main()

    Dim filesys, folder, f1, fc, filelist, datetime, copyfile, newname, oldname, filepath

    filepath = "\\server\directory\"

    Set filesys = CreateObject("Scripting.FileSystemObject")

    Set filefolder = filesys.GetFolder(filepath)

    Set fc = filefolder.Files

    For Each f1 in fc

    if left(f1.name,2) = "SP" then

    datetime = right (f1.name,18)

    oldname = f1.name

    if (len(oldname) > 18) Then

    newname = filepath + left(oldname,(len(oldname) - 18)) + ".dat"

    set copyfile = filesys.GetFile(filepath + oldname)

    copyfile.Copy(newname)

    copyfile.Delete

    filelist = filelist & oldname & " renamed as " & newname

    filelist = filelist & vbCrLf

    end if

    end if

    Next

    Main = DTSTaskExecResult_Success

    End Function

Viewing 2 posts - 1 through 2 (of 2 total)

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