Importing CSV via Java to a Notes Database

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. The title spreadsheet has two rows. The first has the column titles form the first and the second is the field name you want to import to.

Here is a sample of the import title:

Alpha,Beta,Gamma,Delta,Epsilon
AlphaField,BetaField,GammaField,DeltaField,EpsilonField

Here is a sample of the data file:

Alpha,Beta,Gamma,Delta,Epsilon
First1,Second1,Third1,Forth1,Fifth1
First2,Second2,Third2,Forth2,Fifth2

Here is the bean:

package com.something;

import java.io.*;

import java.util.*;

import javax.faces.context.*;

import lotus.domino.*;

import org.apache.commons.lang.*;

public class ImportMap implements Serializable {

private static final long serialVersionUID = 1L;

public ImportMap() {

}

public void ImportMappedData(String dataFile, String fieldMapFile, String formName) {

try {

BufferedReader fieldMapReader = new BufferedReader(new FileReader(fieldMapFile));

String fieldMapTitleLine = fieldMapReader.readLine();

String fieldMapFieldLine = fieldMapReader.readLine();

// will need to account for different numbers of columns

HashMap hm = new LinkedHashMap();

String[] splitTitle = fieldMapTitleLine.split(",");

String[] splitField = fieldMapFieldLine.split(",");

for (int i = 0; i < splitTitle.length; i++) {

hm.put(splitTitle[i], splitField[i]);

// System.out.println("Title: " + splitTitle[i]);

}

// Get a set of the entries

Set set = hm.entrySet();

// Get an iterator

Iterator i = set.iterator();

// Display elements

while (i.hasNext()) {

Map.Entry me = (Map.Entry) i.next();

}

BufferedReader dataMapReader = new BufferedReader(new FileReader(dataFile));

String dataMapTitleLine = dataMapReader.readLine();

String dataMapDataLine = dataMapReader.readLine();

String[] splitDataTitle = dataMapTitleLine.split(",");

Session session = (Session) getVariableValue("session");

Database db = session.getCurrentDatabase();

lotus.domino.Document importDoc = null;

while (dataMapDataLine != null) {

boolean saveDoc = false;

importDoc = db.createDocument();

importDoc.replaceItemValue("form", formName);

String[] dataMapData = dataMapDataLine.split(",");

for (int j = 0; j < splitDataTitle.length; j++) {

if (hm.containsKey(splitDataTitle[j])) {

try {

saveDoc = true;

importDoc.replaceItemValue(StringUtils.trimToEmpty(hm.get(splitDataTitle[j]).toString()), StringUtils.trimToEmpty(dataMapData[j]));

} catch (Exception Ae) {

// System.out.println("no value to write for " + splitDataTitle[j] );

}

}

}

if (saveDoc) {

importDoc.save();

}

importDoc.recycle();

dataMapDataLine = dataMapReader.readLine();

}

incinerate(importDoc, db);

} catch (Exception e) {

e.printStackTrace();

}

}

private void incinerate(Object... dominoObjects) {

for (Object dominoObject : dominoObjects) {

if (null != dominoObject) {

if (dominoObject instanceof Base) {

try {

((Base) dominoObject).recycle();

} catch (NotesException recycleSucks) {

// optionally log exception

}

}

}

}

}

public static Object getVariableValue(String varName) {

FacesContext context = FacesContext.getCurrentInstance();

return context.getApplication().getVariableResolver().resolveVariable(context, varName);

}

}

This one uses the Apache String Utils to strip out the extra spaces that might be in, and leave an empty string if nothing is found. You can take that out if you don't have the Jar or don't want to use it. My import process doesn't account for commas within a quoted string. I know that should be part of the process but it wasn't needed for my project. I'd like to find a way to address it. Currently I'm just using Split to break each row apart based on commas, but at some point I'd like to create something (possibly using RegEx) that accounts for the commas in a quoted string. I would suggest running something to remove extra line breaks. I had to write a script to remove them from Numbers. Otherwise you may get partial lines trying to import.

Cheers,
Brian