193 Lotus blogs updated hourly. Who will post next? Home | Blogs | Search | About 
 
Latest 7 Posts
HTML5 Canvas to PNG via RPC
Tue, Jun 21st 2016 6
Eternal fustrations with IBM "Help" - - this time trying to give them money
Mon, May 23rd 2016 7
Simple Example: Bootstrap
Tue, May 3rd 2016 5
Import CSVs into a Notes/XPage database
Thu, Mar 24th 2016 6
REST via Service Bean
Tue, Feb 16th 2016 5
Reversing the display order of a Multi-value field (XPages)
Wed, Jan 13th 2016 8
nhttp preview won't "switch ID"
Mon, Dec 7th 2015 8
Top 10
nhttp preview won't "switch ID"
Mon, Dec 7th 2015 8
Reversing the display order of a Multi-value field (XPages)
Wed, Jan 13th 2016 8
dataTable with Categories
Tue, Jul 14th 2015 7
dataTable column width
Tue, Jul 14th 2015 7
DirectoryNavigator via Java
Wed, Nov 4th 2015 7
Eternal fustrations with IBM "Help" - - this time trying to give them money
Mon, May 23rd 2016 7
StartKeys for categorized viewPanel and number column
Sat, Jul 4th 2015 6
Populate an InputText Date field (that has a calendar picker)
Fri, Jun 26th 2015 6
First 2 repeat values on the same row - a simple example
Thu, Jun 4th 2015 6
Bootstrap Progress Bars - sample database
Wed, Aug 12th 2015 6


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
7 hits



Recent Blog Posts
6
HTML5 Canvas to PNG via RPC
Tue, Jun 21st 2016 1:21p   Brian M. Moore
Declan Lynch provided a Signature Capture Control on OpenNTF some time ago. I had downloaded and played with it a bit, but hadn't had a production use for it, but it worked just like it said on the tin - drop it in and use it. Recently I was asked to come up with a way to let people sign into an event using tablets, so a perfect opportunity to pull it out. It was a breeze to add it to the sign-in portion, we display a page on a mobile device and the user can sign in on the canvas. That was the
7
Eternal fustrations with IBM "Help" - - this time trying to give them money
Mon, May 23rd 2016 11:22a   Brian M. Moore
So I find I need to purchase a Domino license again, this happens for independent developers. I make my selection and get taken to what IBM is now calling the "Marketplace" to check out. However the option to enter a credit card to actually pay for my purchase is greyed out. So I call in. There is a wait and a lady answers. She asks the typical questions and then for me to send them an email with a screen shot. I ask for a ticket number so I can track this request (my reopened ticket for Bluem
5
Simple Example: Bootstrap
Tue, May 3rd 2016 8:23p   Brian M. Moore
The Bootstrap library is a great way to do responsive design, and it's been incorporated into the Extension Library so you can use it "out of the box". The problem I've found is that the samples provided are pretty complex. Not too helpful if you are starting out since you have to try to figure out callbacks and a lot of other stuff to get to the points you want. I think overly complex starter examples are a waste. They let the creator think they have provided something without actually hel




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