text file import

Hi there,

I have an agent which imports a text file. That file has the attendance records for each employee, these can be thousands

The text file is usually exported from a third party system. I have the text file exported to another server, then I import this file to my notes database using a lotusscript agent.

Currently, I am deleting all data in db every night and every morning importing the fresh data which include previous days data as well as the accumulated new day's data.

For example, today I import file which has yesterday's data(no real time, data in text file is always previous day attendance data).  At the end of today, I delete all data from database, meanwhile the text file is exported with today's data in it, so tomorrow, I import the text file again which has yesterday and today and so on until the end of period(28 days)

What I thought of is to try to create a view which has all the documents sorted by date descending, without categorizing the view. Then the last document in the view, try to check the date with the newly imported data, if that last document in the view is one day older than the document about to import, then import, otherwise ignore.

This is when I need your support guys

Here's my import code, can you take a look and see where and how I can accomodate for date checking.

Agent Information
Name:      1.Import data for TATC Form
Alias:       TATC
Last Modification:      26/02/2003 10:20:01 PM
Comment:      [Not Assigned]
Shared Agent:      Yes
Type:      LotusScript
State:      Enabled
Trigger:      on schedule once d day
Acts On:      all documents in database

LotusScript Code:
Option Public
Option Compare Nocase
%INCLUDE "LSCONST.LSS"
%INCLUDE "LSERR.LSS"
%INCLUDE "LSXBEERR.LSS"
Sub Initialize
'***************************************************************************************
'This is calling the main function. the arguments of the main function
' is the input file location and the outputdb  
'***************************************************************************************
      'Dim taskid As Integer
      'taskid = Shell("/notes/tmp/Detailtest.txt/notes/tmp")
      'Call main("/notes/tmp/Detailtest.txt", "," , "attendDB.ntf" , "TATC")
      'Call main("taskid", "," ,"attendDB.ntf" , "TATC")
      Call main("c:\user\detail.txt", "," , "flexDB.nsf", "TATC")
      'Call main("//domino/tmp/detail.txt", "," , "attendDB.ntf" , "TATC")      
End Sub
Function createdocument ( outputdb As notesdatabase , form As String , fieldvalues As Variant) As notesdocument
' *******************************************************************************************************************
'This paragraph is declaring object variables, a notesdocument,  a counter variable (ix),
'FieldArray variable to hold the Feld Values, a view to get access to the notes document
'a notes entry to get access to a view entry(in this case manager and director) and a manager
'and a director variables.       
' ********************************************************************************************************************
      Dim doc As notesdocument
      Dim ix As Long
      Dim readersArray(5) As String
      Dim view As notesview
      Dim entry As notesviewEntry
      Dim manager As String, director As String
      Dim name1 As String, name2 As String
      '****************************************************************************************************************
      'This section declares new notes database and assigns it the value of namesDept.nsf.
      'The code also check whether or not the database is open and if it is open, it prints to the
      'status bar "successfully opened namesDept.nsf". If the namesDept.nsf is not open then another
      'the message that is printed to the status bar is "unable to open database"
      '****************************************************************************************************************
      Dim session As New notessession
      Dim dbhr As notesdatabase
      If session.IsOnServer Then
            Set dbhr =  New NotesDatabase( "", "namesDept.nsf" )
      Else
            Set dbhr = New notesdatabase("", "namesDept.nsf")
      End If
'******************************************************************************************************
'this is to set the value of the notes document.
 'this also can be written as follows
' set doc = new NotesDocument(outputbd)
' *******************************************************************************************************
      Set doc = outputdb.createdocument
'********************************************************************************************************
'This code is to set the field "Form" with the Form's name "TATC" so that it can be used
'to open you document
' *********************************************************************************************************      
      doc.Form = "TATC"
