Solved

Importing to Record from Excel

Posted on 2006-03-28
15
791 Views
Last Modified: 2013-12-18
I am trying to get some script together which will allow the data from a spreadsheet to be imported into a form. I will have a spreadsheet where the Office ID is in the first column, I will have a record open in notes which will have the Office in a field on the form. I want the system to look through each row of the spreadsheet and pull in the data if it matches the office ID on the open record. The code below is close but it forces me to separate the data for each office into its own tab. On the form I will have a series of fields in a table that the data will fall into. Below is just a basic layout where Import 1a..b..c would be the data that has matched this office ID from columns A,B,C in the spreadsheet. Hopefully what I am asking for amkes sense, appreciate any assistance.
Joe

Import1a  Import 1b  Import 1c
Import2a  Import 2b  Import 2c
Import3a  Import 3b  Import 3c
Import4a  Import 4b  Import 4c
Import5a  Import 5b  Import 5c


Sub Click(Source As Button)
      Dim session As New NotesSession
      Dim workspace As New NotesUIWorkspace
      Dim db As NotesDatabase
      Dim doc As NotesDocument
      Dim item As NotesItem
      Dim Filename As String
      Set db = session.CurrentDatabase
      Set uidoc = workspace.CurrentDocument
      Set doc = uidoc.Document
      
      Dim xlapp As Variant
      Dim xlsheet As Variant
      
      Dim x As Long
      Filename = "C:\dlm_refresh\dlm_refresh.xls"
'      Excel.Workbooks.Open Filename '// Open the Excel file
      Set xlapp = GetObject( Filename , "" )
      shtname= doc.office_id(0)   '  each sheet in excel is named with the agency 3-character id.  I pull this from the doc to locate the correct sheet.
      Set xlsheet = xlapp.WorkSheets( shtname )
      
      doc.userid1 = Clng(xlsheet.range("C2").value)
      doc.userid2 = Clng(xlsheet.range("C3").value)
      doc.userid3 = Clng(xlsheet.range("C4").value)
      doc.userid4 = Clng(xlsheet.range("C5").value)
      doc.userid5 = Clng(xlsheet.range("C6").value)
      doc.asset1 = Clng(xlsheet.range("B2").value)
      doc.asset2 = Clng(xlsheet.range("B3").value)
      doc.asset3 = Clng(xlsheet.range("B4").value)
      doc.asset4 = Clng(xlsheet.range("B5").value)
      doc.asset5 = Clng(xlsheet.range("B6").value)
      
      Call doc.save(True,True)
      
Out:
      Messagebox "Import done successfully."
      
      Set xls = Nothing
      Set sheet = Nothing
End Sub
0
Comment
Question by:jforget1
  • 4
  • 4
  • 4
  • +1
15 Comments
 
LVL 46

Expert Comment

by:Sjef Bosman
Comment Utility
I tried to find out what your real question is, but I can't. I don't quite get it.

I assume you need to fetch the current document's office-id
    id= doc.OfficeID(0)
