Link to home
Start Free TrialLog in
Avatar of David Schmalzer
David SchmalzerFlag for United States of America

asked on

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?
Avatar of marilyng
marilyng

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 & "..."
    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

--------------------------

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.

Regards!
Avatar of David Schmalzer

ASKER

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.
Oh, and do the colum widths have to match between the spreadsheet and the view?
ASKER CERTIFIED SOLUTION
Avatar of marilyng
marilyng

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
            Else
                  Print #fileNum, "Record already exists :" & Trim(Cstr(WSheet.Cells(row, 1).Value))
            End If
            row = row + 1
      Wend
      Close fileNum
      App.Application.Quit
      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.
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.
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.
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.
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.