Excel Import Opinion

Posted on 2006-04-25
Last Modified: 2013-12-18
I have seen many posts and code on importing an excel spreadsheet, however not being a script guy myself, I would like an opinion on the best, simplest code to use. And in that opinion, show me where to put my column data which will be(DEPT,FP,S_A,IPL_CODE,BATCH,DESCRIPTOR,QTY,G,START_DATE,DUE_DATE,TYPE).

I want to use it in a button in a view or an agent, whatever would work best. I want it to overwrite everything in the view.

Also, when using these excel import scripts, do I have to make sure column widths are the same in the spreadsheet like when I am importing from a lotus spreadsheet?
Question by:schmad01
    LVL 18

    Expert Comment

    Hi schmad01,,289483,sid4_gci822343,00.html?track=NL-47&ad=541560 is good, and easy to follow.

    This one, imports the excel file and creates a check view of the imported stuff.  The only thing you need to do is create the Notes form, and include your fields: Dept, FP, S_A, IPL_CODE, BATCH.. etc. on the form.  (Or if you never want to view the FORM, just create the form with minimal fields, createdby createddate, form field.  The code will create the form and display it in the Excel Import Check view.

    Option Public
    Option Declare

    'Important set these values to your environment..........
    Const str_VIEWNAME="Excel Import Check"      
    Const str_FORM = "Refresh Tracking"                 'Change this to your form name
    Const str_IMPORTFILE="C:\assettracking.xls"    'Change this to your import file name
    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 is excel column 2
    Const chk_COL2=3       'userid  is excel column 3
    Const chk_COL3=4       'office_id  is excel column 4

    Sub Initialize
            'Clear Lotus Notes View Of  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 & "..."
        Set xlWorkbook = Excel.Workbooks(1)
        Set xlSheet = Excel.Workbooks(1).Worksheets(1)
        Dim chk As String, skiprow As Integer
        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..."    
        With xlSheet
            For k = 1 To 255
                tmpStr =Format(.cells(row,k).value)
                If Len(Trim(tmpStr))>0 Then
                    FieldNames(k) = tmpStr
                    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
        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) + _
                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))                                
                        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      
            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
        Print "Disconnecting from Excel..."
        Excel.Quit '// Close Excel
        Set Excel = Nothing '// Free the memory that we'd used
        Exit Sub
        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


    As long as you have the form with the field names = col names, then you can format as you see fit and add more columns.


    Author Comment

    Ok, now where in the above code do I put the column-field names. Just show me one, and I can do the rest.  Thanks.

    Author Comment

    Oh, and do the colum widths have to match between the spreadsheet and the view?
    LVL 18

    Accepted Solution

    hee. hee.  it's dynamic.  whatever your first row column names are become the fields for the document import.  The only problem might be if the column name has spaces in it, in which case you might want to test and replace with an underline.

    FieldNames(k) is the array of column names taken from the excel worksheet.

                      For k = 1 To max_COLUMNS                            
                            Call doc.ReplaceItemValue(FieldNames(k), Format(xlsheet.cells(row, k).value))                                

    if you want to change the names, then you just have to make sure you have a parallel array with the Notes Field equivalent.

    For instance:

    fieldnames(1) = "col1value"
    fieldnames(2) = "col2value"
    fieldnames(3) = "col3value"

    Your note form might have:
    Dim notenames() as variant
    redim notenames(3)
    notenames(1) = "Department"
    notenames(2) = "FP"
    notenames(3) = "SA"

    so that notenames = fieldnames

    Then the code can be interchangeable:
                        For k = 1 To max_COLUMNS                            
                            Call doc.ReplaceItemValue(notenames(k), Format(xlsheet.cells(row, k).value))                                

    But that would hard code the dynamic agent... so, another alternative is to just replace the column names with the Notes fields in the excel spreadsheet.

    No the column widths don't have to match the view widths, you set the view properties independent of the spreadsheet (since excel is a spreadsheet and Notes is a database, they are two different animals)


    Author Comment

    I found this code and I got it to work.  The problem is that it only imported 30 records when there are 61 rows in the spreadsheet. Any idea why?  Here is the code I am using:

    Dim ws As New NotesUIWorkSpace
          Dim ses As New NotesSession
          Dim db  As NotesDatabase
          Dim view As NotesView
          Dim doc As NotesDocument
          Dim App As Variant, Wbook As Variant, WSheet As Variant
          Dim RetVal As Variant
          Dim XLfile As String
          Dim row As Long
          If Dir$(defDir,16) = "" Then
                Mkdir defDir
          End If
         'Choose Excel file
          XLfile = "o:\data\spreadsheets\schedwet.xls"
         'get Excel Object
          Print "Preparing to import..."
          Set App = CreateObject("Excel.Application")
          If App Is Nothing Then
                Msgbox "You do not have Excel installed.", 0 + 16, "Import from excel"
                Exit Sub
          End If
          App.Visible = False
          App.Workbooks.Open XLFile
          Set Wbook = App.ActiveWorkbook
          Set WSheet = Wbook.ActiveSheet
          Set db = ses.CurrentDatabase
          Set view =  db.GetView("YieldImport")
          Dim importCnt As Long
          importCnt = 0
          Print "Importing..."
          Dim fileNum As Integer
          Dim fileName As String
          fileNum = Freefile()
          fileName = defdir & "\Excel_Import_Log" & Format(Today,"ddmmyy") & ".txt"
          Open fileName For Output As filenum
          row = 2 '<--- skip the column header use 1 if your file has no column header
          While Trim(Cstr(WSheet.Cells(row, 1).Value)) <> ""      
    '-----> check if doc already exists
                Dim dummyDoc As NotesDocument
                Call view.Refresh
                Set dummyDoc = view.GetDocumentByKey(Trim(Cstr(WSheet.Cells(row, 1).Value)))          
                If dummyDoc Is Nothing Then
    '-----> check if doc already exists                        
                      Set doc = db.CreateDocument
                      doc.form = "YieldSheet1"
                      doc.dept = Trim(Cstr(WSheet.Cells(row, 1).Value))
                      doc.fp = Trim(Cstr(WSheet.Cells(row, 2).Value))
                      doc.s_a = Trim(Cstr(WSheet.Cells(row, 3).Value))
                      doc.ipl_code = Trim(Cstr(WSheet.Cells(row, 4).Value))
                      doc.batch = Trim(Cstr(WSheet.Cells(row, 5).Value))
                      doc.descriptor = Trim(Cstr(WSheet.Cells(row, 6).Value))
                      doc.qty = Trim(Cstr(WSheet.Cells(row, 7).Value))
                      doc.g = Trim(Cstr(WSheet.Cells(row, 8).Value))
                      doc.startdate = Trim(Cstr(WSheet.Cells(row, 9).Value))
                      doc.due_date = Trim(Cstr(WSheet.Cells(row, 10).Value))
                      doc.type = Trim(Cstr(WSheet.Cells(row, 11).Value))
             True, True    
                      importCnt = importCnt + 1
                      Print #fileNum, "Record already exists :" & Trim(Cstr(WSheet.Cells(row, 1).Value))
                End If
                row = row + 1
          Close fileNum
          Set App = Nothing
          Set Wbook = Nothing
          Set Wsheet = Nothing
          Msgbox "Successfully imported " & Cstr(importCnt) & " record(s).", 0 + 64, "Import from excel"
    LVL 18

    Expert Comment

    It hit a blank value in column 1 of the 31 row.

    Author Comment

    I got it.  I decided to put the batch as the first column because that data is always different. It was picking up duplicate data in column 1 and not creating new records for the duplicates. Changed the column order in the spreadsheet to match and updated the code.

    Author Comment

    I am going to post another question to follow up on this.  There is something else I want to accomplish, but it is worthy of being a new question.

    Author Comment

    Thanks for your help lately.
    LVL 18

    Expert Comment

    Not a problem, whatever works for you.  If you get a chance, do explore the module import.. it's a bit more robust than the one you selected.

    Author Comment

    I will explore the other module, but since time is of the essence(when is it not?), I will use this one for now.  And now part 2 question coming.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Notes Document Link used by IBM Notes is a link file which aids in the sharing of links to documents in email and webpages. The posts describe the importance and steps to create a Lotus Notes NDL file in brief.
    This article covers general Notes 8.5 troubleshooting information including recreating the Notes\Data folder.
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    759 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