Solved

Importing a wk1 file

Posted on 2001-09-11
10
729 Views
Last Modified: 2013-12-18
I want to create an agent that imports a file automaticly every week.
I can import the file manualy using File|Import. I tried using
@Command([FileImport]; "Lotus 1-2-3"; "C:\\File.wk1")
but it shows a dialog box that needs user interaction.

So I searched on a couple of sites and I found that I'm gonna have to import the file line by line in lotusscript.

What I want to know is how can I find out the structure of the file?
0
Comment
Question by:Pennywisdom
  • 5
  • 3
  • 2
10 Comments
 
LVL 24

Expert Comment

by:HemanthaKumar
Comment Utility
0
 

Author Comment

by:Pennywisdom
Comment Utility
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.
0
 
LVL 24

Expert Comment

by:HemanthaKumar
Comment Utility
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 ???
0
 

Author Comment

by:Pennywisdom
Comment Utility
I could use csv but converting the file to csv each time would'nt allow me the automize the import
0
 
LVL 18

Expert Comment

by:marilyng
Comment Utility
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

0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:Pennywisdom
Comment Utility
Can someone send me some sample code on how to use OLE/ActiveX that opens a file and saves i to csv?
0
 
LVL 18

Accepted Solution

by:
marilyng earned 100 total points
Comment Utility
Here's the example for grabbing the file from excel to import it also includes setting up the file and sending data to excel ( see: www.martinscott.com for more examples)

The line:     Set workbook = .Workbooks.Open("c:\My Documents\Test2.xls")

opens the xls file, but should also open any file that can be read by excel, including the *.wk1.  To find the appropriate syntax for the commands, you need to access the "OLE" resources as you do in script to access the lotus classes and language references.  If you select the excel worksheet activex commands and hit f1, and Lotus reports that it can't find the help file, then you may need to add the vb for excel help file to your current excel installation.   Also, remember to close excel else your script and the testing thereof will create many open instances (use ctrl+alt+delete to see running instances and close them in testing)

Declarations
'Used in functions to return the current column and row
Dim Cols As Integer
Dim Rows As Integer

'Phone book list
Type Phonebook
     pname As String
     address As String
     areacode As String
     phonenumber As String
End Type

Dim rec() As phonebook

'Excel Variables
Dim XLapp As Variant
Dim Workbook As Variant
Dim Worksheet As Variant
Dim Range As Variant
Dim ActiveCell As Variant

Sub Initialize
     
     'Excel Application
     Dim numberRows As Integer
     Dim numberCols As Integer
     
     On Error Goto Errorhandler
     Set XLapp = CreateObject("Excel.Application")
     
     With XLapp
          XLapp.visible = False
          Set workbook = .Workbooks.Open("c:\My Documents\Test2.xls")
          Set worksheet = .parent.worksheets("Sheet1")
          Set Range = worksheet.UsedRange
          Set ActiveCell = worksheet.Range("A1")
         
         
          'Now find the total number of rows and columns used in the worksheet
          numberCols = CalculateColumns            'count the number of columns
          numberRows = CalculateRows              'count the number of rows
         
          Rows = numberRows
          Cols = numberCols
         
          'Build a table list to hold the row information
          Call CollectPhoneList (Rows,Cols)
         
         
          'Then create the documents for each row
          Call Createdocs (numberRows)
     End With
     
     
ExitHere:
     XLapp.application.quit
     Set XLapp = Nothing
     Set Workbook = Nothing
     Set Worksheet = Nothing
     Set Range = Nothing
     Set ActiveCell = Nothing
     
     
     
     Exit Sub
     
Errorhandler:
     Messagebox "Error " & Str(Err) & ": " & Error$
     Resume exithere
     
End Sub

Sub CreateDocs (Rows As Integer)
     Dim s As New NotesSession
     Dim db As NotesDatabase    
     Set db = s.currentdatabase
     Dim doc As Notesdocument
     
     
     For r = 1 To Rows
'check to see if the first row is a header
          If rec(r).pname <>"Name" Then
               
               Set doc = New NotesDocument (db)          
               With doc
                    doc.form = "People"          
                    doc.Name=rec(r).pname
                    doc.Address=rec(r).Address
                    doc.AreaCode=rec(r).AreaCode
                    doc.PhoneNumber=rec(r).PhoneNumber
                    doc.status="Agent Import"
                    .save True,True
               End With
          End If
         
     Next
     
     Set s = Nothing
     Set db = Nothing
     Set doc = Nothing
     
End Sub

Function CalculateColumns () As Integer
     'Count the number of used columns
     i = 1
     While XLapp.Cells(1,i).value <>""
          i=i+1
     Wend
     CalculateColumns = i-1
End Function

Function CalculateRows () As Integer
     i=1
     While XLapp.Cells(i,1).Value <>""
          i=i+1
     Wend
     
     CalculateRows = i-1
     
End Function

Sub CollectPhoneList (Rows As Integer, Col As Integer)
'Now step through the number of rows and fill the phonebook list
     Redim rec(1 To Rows) As phonebook
     
     For r = 1 To Rows
          rec(r).pname = XLapp.cells(r,1).value
          Print "Adding " + rec(r).pname
          rec(r).address = XLapp.cells(r,2).value
          rec(r).areacode=XLapp.cells(r,3).value
          rec(r).phonenumber = XLapp.cells(r,4).value
         
     Next
     
End Sub
0
 

Author Comment

by:Pennywisdom
Comment Utility
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
0
 
LVL 18

Expert Comment

by:marilyng
Comment Utility
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
0
 

Author Comment

by:Pennywisdom
Comment Utility
Thanks for the advice!

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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

For beginners of Lotus Notes user this is important to know about the types of files and their location supported by IBM Notes. Mostly users are unaware about how many file types are created and what their usages are. This Article is fully dedicated…
Article by: Rob
Notes 8.5 Archiving Steps and Tips This article covers setting up a Notes archive, and helps understand some of the menu choices making setting up and maintaining a Notes archive file easier.
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now