Manage Learn to apply best practices and optimize your operations.

Importing data from files

When processing files from other sources, sometimes you have fixed length files and sometimes you have delimited files. This tip shows two generic subroutines -- SetVariableValueByLen and SetVariableValue -- that simplify the process and unclutter your code.


When processing files from other sources, sometimes you have fixed length files and sometimes you have delimited files. The routines SetVariableValueByLen and SetVariableValue are two generic subroutines that facilitate the process nicely and unclutters your code.

Code: Sub SetVariableValueByLen
( variablename As String, searchstring 
As String, strlen As Integer)
 variablename = Left(searchstring, strlen)
 searchstring = Right(searchstring, 
Len(searchstring) - strlen)
End Sub

Sub SetVariableValue( variablename As 
String, searchstring As String, searchchar As String)
 i = 0
 charat = 0
 For i = 1 To Len(searchstring)
  bchar = Mid(searchstring, i, 1)
  If bchar = searchchar Then
   charat = i
   'set i to the len of search so it 
will quit looping unnecessarily
   i = Len(searchstring)
  End If
 If ( charat = 0 ) Then
  'no searchchar found
  variablename =  Fulltrim(searchstring)
  If ( charact = 1) Then
   variablename = ""
   searchstring = Right(searchstring, 
Len(searchstring) - charat)
   variablename = Left(searchstring, charat -1)
   searchstring = Right(searchstring, 
Len(searchstring) - charat)
  End If
 End If
End Sub

fname = ws.OpenFileDialog(False, 
"Select file to be imported",, pathname) 
If Not(Isempty(fname)) Then
   Set stream = session.CreateStream
   pathname = Cstr(fname(0)) 
   If Not stream.Open(pathname, "ASCII") Then
      Messagebox pathname,, "Open failed"
      Exit Sub
   End If
   If stream.Bytes = 0 Then
      Messagebox pathname,, "File has no content"
      Exit Sub
   End If
   cnt = 0
      cnt = cnt + 1
      buffer$ = stream.ReadText
      REM Discard lines with only LF + CR
      While buffer$ = Chr(13) & Chr(10) _
      And (Not stream.IsEOS)
         buffer$ = stream.ReadText(STMREAD_LINE)
REM Process each line
   datavalue = buffer$
Call SetVariableValueByLen( checkstatus, 
datavalue, 1) Call SetVariableValueByLen
( bankdiv, datavalue, 
3) Call SetVariableValueByLen( account, 
datavalue, 10) Call SetVariableValueByLen
( checknum, datavalue, 
10) Call SetVariableValueByLen( yy, datavalue, 
2) Call SetVariableValueByLen( mm, datavalue, 
2) Call SetVariableValueByLen( dd, datavalue, 
2) Call SetVariableValueByLen( amountstr, datavalue, 
10) amount = Ccur( amountstr) \ 100 
Call SetVariableValueByLen( temp, datavalue, 
1) Call SetVariableValueByLen( unitcode, datavalue, 
3) Call SetVariableValueByLen( vendorid, datavalue, 
16) Call CreateCheckDoc() Loop 
Until stream.IsEOS Call stream.Close


for delimited files I prefer something like this. I feel it's lot more readable:

Dim RecordEntry As Variant 
Line Input #Fh%, EntireLine 
'Explode the values from that 
record to return individual field values 
RecordEntry = 
Evaluate (|@Explode("| & EntireLine & |" ; ";")|) 

Call doc.ReplaceItemValue("Field1", RecordEntry(0)) 
Call doc.ReplaceItemValue("Field2", RecordEntry(1)) 
Call doc.ReplaceItemValue("Field3", RecordEntry(2)) 

—Thilo H.

Do you have comments on this tip? Let us know.

This tip was submitted to the tip library by member Amy Weston. Please let others know how useful it is via the rating scale below. Do you have a useful Lotus Notes, Domino, Workplace or WebSphere tip or code snippet to share? Submit it to our monthly tip contest and you could win a prize.

Dig Deeper on Lotus Notes Domino Agents

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.




  • iSeries tutorials's tutorials provide in-depth information on the iSeries. Our iSeries tutorials address areas you need to know about...

  • V6R1 upgrade planning checklist

    When upgrading to V6R1, make sure your software will be supported, your programs will function and the correct PTFs have been ...

  • Connecting multiple iSeries systems through DDM

    Working with databases over multiple iSeries systems can be simple when remotely connecting logical partitions with distributed ...