and you need to loop through all lines in the spreadsheet
    i= 1
    Do While Not(IsEMpty(xlsheet.Cells(i,1).Value)
        ' add some code
        i= i + 1
    Loop

But why do you have to visit each row, and what are you going to do with those rows?
0
 

Author Comment

by:jforget1
Comment Utility
This is a tough one to explain, I will try and get it a little clearer. The person using this DB has a large spreadsheet which consists of machine information for laptops. THese are for machine which will need to be replaced soon. Instead of him having to enter the data for each office. I am trying to make it so he can start a new record enter the Office ID which will be the reference item. He will then click the import button and the system will then look to the spreadsheet and go through row by row to see if there are any machine for that office. If there are it wil pull the data from the sheet, (asset tag, serial # etc.). I want to have a record for each office and will setup a table with 25 rows of fields for the machine information to drop into. There would never be an office that needs more than 25 rows.

I know this is a weird one and I hope this makes it a lil clearer. I am new with LS so writing something like this is a challenge.

0
 
LVL 46

Expert Comment

by:Sjef Bosman
Comment Utility
You want a record per office, and not per machine? Never more than 25 machines per office? Never say never...

You know that the approach you chose might make things more difficult for you. You have to build code that will move machines in the office-document when one machine is deleted. Or inserted. Or whatever. Also, machine-dependent views are not possible. On the other hand, if you want to compare the Excel file with the documents present regularly, it is somewhat easier to do that with only one document. Will the user continue to use the spreadsheet? Altogether, I'm not in favour of the approach.
0
 
LVL 63

Expert Comment

by:SysExpert
Comment Utility
I would build it all in advance. Import the Excel info into a Notes DB using a form that is sililar to the column setup.
Then run an agent that build the per office info.

I hope this helps !
0
 
LVL 63

Expert Comment

by:SysExpert
Comment Utility
sililar= similar
0
 

Author Comment

by:jforget1
Comment Utility
The only reason we want to do them all on one form is so the person we are sending to does not get a ton of  emails in their inbox. If I can get a way where I can wrap all this up into one note when sending, maybe I can do that. I have not done this particular thing before and am trying to think of the best way.
0
 
LVL 18

Expert Comment

by:marilyng
Comment Utility
Hi jforget1,
Both SysExpert and sjef are right here... you have a large worksheet, and as usual, when people use Notes they try to make Notes into Excel.   Excel is a spreadsheet, Notes is a database.

One machine= one form

An office can have many machines, so the office is the category, and the form is the single machine.  The view will show all machines for an office, another view can show all machines by user, or by machine type or by OS.

The form is simply:

Office ID
Machine asset tag
Serial #
OS
Harddrive
and so on.
And can include - replacement date and cross reference to what it was replaced by... so now you have a view of DONE, TODO
-------------
Your views show the accumulated data any way you want to present them.

If you need to send an email, print the view to a PDF, or do a newsletter email, or have a report form that rolls everything into a rich text field, or back out to excel....

Regards!
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

Author Comment

by:jforget1
Comment Utility
I am up for using this method of doing one form for each machine. How do I import into a form if there are no records in advance. I have done imports where I reference a field, happened to be office and then imported the data based on that reference point. But how do you import where one row in the sheet is populated into a new record? Have noever done this.
0
 
LVL 63

Expert Comment

by:SysExpert
Comment Utility
simple

ub ImportXL_Reg
      
%REM
This subroutine imports data from an Excel Sheet.
Created by Dr. Nadir Patir
Istanbul Pazarlama A.S., Istanbul Turkey
E-Mail: nadir@istpaz.com.tr
This routine is used in TeamWork CRM software of author.

EXCEL SHEET FORMAT:

Row 1 of Excel Sheet must contain Field Names to be imported.
Column 1 of Excel Sheet must contain Form name.
(You can import data to different forms based on form name in column 1.)

Each row will be imported to a document.

MR 1/31/05 - adding merge options see Import_mrg_XL

%END REM
      
      Dim ws As New NotesUIWorkspace
      Dim session As New NotesSession
      Dim db As NotesDatabase
      Dim doc As NotesDocument
      Dim item As NotesItem
      Dim App As Variant, Wbook As Variant, WSheet As Variant
      Dim v As Variant
      Dim row As Double
      Dim  form As String, fileXL As String, calcf As String,  t As String
      Dim title(255) As String
      Dim k As Integer, cols As Integer
      Dim cnt As Long
      
      Set db = session.CurrentDatabase
      
      Set App = CreateObject("Excel.Application")
      App.Visible = False
      
'Choose Excel file
      v=ws.OpenFileDialog(False,"Please Select Excel File", "*.xls", "")
      If Isempty(v) Then Exit Sub
      fileXL=v(0)
      cnt=0
      
      App.Workbooks.Open fileXL
      Set Wbook = App.ActiveWorkbook
      Set WSheet = Wbook.ActiveSheet
      
      If Wsheet.Cells(1, 1).Value<>"Form" Then
            Messagebox "First Column of Excel sheet must contain Form names"
            Goto fin
      End If
      
'Recalc question
'      x(0)="Yes"
'      x(1)="No"
'      calcf= ws.Prompt( PROMPT_OKCANCELLIST, "CALC", "Calculate fields on  form during document Import?","No", x )
''      If calcf="" Then Exit Sub
      
      calcf=      "No"
      
'Read field names
      cols=1
      For k=1 To 255
            title(k)=Wsheet.Cells(1, k).Value
            If Trim(title(k))="" Then
                  cols=k-1
                  Exit For
            End If
      Next
      
'Import documents
      row=2
      form=Trim(Cstr(Wsheet.Cells(row, 1).Value))
      
      Do While form<>""
            
            Set doc=db.CreateDocument
            doc.Form = form
            For k=2 To cols
                  t=Trim(Cstr(Wsheet.Cells(row, k).Value))
                  If t<>"" Then
                        Set item = doc.ReplaceItemValue( title(k),  t )
                  End If
            Next
            
'Calculate
            If calcf="Yes" Then
                  Call doc.ComputeWithForm(False,False)
            End If
            
'Save
            Call doc.Save(True,True)
            cnt=cnt+1
            Print cnt
            
            row=row+1
            form=Trim(Cstr(Wsheet.Cells(row, 1).Value))
            
      Loop
      
fin:
      Messagebox Cstr(cnt) + " documents imported"
      App.Application.Quit
      Set App = Nothing
      Set Wbook = Nothing
      Set Wsheet = Nothing
      
End Sub
0
 

Author Comment

by:jforget1
Comment Utility
I have put this code into a view in the database and have formatted the excel sheet as advised and I have matched the header for each column to match the field names but I am getting 0 records imported. Here is a copy of the data in the spreadsheet. dlm_refresh_tracking is the name of the form I want to import into.

dlm_refresh_tracking      asset      userid      office_id
dlm_refresh_tracking      7726484      04e4551      04E
dlm_refresh_tracking      7727189      04e4571      04E
dlm_refresh_tracking      7726103      13a8701      13A
dlm_refresh_tracking      7726717      44j9591      44J
0
 
LVL 63

Expert Comment

by:SysExpert
Comment Utility
OK
1) The agent stops on the first row with no Form name.
2) The Excel sheet must be the first in a workbook.

