Post reply

Import multiple Excel Files into exciting table

  • July 26, 2007 at 7:59 am

    #73334

    I shall explain my situation (SQL 2000)

    I have a folder A, which contains excel sheets with the following name Services wXX.xls, XX stands for the number of the week.

    Now I want to import all these excel sheets with the different weeks into an existing table.

    And after that I what to move all those imported files into folder B, so I know these files are imported.

    Each week I receive new excel sheets which i put in folder A and I want to schedule my job to import the new excel sheets etc. etc.

     

    Hopefully someone can advise/help me

     

     

  • July 26, 2007 at 10:48 am

    #722647

    a vbs script sets a global variable to create your tablename.

    ww= datepart(ww,date())

    DTSGlobalVariables("importpath").Value = "\\import path to file\"

    DTSGlobalVariables("exportpath").Value = "\\export path to file\"

    DTSGlobalVariables("importname").Value = "w" & ww & ".xls"

    a dynamic properties task sets the import file of your transform data task to the global variable

    a vbs script moves the file to the new folder

    set fso = createobject("scripting.filesystemobject")

     if fso.fileexists (DTSGlobalVariables("importpath").Value & DTSGlobalVariables("importname").Value) then fso.movefile DTSGlobalVariables("importpath").Value & DTSGlobalVariables("importname").Value, DTSGlobalVariables("exportpath").Value & DTSGlobalVariables("importname").Value

  • July 26, 2007 at 4:04 pm

    #722725

    mrpolecat, could you please be more specific> I really do not understand how I can use your code?

    I have to have some more help with this one, I learn quickly I'll promise.

    I changed some of your code into for me usefull code. i made i bold, I also add some questions

    My first question is, where do I have to program this code?

    DTS? Job with ActiveX Script? please advise

    a vbs script sets a global variable to create your tablename. how can I do this? and where?

    ww= datepart(ww,date())

    DTSGlobalVariables("importpath").Value = "F:\Import"

    DTSGlobalVariables("exportpath").Value = "F:\Export"

    DTSGlobalVariables("importname").Value = "Services" & ww & ".xls"

    a dynamic properties task sets the import file of your transform data task to the global variable, how can I do this?

    a vbs script moves the file to the new folder. how can I do this?

    set fso = createobject("scripting.filesystemobject")

     if fso.fileexists (DTSGlobalVariables("importpath").Value & DTSGlobalVariables("importname").Value) then fso.movefile DTSGlobalVariables("importpath").Value & DTSGlobalVariables("importname").Value, DTSGlobalVariables("exportpath").Value & DTSGlobalVariables("importname").Value

  • July 26, 2007 at 5:35 pm

    #722735

    Take a look at this article.

    http://www.sqldts.com/246.aspx

    --------------------
    Colt 45 - the original point and click interface

  • July 27, 2007 at 4:26 am

    #722809

    I try to understand that article.

    Where are DTSGlobalVariables("gv_ArchiveLocation")

    and DTSGlobalVariables("gv_FileLocation")

    defined? or do i need to change "gv_FileLocation" into F:\Import?

    Where can I assigne the filename that only files start with Serrvices* are imported in that folder with different weeks?

     

  • July 27, 2007 at 11:42 pm

    #723116

    Did you download the sample package?

    --------------------
    Colt 45 - the original point and click interface

  • July 30, 2007 at 4:07 am

    #723242

    yes I download the sample package

  • July 30, 2007 at 4:17 am

    #723246

    Then it should show you what needs to go where.

    --------------------
    Colt 45 - the original point and click interface

  • July 30, 2007 at 4:35 am

    #723249

    not everything is clear to me.

    where are those gv stored?

     

  • July 30, 2007 at 9:09 am

    #723327

    If you don't know where global variables are stored then you need to a lot more reading and learning than can be provided in a simple forum response.

    Start of with this to learn about Global Variables. http://www.sqldts.com/280.aspx

    There are also a few other tutorials and examples on that same website.

    --------------------
    Colt 45 - the original point and click interface

  • July 31, 2007 at 2:54 am

    #723497

    oke I found Where I can find GV and what they to, I followed some tutorials.

    If Im correct only files with the name F:\Files\Copy (10) of Copy of Copy of Copy of Copy of Copy of File1.txt are imported?

    and that are txt files, I want to import xls files.

Viewing 11 posts - 1 through 10 (of 10 total)

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