Re: Import LATEST Excel file using DTS

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Allan Mitchell (allan_at_no-spam.sqldts.com)
Date: 03/01/04 

Date: Mon, 1 Mar 2004 10:15:47 -0000

You loop through the files - good
You pick up the file.
    First file Store it's name and DateLastModified properties in variables
    Subsequent files - compare time variable to this file's
DateLastModified.
        If this is a newer file replace the variables with this file's
properties.
    Continue comparisons 

-- 
----------------------------
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
<anonymous@discussions.microsoft.com> wrote in message
news:47bc01c3ff6a$4b6a2690$a401280a@phx.gbl...
> Hi Allan,
>
> Thanks for the quick response. As you suggested, i am
> trying to implement the following code -
>
> '**********************************************************
> ************
> '  Visual Basic ActiveX Script
> '**********************************************************
> **************
>
> Function Main()
> Dim lobjFileObject
> Dim lobjFolder
> Dim lobjFiles
> Dim ldtLastModiefied
>
> set lobjFileObject = CreateObject
> ("Scripting.FileSystemObject")
> Set lobjFolder = lobjFileObject.GetFolder("\\odc-
> ashish\Code")
> Set lobjFiles = lobjFolder.Files
>
> For Each File In lobjFiles
>     ldtLastModiefied = FileDateTime("\\odc-ashish\Code\" &
> File.Name)
>     If DateValue(ldtLastModiefied) >= DateValue(Now) Then
>         'create new connection to source Excel file
>         'Create new connection to table in SQL Server
>         'Dump data from source file into table
>     End If
> Next
>
>      Main = DTSTaskExecResult_Success
> End Function
>
> Is this correct?
>
> Regards
> Pratyush
>
> >-----Original Message-----
> >Loop through the files using the FSO (File System Object)
> >Create a variable to hold file names
> >Grab the .DateLastModified and compare against the
> current variable
> >replacing as needs be.
> >
> >
> >Another way to do this is to archive your files once
> you've finished with
> >them and you latest file will be the only one in the
> directory
> >
> >
> >
> >-- 
> >
> >----------------------------
> >
> >Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> >www.allisonmitchell.com - Expert SQL Server Consultancy.
> >www.SQLDTS.com - The site for all your DTS needs.
> >I support PASS - the definitive, global community
> >for SQL Server professionals - http://www.sqlpass.org
> >
> >
> >"Pratyush Kumar" <pratyushk@ggn.hcltech.com> wrote in
> message
> >news:1bda01c3ff5b$4dd4e2d0$a601280a@phx.gbl...
> >> Hi,
> >>
> >> I have a query regarding customization of data
> >> transformation using DTS. I have created a table in SQL
> >> Server 2000, in which data have to be imported from an
> >> excel file. I have a folder containing number of excel
> >> files. The problem is that, i have to import only that
> >> file which has recently been created/modified. DTS will
> >> trigger the import every half an hour and take the
> latest
> >> created/modified file and copy all the contents into the
> >> same table each time.
> >>
> >> Can you please suggest me how it can be done. Your
> >> response will highly be appreciated.
> >>
> >> Thanks & Regards,
> >> Pratyush Kumar.
> >
> >
> >.
> >

Relevant Pages

  • Re: Import LATEST Excel file using DTS
    ... Thanks for the quick response. ... Dim lobjFileObject ... Set lobjFiles = lobjFolder.Files ... >www.allisonmitchell.com - Expert SQL Server Consultancy. ...
    (microsoft.public.sqlserver.dts)
  • Re: File Auditing - Fast DB import and data manipulation
    ... the files into the database is not the best way forward. ... on either system (you'll need to download gunzip from gnuwin32 ... The databases I've dealt with (Oracle, SQL Server, Access, Postgres) ... So, I need some way to compare todays file, to yesterdays and see ...
    (comp.lang.ruby)
  • Re: Database Compare Application help needed
    ... of the work should be done within SQL Server. ... > I have to write a .Net application which can compare SQL Databases ... > in VB.6.0 but I have to design a C#.net application using the latest SQL ... > if anyone know what objects are available in .net to compare databases I ...
    (microsoft.public.dotnet.general)
  • Re: Date time difference complex query not working
    ... you compare a constant string to a constant numeric value. ... I guess you are running a different version of SQL Server. ... SELECT HoursOpen, HoursOverSLA ... (second complex expression) AS HoursOverSLA ...
    (microsoft.public.sqlserver.mseq)
  • Backup Comparison and Schema Versioning for SQL Server by Devart Is Now Released
    ... dbForge Schema Compare for SQL Server 2.0 compares schema snapshots, ... database backups, and database changes in version control systems to ... Devart has announced the public availability of Schema Compare for SQL ... across different SQL Server versions. ...
    (microsoft.public.sqlserver.tools)