Approach User Support homepage Approach User Support
Answers to Frequently Asked Questions about Lotus Approach
Examples LotusScripts
Example databases
Links to other Lotus Approach support services

[Return to contents]

Example LotusScript: Import a text file (or .dbf) into a DBF

Last updated: before December, 1998

The following was posted to the Approach Users Mailing List by Ankur R. Desai:

Sub import
        Dim t as table       'the table we will replace
        Dim r as ResultSet   'the object we use to modify the table
        Dim a$, j%               'extra variables
        Dim afile as integer 'the file number
        Set t = CurrentDocument.GetTableByName("tablename")
          'Where tablename is the table you want
        Set r = t.CreateResultSet()
          'Saves the hassles of using the connect and query
          'At this point however, you could do an SQL query with the Query
          'statement to select specific records the following way I think
          'r.query.sql = "SELECT ... "
          'r.execute()     <- returns true if successful
        If r.NumRows() > 0 Then
                ans = MessageBox("Yo, dis table be not empty, delete it?",4)
                if ans = 6 Then
                        Call r.FirstRow() 'go to the first row
                        For j = 1 to r.NumRows()
                                Call r.DeleteRow() 'delete it
                end if 
        End if
        afile = freefile()   'Get a file number to read from
        Open "filename.txt" For Input As afile
          'we open the filename and give it a filenumber you could ask
          'the user for the filename with the InputBox statement
          'in another message, I can post the routine to call the
          'OPEN-CommonDialog to get the filename from a user using
          'The standard Windows 95 OPEN box, it's not that hard
          'Search Lotuscript on altavista and you'll find it one the
          'first page of results        
        Do Until Eof(afile) 'EOF is End of File
                Line Input #afuke,a$
              'Read in one line - if you have a comma delimted file you
              'could use the Input statement to read each item
                Call r.AddRow() 'Add a row to the table
                Call r.SetValue("field1",Mid(a$,1,6))
                Call r.SetValue("field2",Mid(a$,7,10)+".0")
              'Sets field1 to the first 6 characters of a$
              'and field2 to to the next 10adding a .0 in the end
                Call r.UpdateRow
              'This commits the addition to the table
        Loop                 'Around we go
        Close afile                'Always close any files you open
        Call CurrentWindow.refresh()
          'Refreshes the data in the current window with the updated dbf
        Call CurrentWindow.repaint()
          'And redraw the window
          'you could do anything you want at this point
          'for example you could do a find on certain records and
          'find all records with a specific value and fill field3 with
          'a value, ex:
        Dim myfind as new FIND
          'make a new find object
        Call myfind.And("field1","Horseradish")
          'add an AND condition to find all records where field1 =
        Call CurrentWindow.FindSort(myfind)
          'excute the find
        Call CurrentWindow.FillField("field3","foobar")
          'fill all field3 in the found set with the work Yuck
End Sub

You can also import another DBF using the Connection property by doing the following:
Dim Q as New Query
Dim C as New Connection
Dim RS as New Resultset
        If C.ConnectTo("dBASE IV") Then   'Connect to dBASE.
                Set Q.Connection = C
                Q.Tablename = "path\filename.dbf"   
                Set RS.Query = Q
                If (RS.Execute)Then
                'Do funky stuff here
                        Call RS.FirstRow()
                        For j = 1 To RS.numrows()
                                Call R.AddRow()
                                Call R.Setvalue("CT_CODE", RS.GetValue("CT_REAS_CD"))
                                Call R.SetValue("CT_DESC",RS.GetValue("CT_REASON"))
                                Call RS.Nextrow()
                                Call R.UpdateRow()

Other nice lotusScript functions:
c$ = CurrentDocument.GetTableByName("tablename").NumRecords
          'Gives you the number of records in a table in your APR
          'Could replace "tablename" with CurrentView.MainTable
b$ = CurrentWindow.NumRecordsFound
          'Gives you the number of records in the current found set
          'With these two numbers you can fill in a variable field in a report
          'and be able to print out the info in the status bar
CurrentView.Footer.Field = "Found "+b$+" of "+c$

[Return to contents]

© Copyright, JohnBrown, Trademarks, Disclaimer, Acknowledgements.