Getting excel file into sql table

  • Hi

    I want to create a trigger that loads an excel file into a sql table whenever an users saves an excel in that directory.

    How can we do this ?

    Thx

    El Jefe


    JV

  • This depends on how the file gets there

    If you are loading via ftp/asp for instance, your asp page can check for a correct load and then execute a corresponding DTS file

    Second option, if you are simply allowing users to save to directory on server is to write a sweep routine in wsh/vbs.  This can run every x mins and check for a file.  if it finds one, it executes the load routine and then moves the file out of the directory to avoid reprocessing.

    Hope this helps

    Ross

  • A trigger would not be the right terminology or method of doign this. Seems to me the best solution here is a DTS package that when completed, gets scheduled as a job that runs every X minutes.

    One, borrowing on what RossC said, make sure that you have a way/place to move a file once it is processed to avoid reprocessing.

    Lets assume your directory structure is like this

    D:\uploads\ForProcessing

    D:\uploads\Processed

    Create a new DTS package

    Add some a package level Global Variable and lets call it targetFile

    Make a data import task that will import an excel file (assuming they will always use the same formatting

    Name that task something easy to remember, and make it get the name of file it needs to import from the DTSpackage Global Variable object named targetFile

    Add an ActiveX script task, and use something similiar to the following to set the global variable

    Function Main()

    Dim FSO

    Dim oFiles

    Dim iFile

    Dim targetFile

     Set FSO = CreateObject("Scripting.FileSystemObject")

      Set OFiles = FSO.GetFolder("D:\Uploads\ForProcessing")

       For Each iFile In OFiles.Files ' For each file that is in the target Directory

       

        ' Set the Value of the Global variable here

        DTSGlobalVariables("targetFile").Value = iFile.ParentFolder & "\" & iFile.Name

        'Look up some code that will execute your import task

        ' EXECUTE YOUR DATA IMPORT TASK HERE

        ' Move the targetFile to the other direcotry to avoid reprocessing

        iFile.Move("D:\Uploads\ForProcessing\" & iFile.Name, "D:\Uploads\Processed\" & iFile.Name )

       Next

      Set OFiles = Nothing

     Set FSO = Nothing

     

     Main = DTSTaskExecResult_Success

     

    End Function

     

    Fill in the blanks where needed to suit your needs and you should be pretty good to go.

     

  • If you have access to .Net dev tools you can also take advantage of the FileSystemWatcher - this would allow you to perform your tasks as each file is saved/moved to the "load" directory.

    Otherwise - I like (and have used) sellertools method with great results.

  • Having read sellertools response, I would concur.

    The reason I am sweeping the directory is that we perform other tasks at os level as well

    The check sellertools is using will mean that no unnecessary processing is undertaken, and has the further advantage of the total solution being in one location. 

    Regards

    Ross

  • Guy

     

    Thx for the responses. We have some software that can trigger files. It's called automation. Then you can start stored procedures.

    We'll try something out 😉


    JV

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

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