?
Solved

Phase 2 of the import

Posted on 2005-03-02
20
Medium Priority
?
241 Views
Last Modified: 2013-12-18
OK now I have the ability to import values from a excel spread sheet in to a form.  Here is the next issue I need to solve.

When the import runs it creates a document for every row in the spreadsheet.  This is fine.  When the second import comes at end of month I need it to edit the documents with the new data and not create all new documents.  Here is the way I am thinking - there are two fields in the form ( a lot more this just a example)

Jan_Plan
Jan_OL

The first import puts a value from the spreadsheet in Jan_Plan, then the second and others imports only edit the Jan_OL field.
Jan_Plan is a target field and Jan_OL is actual.  Please tell me if I need to make this more clear.  I will try to sum it up.

Import 1

All fields are populated from a spreadsheet and a bunch of documents are created.  The fields pertaining to months are populated in the Month_Plan fields.

Import 2

All the documents that were created is now edited and no new documents are created.  It only creates documents on the first import.  The values are now assigned to Month_OL.  That way I can have a 3rd field calculating the delta between Month_Plan and Month_OL

Here is the first import

Sub Click(Source As Button)
      On Error Goto barry
      Dim file As Variant
      Dim ws As New NotesUIWorkspace
      Dim xlFilename As String
      Dim session As New NotesSession
      Dim db As NotesDatabase
      Dim doc As NotesDocument
      Dim colValues(1 To 30) As Variant
      Dim intCount As Integer
      Dim intRow As Integer
      Dim Excel As Variant
      Dim xlWorkbook As Variant
      Dim xlSheet As Variant
      Dim blankLine As Variant
      
      Set db = session.CurrentDatabase
      
      file = ws.OpenFileDialog(False, "File List", "", "c:")
      If Isempty(file) Then
            Exit Sub
      End If
      
     ' Connect to Excel and open the file. Then start pulling over the records.
      xlFilename = Cstr(File(0)) '// This is the name of the Excel file that will be imported
      Set Excel = CreateObject("Excel.Application")
      Excel.Visible = False '// Don't display the Excel window
      Excel.Workbooks.Open xlFilename '// Open the Excel file
      Set xlWorkbook = Excel.ActiveWorkbook
      Set xlSheet = xlWorkbook.ActiveSheet
      
     ' Get the data from the first row in Excel
      blankLine = True
      intRow = 1     ' Start with 2 instead of 1 if your Excel sheet has a header row
      For intCount = 1 To 30
            colValues(intCount) = xlSheet.Cells(intRow, intCount).Value
            If colValues(intCount) >< "" Then blankLine = False
      Next
      
     ' While we don't have a blank line, create a document out of what we do have.
      Do While Not blankLine
            Set doc = New NotesDocument(db)
            doc.Form = "Jat2"
            doc.Group = colValues(1)
            doc.Manager4th = colValues(2)
            doc.Area = colValues(3)
            doc.Category = colValues(4)
            doc.Jan = colValues(4)
            doc.Feb = colValues(5)
            doc.Mar = colValues(6)
            doc.Q1_Total = colValues(7)
            Call doc.Save(True, False)
          ' Get the data from the next line.
            intRow = intRow + 1
            blankLine = True
            For intCount = 1 To 30
                  colValues(intCount) = xlSheet.Cells(intRow, intCount).Value
                  If colValues(intCount) >< "" Then blankLine = False
            Next
      Loop
      
      xlWorkbook.Close False '// Close the Excel file without saving (we made no changes)
      Excel.Quit '// Close Excel
      Set Excel = Nothing '// Free the memory that we'd used
      Exit Sub
barry:
      Msgbox "error occured on line number" & Erl & "error is " & Error
      Exit Sub
End Sub

0
Comment
Question by:Jaziar
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 12
  • 8
20 Comments
 
LVL 7

Accepted Solution

by:
BarryTice earned 2000 total points
ID: 13439974
Good morning, Jaziar.

