Importing a wk1 file

Check this sample from sandbox:

http://www.notes.net/sandbox.nsf/ecc552f1ab6e46e4852568a90055c4cd/856b92a3d3756d418525697700476b81?OpenDocument

~Hemanth

Sorry Hermanth but this example is to open a csv file. I want to open a wk1 file. The csv is a text file and can be opened in Notepad, if you open a wk1 file in notepad you see garbage.

Thanks anyway for your help.

You can save the wk1 file in csv and use this sample. It will be easier to read a flat file, than using some ole to read the data from the file, is that what you are looking for ???

I could use csv but converting the file to csv each time would'nt allow me the automize the import

Pennywisdom,

To find out the structure of the *.wk1 file, simply open it in Lotus 1-2-3, or in excel (that is if you have installed the Lotus Filters when you installed Excel).

Or simply import the file into a Rich text field to see what the column structure contains.

There is a threshold with importing line by line, so I hope your wk1 file doesn't exceed the 8,192 rows? Also, I found that when importing either from excel or 1-2-3, and doing a line by line loop there is a memory leak after the first 5000 rows (depending on the size of each row).

You can try DDE functions to access the 1-2-3 file: (this is R5)
Sub BUTTONButton2(B2 As Button)
   Dim taskId As Integer
   ' Start 1-2-3 (the DDE server) and open a worksheet.
   taskId% = Shell(c:\123r5w\programs\123w.exe, _
                   d:\work\expenses.wk4")
   ' Create the DDE object.
   Set DDE123 = New DDE("123worksheet", _
        "d:\work\expenses.wk4")
   ' Retrieve the value in the range named MiscTotal
   ' and place it in Field 1 of the current form.
   Field1.Value = DDE123.Request ("MiscTotal")
   ' Terminate the DDE conversation
   DDE123.Terminate
End Sub

Replace the *.wk4 with the wk1 extension and see if it imports into a rich text field where you can examine the structure, columns etc. If this works, then you can explore the remainder of the DDE functions to accomplish the import automatically.

If you explore the DDE functions, you'll find several examples that might help you to do this.

The better way is to configure a DSN/ODBC connection on your computer with the 1-2-3 file (you might have to install the Lotus SQL patch to add to the ODBC datatypes, download from Lotus.com) Then you can simply access this via standard lotus script ODBC commands, or the @DBlookup, @DBcolumn formula commands to create and import the documents.  Using the formula language commands might be more efficient than stepping through the import line by line and less likely to hit the memory leak thresholds.


Last, you can also configure a script (as HemanthaHumar suggests) that will open the file in Excel and save it to a csv file using OLE/ActiveX commands and then import it via standard script.  How you do it depends on the total number of rows that you need to import. (for instance, I've had to pull and push over 5000 rows to and from excel, and had to find creative workarounds to automate it.)

Offhand, I'd go for the path of least resistance and configure the ODBC connection and use @dblookups to access and import the data.

hope this helps,
marilyng

Can someone send me some sample code on how to use OLE/ActiveX that opens a file and saves i to csv?

Thanks marilyng, with your help I was able to code my import agent.

For the record it looks like this:
Sub Initialize
     Dim XLapp As Variant
     Dim Workbook As Variant
     Dim Filenum As Integer
     Dim session As New NotesSession
     Dim db As NotesDatabase    
     Dim doc As Notesdocument
     Dim pos As Integer
     Dim OldPos As Integer
     Dim s As String
     Const Sep = ","
     
     On Error Goto Errorhandler
     
     'Open in Excel and save in CSV format
     Set XLapp = CreateObject("Excel.Application")
     XLapp.visible = False
     XLApp.DisplayAlerts = False
     Set workbook = XLApp.Workbooks.Open("C:\File.wk1")
     workbook.SaveAs "C:\File.csv",23 '23 = xlCSVWindows, 24 = xlCSVMSDOS
     
     Set db = session.currentdatabase
     
     'Open the file as text
     Filenum = Freefile
     Open "C:\File.csv" For Input As #Filenum
     Do Until Eof(Filenum)
          Line Input #Filenum, s
          Set doc = New NotesDocument (db)          
          doc.form = "MyForm"
         
          pos = Instr(1,s,sep)
          doc.Field1 = Left(s,pos-1)
         
          oldpos = pos + 1
          pos = Instr(oldpos,s,sep)
          doc.Field2 = Mid(s,oldpos,pos-oldpos)

          oldpos = pos + 1
          pos = Len(s) + 1
          doc.Field3 = Mid(s,oldpos,pos-oldpos)
         
          doc.save True,True
     Loop
     Close #Filenum
     
ExitHere:
     XLapp.application.quit
     Set XLapp = Nothing
     Set Workbook = Nothing
     
     Set session = Nothing
     Set db = Nothing
     Set doc = Nothing
     
     Exit Sub
     
Errorhandler:
     Messagebox "Error " & Str(Err) & ": " & Error$
     Resume exithere
     
End Sub

You go girl!!

Quick note.. you might want to remark out the message box if you're going to schedule the agent for the server -- this way if there is an error, the server won't be sitting in some room three states away waiting for someone to click on the "ok" button (grin).  

Second note.. you might want to count the total lines processed, because after 5000 rows there is a memory leak and if running locally, it will simply shut down your computer.. Although, Notes does say it fixed this in versions 4.6.8+...

Also you probably need to check the value of the substring when you have first, second,,fourth and third is ""

So your incremental sep might return an error when the last field or first field is blank.

Or, you have a text field, "this is address,State 99999",
(which is why I hate comma delimited files) I would do what you're doing, but add a loop to make sure you don't have too many commas other than those separating the fields.

countcomma = 0
for x = 1 to length(line)
if mid(line,1,x) ="," then countcomma = countcomma +1
next

if countcomma > totalfields then --- go to sub to decide what to do..

hope this helps and thanks for the points..
marilyng

Thanks for the advice!

How come "you go girl", what made you think i'm a girl?