So make sure you have no blank rows at the begnining.
Also . Open Office in Excel Format does not always work, use MS Excel.

I hope this helps !
0
 
LVL 46

Expert Comment

by:Sjef Bosman
Comment Utility
If you just want to put all rows in your Notes database, never to use the spreadsheet again, then import all data. Save the data from Excel as a Lotus 1-2-3 file, with extension .wk4, and use fieldnames as the header. It's very well described in the Help databases.

http:Q_21200019.html "import excel data into contacts"
http:Q_20723818.html "Is it possible to import and export to a Excel Spreadsheet"
0
 
LVL 18

Accepted Solution

by:
marilyng earned 500 total points
Comment Utility
Or you can paste this into an agent, shared, Target = None, security = 3

It will create a view to make sure you're not importing stuff twice, and will create a field for each title in the first row.  But, I did account for Asset, userid and office_id.  

Please make sure you read the comments and configure the CONST values.


'Import Excel:

Option Public
Option Declare

'Important set these values to your environment..........
Const str_VIEWNAME="Excel Import Check"
Const str_FORM = "Refresh Tracking"
Const str_IMPORTFILE="C:\assettracking.xls"
Const max_RECORDS=100                   'Set this to the maximum number of records you want to loop
Const max_SKIPROWS=10                    'Set this to the number of blank rows you want to skip before you assume
                                                                       'the rest of the worksheet is blank
'I am using these to concatentate three column values into a unique key to avoid importing duplicates
Const chk_COL1=2       'Asset
Const chk_COL2=3       'userid
Const chk_COL3=4       'office_id
Sub Initialize
          'Clear Lotus Notes View Of Employee Records    
      Dim session As New NotesSession
      Dim db As NotesDatabase
      Dim view As NotesView
      Dim vColl As NotesViewEntryCollection
      Dim max_Columns As Integer
      Set db = session.currentdatabase
      Set view = db.getview(str_VIEWNAME)
      If view Is Nothing Then
            Set view = CreateChkView(session)
            If view Is Nothing Then
                  Msgbox "Sorry, unable to create the check view to avoid importing duplicate documents"
                  Exit Sub
            End If
      End If
      
      Dim xlFilename As String
     'IMPORT FILENAME.............................................
      xlFilename= str_IMPORTFILE
      
      Dim doc As NotesDocument, titles As Variant
      Dim row As Integer, col As Integer, k As Integer
      Dim written As Integer            
      
      On Error Goto Handle_Error
     'First Connect to Excel and see if there are any records to import......................
      Dim Excel As Variant
      Dim xlWorkbook As Variant
      Dim xlSheet As Variant
      Print "Connecting to Excel..."
      Set Excel = CreateObject( "Excel.Application" )
      If Excel Is Nothing Then
            Print "Unable to open the excel object..."
            Exit Sub
      End If
      
      Excel.Visible = False '// Don't display the Excel window
      
      Excel.Workbooks.Open xlFilename '// Open the Excel file
      Print "Opening " & xlFilename & "..."
      Excel.Workbooks.add
      Excel.Workbooks(1).Worksheets(1).Activate
      Set xlWorkbook = Excel.Workbooks(1)
      Set xlSheet = Excel.Workbooks(1).Worksheets(1)
      Dim chk As String, skiprow As Integer
      
