198 Lotus blogs updated hourly. Who will post next? Home | Blogs | Search | About 
 
Latest 7 Posts
Importing CSV via Java to a Notes Database
Fri, Aug 11th 2017 4
HTML5 Canvas to PNG via RPC
Tue, Jun 21st 2016 9
Eternal fustrations with IBM "Help" - - this time trying to give them money
Mon, May 23rd 2016 4
Simple Example: Bootstrap
Tue, May 3rd 2016 3
Import CSVs into a Notes/XPage database
Thu, Mar 24th 2016 5
REST via Service Bean
Tue, Feb 16th 2016 5
Reversing the display order of a Multi-value field (XPages)
Wed, Jan 13th 2016 4
Top 10
HTML5 Canvas to PNG via RPC
Tue, Jun 21st 2016 9
dataTable with Categories
Tue, Jul 14th 2015 6
dataTable column width
Tue, Jul 14th 2015 5
REST via Service Bean
Tue, Feb 16th 2016 5
Import CSVs into a Notes/XPage database
Thu, Mar 24th 2016 5
Bootstrap Progress Bars - sample database
Wed, Aug 12th 2015 4
Reversing the display order of a Multi-value field (XPages)
Wed, Jan 13th 2016 4
Eternal fustrations with IBM "Help" - - this time trying to give them money
Mon, May 23rd 2016 4
Importing CSV via Java to a Notes Database
Fri, Aug 11th 2017 4
nhttp preview won't "switch ID"
Mon, Dec 7th 2015 3


Import CSVs into a Notes/XPage database
Twitter Google+ Facebook LinkedIn Addthis Email Gmail Flipboard Reddit Tumblr WhatsApp StumbleUpon Yammer Evernote Delicious
   

We have not had a direct way to import into Notes since it became impossible to save a file in .123 format (or .wk4). I loved being able to import from a view. To work around this, some years ago I created an agent that used MSExcel and two files. That worked when I had MSExcel on my machines, but I don't any more, especially for my personal machines where I won't pay for it. So I needed to change to to import CSV files, which I can do via LibreOffice. So I updated what I had posted in 2009.
 
 The "data" file was the data to be imported, with the first row being (as is common) some description of what the column contains (e.g. "Name" "Telephone number", etc.). This first row is copied to the other spreadsheet, and in the second row, below each column is the Notes field name that column should be mapped to.

Here is a sample of data, note the first column has titles.


And here is how I would set up the mapping:



Then we just need to import, and here is the agent:
Sub Initialize
    Dim session As New NotesSession
    Dim db As NotesDatabase
    Dim doc As NotesDocument
    Dim fileName As String
    Dim lastColumn As Integer
    Dim index As Variant
    Dim lastRow As Integer
    Dim row As Integer
    Set db = session.CurrentDatabase
    Dim fileNum As Integer, cells As Integer, k As Integer
    Dim InputStr As String, delimiter As String
    fileNum% = FreeFile()
    Dim titleFileName As String
    Dim parseSize As Double
    Dim fileDataNum As Integer   
    Const titles = "c:dxlImportTitles.csv"
    Const data = "c:dxlImportData.csv"
    Const formName = "Import Form"       
    Dim q As Double
   
    titleFileName = titles
    'Column titles on first row
    'Notes field names on row 2
    delimiter = "," ' Delimiter of your file
    Dim parseArray As Variant
   
    Dim fieldArray() As String
    ReDim Preserve fieldArray(1, index)
    Open titleFileName For Input As fileNum%
    k = 0
    Do While Not EOF(fileNum%)
        Line Input #1,  InputStr$
        parseArray = Split(InputStr$, ",")
        parseSize = UBound(parseArray)
        If(k = 0) Then
            ReDim Preserve fieldArray(1, parseSize)
            q = 0
            Do Until q = parseSize + 1
                fieldArray(0, q) = parseArray(q)               
                q = q + 1
            loop
        Else
            q = 0
            Do Until q = parseSize + 1
                fieldArray(1, q) = parseArray(q)
                q = q + 1               
            Loop
        End If
        k = k + 1
    Loop
    Close fileNum%
           
    fileDataNum% = FreeFile()
    Dim dataFileName As String
    dataFileName = data
   
    Open data For Input As fileDataNum%
    k = 0
    Do While Not EOF(fileDataNum%)
        Line Input #1, InputStr$
        parseArray = Split(InputStr$, ",")
       
        If(k = 0) Then
            'first row, so the titles
            lastColumn = UBound(parseArray)       
            parseSize = UBound(parseArray)   
            Dim x As Integer, y As Integer
            y = 0
            x = 0
            Do While x < (Ubound(fieldArray, 2) + 1)
                Do While y < lastColumn + 1
                    'this determines what column as what title, therefore needs to be mapped To what Field
                    If (fieldArray(0, x) = parseArray(y)) Then
                        fieldArray(0, x) = y
                        GoTo jump
                    Else
                        y = y + 1
                    End If
                Loop
            jump:
                x = x + 1
                y = 1
            Loop   
            k = k + 1        
        Else
            'we are importing data
            x = 0
            Print k
            Set doc = db.CreateDocument
            doc.Form = formName
            Do While x < (UBound(fieldArray, 2) + 1)
                'for each column in array
                If Not (fieldArray(0,x)) = "" Then
                    y = CInt(fieldArray(0,x))
                    'Below will bring in each column value as mapped to the Field (above)
                    Call doc.ReplaceItemValue(fieldArray(1,x), parseArray(y))
                End If
                x = x + 1
            Loop
            Call doc.Save(True, False)
            k = k + 1
        End If
    Loop
    Close fileNum%
End Sub

(I might need to do a little clean up on it, I think I have a few spare Dims)

The column names on the two spreadsheets do not have to be in the same order, as you can see in the pictures, but the column titles to have to be the same. I have not made any attempt to cast case or anything, so they need to be the same case.

I've not done a lot of testing on this yet, I'm relaying on the fact the process worked great in it's previous incarnation. Hopefully this will help someone else. I have discovered that the CSV needs to be clean.

Hopefully this will help someone.

Cheers,
Brian



---------------------
http://dominoherald.blogspot.com/2016/03/import-csvs-into-notesxpage-database.html
Mar 24, 2016
6 hits



Recent Blog Posts
4
Importing CSV via Java to a Notes Database
Fri, Aug 11th 2017 8:35p   Brian M. Moore
It's been longer than I intended since my last post, but here it is. One of the things I've posted has been importing to a Notes database. My first was using LotusScript to import from an Excel file, I've moved to using CSV which does not require an external program. This one uses a Java bean and HashMap to map the field names. As with the others, this takes two files. First is a simple where the you have the data. The column titles will match up with the same row in a title spreadsheet. Th




Created and Maintained by Yancy Lent - About - Planet Lotus Blog - Advertising - Mobile Edition