'***********************************************************************************************************      
'The following section is to check whether or not the fieldValue Array equals to 7. if the upper
'bound of the FieldValue is 7 then the values in the Array are assigned to the newly created form
'in the fieldArray to the field on the newly created form.
'The field values are picked up from the first line of the txt file, for example attendance.tx
'******************************************************************************************************************************
      If  Ubound(FieldValues) =  7 Then
            Call doc.ReplaceitemValue("datetime",fieldvalues(0))
            Call doc.ReplaceItemValue("reader", fieldvalues(1))
            Call doc.ReplaceitemValue("cd_status",fieldvalues(2))
            Call doc.ReplaceItemValue("surname", fieldvalues(3))
            Call doc.ReplaceitemValue("first_name",fieldvalues(4))
            Call doc.ReplaceItemValue("department", fieldvalues(5))
            Call doc.ReplaceitemValue("hours", fieldvalues(6))
            Call doc.ReplaceitemValue("minutes",fieldvalues(7))
'*************************************************************************************************************************            
'This code is assigning first_name and the surname fields to the first entry in the array
'**************************************************************************************************************************            
            readersArray(0)  = doc.First_name(0) & " " & doc.Surname(0)
'**************************************************************************************************************************            
 'This code is to get the view "people"  from namesDept.nsf
'**************************************************************************************************************************
            Set view = dbhr.getview("people1")
'**************************************************************************************************************************            
 'This code is to get the employee document, and sets the value to a variable called "entry"
'**************************************************************************************************************************
            Set entry = view.getentrybykey(doc.First_name(0) & " " & doc.Surname(0) , True)
'**************************************************************************************************************************            
 'This code is to get the manager and director values from columns in people view
'**************************************************************************************************************************
            If Not entry Is Nothing Then
                  manager= entry.columnvalues(6)
                  director = entry.columnvalues(5)
                  name1 = entry.columnvalues(7)
                  name2 = entry.columnvalues(8)
            End If
'**************************************************************************************************************************            
 'This code is to assign the two (manager and director) extracted from the people view from
'withing namesDept.nsf their equivalent field in the new created document.
'**************************************************************************************************************************
            doc.manager1 = manager
            doc.director = director
            doc.name1 = name1
            doc.name2= name2
'**************************************************************************************************************************            
 'This code is to assign the equivalent values of the readersArray to the appropriate readers fields on
' the document
'**************************************************************************************************************************
            readersArray(1) = manager
            readersArray(2) = director
            readersArray(3) = "Name/Org"
            readersArray(4) = name1
            readersArray(5) = name2
            Doc.ReplaceItemValue ("ReaderField",  readersArray).IsReaders = True             
'**************************************************************************************************************************            
'This code is to used to Validates a document (TATC) by executing the default value, translation, and
'validation formulas, if any are defined in any of the fields on the document form.
'The second line of the code prints a message to the status bar.
'The last line of the code saves the document, even if someone else is editing the document while the script
'is running
'**************************************************************************************************************************
            Call Doc.ComputeWithForm(False, False)
            Print "document created "
            Call doc.save (True, False, True)
      End If
End Function
Function main (inputFilePath As String , inputDelimiter As String , outputdatabasepath As String, outputForm As String)
'      ******************************************************************************************************
      ' this paragraph is declaring object variables, a notes database, a notesdocument,  
      ' a counter variable (ix), an input file, an input string and cells variable to manipulate
      ' through the script
'      *******************************************************************************************************
      Dim cells As Integer
      Dim doc As notesdocument
      Dim file As Integer
      Dim inputstr As String
      Dim ix As Long
      Dim Db As NotesDAtabase
'      ******************************************************************************************************
      'This code sets an error trap in case of error. If an error exist go to error routine cpf0000
'      *******************************************************************************************************
      On Error Goto cpf0000
'      ******************************************************************************************************
      'This code is to instantiate a session and a database object for both the session and the
      'databases classes
'      *******************************************************************************************************
      Set session = New notessession
      Set db = session.currentdatabase
'      ******************************************************************************************************
      'to open a file we need to assign the physical name to an internal name. For example, we
      'are assigning here c:\attendance.txt to and internal file (file).
      'Free file returns a free integer number of a file that you can read or write to.
      'if we need only to open one file we can write open "c:\attendance.txt for output as 1
'      *******************************************************************************************************
      file = Freefile()  
'      ******************************************************************************************************
      'This code is to specify the input file path for input
'      *******************************************************************************************************
      Open inputfilepath For Input As file