Records:
      Dim FieldNames(255) As String, tmpStr As String
      row = 1 '// These integers intialize to zero anyway
      col = 1
      written =0      
      Print "Starting import from Excel file..."      
      chk="xxxx"
      
      'GET FIELD NAMES IN FIRST ROW
      With xlSheet
            For k = 1 To 255
                  tmpStr =Format(.cells(row,k).value)
                  If Len(Trim(tmpStr))>0 Then
                        FieldNames(k) = tmpStr
                  Else
                        Select Case K
                        Case 1
                              Msgbox "First Row and Column in the worksheet must contain the Field Titles",,"Column Title Missing"
                              Exit Sub
                        Case Else
                              max_COLUMNS = k-1
                              Exit For
                        End Select
                        
                  End If
            Next
      End With
      
      Row = 2      
      Do While (row<=max_RECORDS)  
            With xlSheet                  
                  chk =Lcase(Trim(Format(.cells(row,chk_COL1).value) + _
                  Format(.cells(row,chk_COL2).value) + _
                  Format(.cells(row,chk_COL3).value)))
                  If Len(chk)<0 Then
                        skiprow = skiprow +1
                        Goto NextRow
                  End If                  
                  Select Case row
                  Case 1
                        'Header Row is Titles
                        
                  Case Else
                        If Len(chk)>0 Then
                        'Check to see if the document has been created
                              Set doc = view.GetDocumentByKey(chk, True)
                              If Not doc Is Nothing Then Goto NextRow
                              Set doc = New NotesDocument(db)
                              doc.Form = str_FORM  'Form name
                              'Update to Fields
                              For k = 1 To max_COLUMNS                                          
                                    Call doc.ReplaceItemValue(FieldNames(k), Format(xlsheet.cells(row, k).value))                                                
                              Next                                    
                              doc.Save True, False, True
                              Print " Creating Record..." + Cstr(row)                  
                              written = written +1
                                     'If we find a record, then set the skiprow back to 0 - it was just a blank row
                              skiprow = 0
                              Set doc = Nothing
                        End If
                  End Select                  
            End With        
NextRow:
            row= row+1
            If skiprow =>max_SKIPROWS Then
                  Print Cstr(max_SKIPROWS) + " blank rows reached... assuming the rest of the worksheet is blank..."
                  Exit Do
            End If
      Loop  
      
      Print "Disconnecting from Excel..."
      Excel.activeworkbook.close      
      Excel.Quit '// Close Excel
      Set Excel = Nothing '// Free the memory that we'd used
      view.Refresh
      Exit Sub
      
Handle_Error:
      If Err = 213 Then
            Print "Import File not found..."
            Err = 0
            Exit Sub
      End If
      On Error Goto 0
      If Not xlworkbook Is Nothing Then
            Excel.activeworkbook.close         '// Close the Excel file without saving (we made no changes)
            Excel.Quit '// Close Excel
            Set Excel = Nothing '// Free the memory that we'd used
      End If
      Exit Sub
End Sub
Sub Terminate
      
End Sub

Function CreateChkView(thisSession As NotesSession) As NotesView
      Dim tmpView As NotesView, columnCount As Integer      
      Dim strSelect As String, strCOL As String
      ColumnCount = 0
      strSelect = {SELECT ((Form = "} + str_FORM + {"));@All}
      strCOL={@LowerCase(@Text(Asset) + @Text(UserID) + @Text(OfficeID))}
      Set tmpView = thisSession.currentdatabase.CreateView(str_VIEWNAME, strSelect)
      If Not tmpView Is Nothing Then
            Dim col As NotesViewColumn, Col1 As NotesViewColumn, Col2 As NotesViewColumn, Col3 As NotesViewColumn            
            Set col = tmpView.CreateColumn(1, "KEY", strCOL)
            Col. IsSorted = True
            Set col1 = tmpView.CreateColumn(2, "Asset", "Asset")
            Set col2 = tmpView.CreateColumn(3, "UserID", "userid")
            Set col3 = tmpView.CreateColumn(4, "Office ID", "office_id")
            Set CreateChkView = tmpView            
      End If      
End Function
0
 
LVL 46

Expert Comment

by:Sjef Bosman
Comment Utility
Goodness gracious me... Can't (and on't want to) beat that :(
0
 
LVL 18

Expert Comment

by:marilyng
Comment Utility
Unfortunately, the 4.6 import code doesn't work well with newer versions of excel.  So, I just supplied one that I know works with Office 2003, and reworked it to use the data supplied.
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Problem "Can you help me recover my changes?  I double-clicked the attachment, made changes, and then hit Save before closing it.  But when I try to re-open it, my changes are missing!"    Solution This solution opens the Outlook Secure Temp Fold…
IBM Notes offer Encryption feature using which the user can secure its NSF emails or entire database easily. In this section we will discuss about the process to Encrypt Incoming and Outgoing Mails in depth.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

763 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

6 Experts available now in Live!

Get 1:1 Help Now