This will require that every line in your Excel spreadsheet have some "key" combination of fields that will be unique. Say your first four columns, Group, Manager4th, Area, and Category end up being your key. That means that regardless of what other data is in your other columns, there will never be two rows that have the same combination of these four. (If you can do it with fewer than four, that's fine. This example assumes four.)

The first thing you'll need to do is create a view that lets you look up existing records. I suggest calling it (Jat2 Docs). (Note that the name of the view is in parentheses to keep it hidden.) Set the view selection as:
SELECT Form = "Jat2"

Make this the formula for the first (only) column:
@Trim(Group) + @Trim(Manager4th) + @Trim(Area) + @Trim(Category)

Make the first column sorted standard. Save and close.

Change your import (above) to this:
' ==== BEGIN CODE ====
Sub Click(Source As Button)
      Dim file As Variant
      Dim ws As New NotesUIWorkspace
      Dim xlFilename As String
      Dim session As New NotesSession
      Dim db As NotesDatabase
      Dim doc As NotesDocument
      Dim colValues(1 To 30) As Variant
      Dim intCount As Integer
      Dim intRow As Integer
      Dim Excel As Variant
      Dim xlWorkbook As Variant
      Dim xlSheet As Variant
      Dim blankLine As Variant
      
      Set db = session.CurrentDatabase
      
      file = ws.OpenFileDialog(False, "File List", "", "c:")
      If Isempty(file) Then
            Exit Sub
      End If
      
     ' Connect to Excel and open the file. Then start pulling over the records.
      xlFilename = Cstr(File(0)) '// This is the name of the Excel file that will be imported
      Set Excel = CreateObject("Excel.Application")
      Excel.Visible = False '// Don't display the Excel window
      Excel.Workbooks.Open xlFilename '// Open the Excel file
      Set xlWorkbook = Excel.ActiveWorkbook
      Set xlSheet = xlWorkbook.ActiveSheet
      
     ' Get the data from the first row in Excel
      blankLine = True
      intRow = 1     ' Start with 2 instead of 1 if your Excel sheet has a header row
      For intCount = 1 To 30      ' 30 will take you out to column AD
            colValues(intCount) = Trim$(Str$(xlSheet.Cells(intRow, intCount).Value))
            If colValues(intCount) >< "" Then blankLine = False
      Next
      
     ' While we don't have a blank line, create a document out of what we do have.
      Do While Not blankLine
            Set doc = GetDoc(db, colValues)
            If (doc Is Nothing) Then
                  Set doc = MakeNewDoc(db, colValues)
            End If
            ' This function adds all the current values, whether the doc is new or existing.
            Call AdjustDoc(doc, colValues)
          ' Get the data from the next line.
            intRow = intRow + 1
            blankLine = True
            For intCount = 1 To 30
                  colValues(intCount) = xlSheet.Cells(intRow, intCount).Value
                  If colValues(intCount) >< "" Then blankLine = False
            Next
      Loop
      
      xlWorkbook.Close False '// Close the Excel file without saving (we made no changes)
      Excel.Quit '// Close Excel
      Set Excel = Nothing '// Free the memory that we'd used
      Exit Sub
End Sub
' ===== END CODE =====

Add the following functions and subroutine. (You can do this by going to the line after End Sub [press Enter if you need to] and pasting this in.)

' ==== BEGIN CODE ====
Function GetDoc(db As NotesDatabase, colValues() As Variant) As NotesDocument
      Dim view As NotesView
      Dim strKey As String
      Dim doc As NotesDocument
      Dim intCount As Integer
      Dim item As NotesItem
      
      ' See if there is already a document with the same first four values
      Set view = db.GetView("(Jat2 Docs)")
      ' Create a temporary document
      Set doc = New NotesDocument(db)
      strKey = ""
      ' Concatenate the first four colValues into a string key
      For intCount = 1 To 4
            ' Create temporary items in that document, so we can reliably get the text of the variant colValues
            Set item = New NotesItem(doc, "Temp" & Trim$(Str$(intCount)), colValues(intCount))
            strKey = strKey & Trim$(item.Text)
      Next
      If strKey = "" Then
            ' If the key is blank, let the outer routine make a new document.
            ' (This should probably never happen, by the way.)
            Set doc = Nothing
      Else
            ' Get the document that matches this key.
            Set doc = view.GetDocumentByKey(strKey, True)
            ' Note that if no document matches the key, doc = Nothing.
      End If
      ' Return the doc (or Nothing) that we found.
      Set GetDoc = doc
End Function
Function MakeNewDoc(db As NotesDatabase, colValues() As Variant) As NotesDocument
      Dim doc As NotesDocument
      
      Set doc = New NotesDocument(db)
      doc.Form = "Jat2"
      doc.Group = colValues(1)
      doc.Manager4th = colValues(2)
      doc.Area = colValues(3)
      doc.Category = colValues(4)
      
      Set MakeNewDoc = doc
End Function
Sub AdjustDoc(doc As NotesDocument, colValues() As Variant)
      ' This function adds all the current values, whether the doc is new or existing.
      ' Coded this way to give one place to maintain code
      doc.Jan = colValues(5)
      doc.Feb = colValues(6)
      doc.Mar = colValues(7)
      doc.Q1_Total = colValues(8)
      Call doc.Save(True, False)
End Sub
' ===== END CODE =====

What this does is read a line from the spreadsheet and call the GetDoc function to see if the document already exists. GetDoc concatenates those four key fields into one string and performs a lookup against this new view you just created. If it finds a document, it returns it for update. If not, it returns "Nothing" and the main routine then knows it needs to use the MakeNewDoc function to create a new document. (This way, if someone adds a line to your spreadsheet the first import will be seamless.)

Then the document (either already existing or just created in MakeNewDoc) is passed to AdjustDoc, which updates all the other fields.

If there are fields you don't want to have updated every time, put them in the MakeNewDoc function. Any field that should be updated every time the import is run should be in the AdjustDoc subroutine.

Does this make sense?

-- b.r.t.
0
 

Author Comment

by:Jaziar
ID: 13440167
wow - nice job - Let me look over this and do some test.  I am still a little unclear about the

This will require that every line in your Excel spreadsheet have some "key" combination of fields that will be unique. Say your first four columns, Group, Manager4th, Area, and Category end up being your key. That means that regardless of what other data is in your other columns, there will never be two rows that have the same combination of these four. (If you can do it with fewer than four, that's fine. This example assumes four.)

It is possible that there could be the same combo of those four.  Maybe not I will try and adjust the spreadsheet so that don't happen.

0
 
LVL 7

Expert Comment

by:BarryTice
ID: 13440358
Consider for a moment that if two lines in the spreadsheet have the same information in them, why do you need them both? Clearly there must be something that distinguishes the two lines, that makes them both necessary.

Apparently this database is to keep track of some objects that are in some group, category, and area, under some manager. If there are two identical such objects out there, how do you tell them apart in the spreadsheet? How do you know which one is which when it comes time for someone to update the spreadsheet? **THAT** is your key value (or at least needs to be some part of your key value).

The key fields don't have to be the first columns. You can have them as columns 1, 17, 23 and 27-30 if that's what fits your situation.

Does this clear it up any?

-- b.r.t.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:Jaziar
ID: 13440524
I have the first 4 being unique now.  So I am in the process of applying
0
 

Author Comment

by:Jaziar
ID: 13442035
Barry the first import has some issues in the code

Do While Not blankLine
            Set doc = GetDoc(db, colValues) <= Illegal parenthesized referenced: GETDOC
If (doc Is Nothing) Then
            Set doc = MakeNewDoc(db, colValues)<= Illegal parenthesized referenced: MakeNewDoc
End
Call AdjustDoc(doc, colValues) <= Not a sub or function name : ADJUSTDOC
0
 
LVL 7

Expert Comment

by:BarryTice
ID: 13442164
Jaziar --

When you're looking at the code for the button click, the code selector pane (not its official name) on the left should have the following options in it:

(Options)
(Declarations)
Click
Objectexecute
Initialize
Terminate
GetDoc
MakeNewDoc
AdjustDoc

The main part of your code would probably be in your Click event.

If you don't have those bottom three options there, you didn't paste in (or somehow it lost) those three subroutines.

Select the first of the functions I included above, from where it begins "Function GetDoc(db As NotesDatabase, colValues() As Variant) As NotesDocument" to the first place it says "End Function", copy that, and go back to the Notes developer. Go to the end of the End Sub line for the Click event. Press Enter to go to the next line. Paste that code in.

That should create a GetDoc function with its own entry in the code listing. Repeat that process for the MakeNewDoc function and the AdjustDoc function.

These functions and this subroutine would be callable from any of the events in this button (e.g., the Initialize event or the Terminate event) if you wanted. Or, they can call each other. The "scope" of these routines is the life of this button-click event.

Does this help?

-- b.r.t.
0
 

Author Comment

by:Jaziar
ID: 13442357
You are correct I dod not paste the subs in first
0
 

Author Comment

by:Jaziar
ID: 13442457
am I putting the code in the (jat2 docs)?  being hidden it will be hard to use the upload button
0
 

Author Comment

by:Jaziar
ID: 13442506
Barry could this code in GetDoc be causing a type mismatch

      Set item = New NotesItem(doc, "Temp" & Trim$(Str$(intCount)), colValues(intCount))
0
 
LVL 7

Expert Comment

by:BarryTice
ID: 13442616
Jaziar --

(Jat2 docs) is a behind-the-scenes view used to lookup existing documents in the GetDoc function. You put the code for this where you put the code from yesterday, replacing that code.

I don't see why it would, off the top of my head. The parameters are:
   doc as a NotesDocument
   "Temp" & Trim$(Str$(intCount)) as a string
   colValues(intCount) as a variant, as the value to go into the new item.

I take it you've run this in debug mode and that's where you're getting the problem?

-- b.r.t.
0
 

Author Comment

by:Jaziar
ID: 13443350
OK here is the results - I fixed the mismatch problem.

I run the first upload

I have 132 documents created  - using jat2 form  which looks like this

[group]   [4thmanager]   [area]   [catergory]

[jan_plan]   [jan_ol]    [jan_delta]
                  [jan_act]  [jan_delta]

each month is like this.  The delta's are calculated.  After the first upload

jan_plan = 10  and jan_ol = 10  which is what I want.  I put this code in

Function MakeNewDoc(db As NotesDatabase, colValues() As Variant) As NotesDocument
      Dim doc As NotesDocument
      
      Set doc = New NotesDocument(db)
      doc.Form = "Jat2"
      doc.Group = colValues(1)
      doc.Manager4th = colValues(2)
      doc.Area = colValues(3)
      doc.Catergory = colValues(4)
      doc.Jan_Plan = colValues(5)
      doc.Feb_Plan = colValues(6)
      doc.Mar_Plan = colValues(7)
      doc.Q1_Total_Plan = colValues(8)
      
      Set MakeNewDoc = doc
End Function  

and

Sub AdjustDoc(doc As NotesDocument, colValues() As Variant)
     ' This function adds all the current values, whether the doc is new or existing.
     ' Coded this way to give one place to maintain code
      doc.Jan_OL = colValues(5)
      doc.Feb_OL = colValues(6)
      doc.Mar_OL = colValues(7)
      doc.Q1_Total_OL = colValues(8)
      Call doc.Save(True, False)
End Sub

So first pass is correct.

Then I go to the spreadsheet and change a value of Jan (colValues(5)) from 10 to 12 - save
go to database and hit the upload button again - does it thing and finishes.  I now have 180 documents.  It creates documents for all the months again - so my view looks like this

something
      manager
            area
                jan_plan = 10     jan_ol = 10
                jan_plan = 12     jan_ol = 12

I need for the view to look like this

something
      manager
            area
                jan_plan = 10     jan_ol = 12

After the first import jan_plan will never change only OLs.  plus it was creating new documents - I was hoping just to replace the OL values in the existing documents.


0
 
LVL 7

Expert Comment

by:BarryTice
ID: 13443505
I don't know if this is it for sure, Jaziar, but in your MakeNewDoc function you are spelling "Category" as "Catergory". If the Category field is part of the "key" that ensures you have a unique document, that could be part of the problem.

The crux of this problem is that the GetDoc function isn't finding the existing document when it checks.

Take a look at the (Jat2 Docs) view in the developer. Refresh the view in the developer to show you what documents are appearing in it. You should be seeing something like:
ColorSocksBobCategory2
MonoSmithJohnCategory1
etc.

The one column that exists in that view is just these four key fields smashed together, and it's critical that the column be sorted.

Try putting this line in the GetDoc function just before the end:
if doc is nothing then print "No Doc. Key = " & strKey

This will print some information on the status line at the bottom of the screen that will be helpful in debugging. For every time it doesn't find an existing document, it will print a message with the key it was using to try to find the document.

Report back with an example of what that's giving you, please.

-- b.r.t.
0
 

Author Comment

by:Jaziar
ID: 13443588
I got it working - I had a key field messed up.  This is great!!!!!!  Thank you very much.  I will close this tomorrow after I run some more test.
0
 
LVL 7

Expert Comment

by:BarryTice
ID: 13443985
OK, Jaziar. Let me know if I can be any more help.

-- b.r.t.
0
 

Author Comment

by:Jaziar
ID: 13444080
Trust me I have two more coming tomorrow!

1.  Is there logic in your subs to check the current date depending on the date assign the column value to either

Jan_OL or Jan_Act - what this does it the following

Jan_Plan = never changes after first input
Jan_OL = Is the current value before and while in the current month
Jan_Act = the actual number after the number has pasted

2  Is it possible to write a agent that looks at the excel file and sees if it have been saved on the current day and if it has then run the import.  Actually we could just set it to run every 6 hours or something.

This would keep the user from having to input in to notes everything they make a change - the system would do it for them.

THose two questions are coming.  This database is very important me and I thank you for your help thus far.



0
 
LVL 7

Expert Comment

by:BarryTice
ID: 13444467
Jaziar --

The short answers are:

1) No. There is no logic there now, but there certainly could be. I would need better requirements before I could start planning an approach, though.

2) Yes it is possible. You could either set it up so when it's run manually it checks to see if it should even bother, or have it run on schedule and import only after there's been a change to the Excel file. You would use the FileDateTime function for that one. (You can learn some about it in the help files, or by asking at EE.)