'      ******************************************************************************************************
      'This code loops through and while is not the end of file, it increments the counter by 1
'      *******************************************************************************************************
      Do While Not Eof( file)
            ix = ix + 1
'      ******************************************************************************************************
      'This code reads a line from a sequential file into the inputstr variable.      
'      *******************************************************************************************************
            Line Input # file, inputstr
'******************************************************************************************************
      'This code assigns the value of parsecolumns function and its arguments to a variable
      'called values.      
'*******************************************************************************************************      
            values = parsecolumns(inputstr, inputDelimiter)
'******************************************************************************************************
'This code sets the value of notes document (doc) to the value of createdocument
'function and its arguments.      
'*******************************************************************************************************                  
            Set doc = createdocument (db, outputform, values)
'******************************************************************************************************
'The code below is to check whether a session to the server exists. If a session does
'not exist then the message "imported" and the number in the counter are output to the
'status bar.      
'*******************************************************************************************************                  
            If  Not session.IsOnServer Then Print "Imported" & Cstr(ix)
      Loop
      Close file      
      Exit Function
'******************************************************************************************************
'The code below represents an error routine in case of error. The error number and
'description are prompted in a messagebox.      
'*******************************************************************************************************      
      'Dim agentLog As New NotesLog("Agent log")
      'Call agentLog.OpenAgentLog
      'Set s=New NotesSession
      'Set db = s.CurrentDatabase
      'Set collection = db.UnprocessedDocuments
      'Set note = collection.GetFirstDocument
      'count = collection.Count
      'Do While (count >0)
      '      Subject = note.Subject
      '      Call agentLog.LogAction("Processing:"+Subject(0))
      '      Set note = collection.GetNextDocument(note)
      '      count = count-1
      'Loop
      'Call agentLog.Close      
      'Messagebox " the agent that is running is" & agent.name
cpf0000:
      Reset
      Msgbox "Error: "&Cstr(Err) & " --" & Error$
      Exit Function
End Function
'******************************************************************************************************
'In the code below, the parsecolumns function goes through each column and extracts
'all the data. The separator is a",".
'Each line is parsed to extract each piece of data. the value is returned in an variant object , using as a table
'*******************************************************************************************************      
Function parsecolumns(inputstr As String, delimiter As String) As Variant
'******************************************************************************************************
'The code below declare all variables that needs to be manipulated in the function
'******************************************************************************************************      
      Dim ix As Long
      Dim pos As Long
      Dim tempinput As String
      Dim value As Variant
      Redim values(0) As Variant
'******************************************************************************************************
'The code below assigns the value of inputstr variable to tempinputvariable.
'******************************************************************************************************
      tempinput = inputstr
'******************************************************************************************************
'Below is a start of a Do loop.
'The loop first checks to see if there is any occurence of "tempinput" in "Delimiter". If
'no occurence exists then the variable value is assigned the value of tempinput.
'If there is occurence of "tempinput variable" within "delimiter" variable then  the value is
'found using the both the Left and right functions       
'******************************************************************************************************
      Do
            pos = Instr(tempinput, delimiter)
            If pos = 0 Then
                  value = Trim (tempinput)
            Else
                  value = Trim (Left(tempinput, pos - 1))
                  tempinput = Trim (Right ( tempinput,  Len(tempinput) - pos))
            End If
'******************************************************************************************************
'In the code below, we redim the values array while preserving its existent values.
'the code also checks the data type of the array "values" and converts it to its
'appropriate data type.
'********************************************************************************************************
            Redim Preserve values (ix)
            If Left(value,1) = | " | And Right (value,1) = | " | Then
                  values (ix) = Mid$(Value, 2, Len(value) -2)
                  If Isdate(value)Then
                        values(ix)=Cdat(value)                   
                  Elseif value = " " Then
                        values(ix) = Cstr(value)
                  Elseif Isnumeric (value) Then
                        values(ix) =Cdbl(value)
                  Else      
                        values(ix) = Cstr(value)
                        ix = ix +1
                  End If
            End If
      Loop Until pos = 0
      parsecolumns = values
End Function
            
Thanks,
Varvoura


 
** all occurances of actual usernames removed - CRAK, Page editor **