• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 421
  • Last Modified:

Excel Import Opinion

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?
  • 7
  • 4
1 Solution
Hi schmad01,
http://searchdomino.techtarget.com/tip/1,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.

schmad01Author Commented:
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.
schmad01Author Commented:
Oh, and do the colum widths have to match between the spreadsheet and the view?
Independent Software Vendors: 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!

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)

schmad01Author Commented:
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))
                  doc.save 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"
It hit a blank value in column 1 of the 31 row.
schmad01Author Commented:
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.
schmad01Author Commented:
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.
schmad01Author Commented:
Thanks for your help lately.
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.
schmad01Author Commented:
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.

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 7
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now