-- b.r.t.
0
 

Author Comment

by:Jaziar
ID: 13444499
1  Yea I know the logic is not there now, but I may have to go that way - still waiting to see.
2.  Great

Thanks for all your help - look for the next question in the morning
0
 

Author Comment

by:Jaziar
ID: 13449327
Here is a quick question that I think goes with this.

I have a computed field that @Sum(Jan_Plan Jan_OL)

The only way I can get this value to show up in a view is to go in the form open for edit and save.  Then it shows up in the views.  Is there a way we can do this in the program?
0
 
LVL 7

Expert Comment

by:BarryTice
ID: 13449488
Absolutely, Jaziar.

Let's assume this field is called Jan_Total, for purposes of this code. After Jan_Plan and Jan_OL are set by your code, you can have this line in your script:
doc.JanTotal = Jan_Plan(0) + Jan_OL(0)

Remember that all Notes items (fields) are, by their nature, lists. So any time you're referring to a field's value in LotusScript, you must refer to it as though you're dealing with an array of values. Jan_Plan(0) gives you the first (and, in this case, only) entry in that array of values.

-- b.r.t.

0
 

Author Comment

by:Jaziar
ID: 13449678
You are correct - that works - I have a meeting at 10:00 if it goes well I will be asking the question about seeing current time - question 1 from above.

Thanks
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For users on the Lotus Notes 8 Standard client, this article provides information on checking the Java Heap size and adjusting it to half of your system RAM in attempt to get the Lotus Notes 8.x Standard client to run faster.  I've had to exercise t…
I thought it will be a good idea to make a post as it will help in case someone else faces these issues. I trust this gives an idea how each entry in Notes.ini can mean a lot for the Domino Server to be functioning properly. This article discusses t…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses
Course of the Month10 days, 11 hours left to enroll

765 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