.nsf data extraction

HCL Domino Domino Forum
1

I have no idea where to put this question… So I will start here.

The issue.

I have a finished goods .nsf file that I have to extract data from. So I installed designer and the notes client on my workstation. I then copied over my .nsf files into the Data folder. Double clicked on my nsf file and it opened up in Notes. So far so good. *Note we do not have a running notes server this was shut down and I cannot bring it back (company was sold off). Now, I have read online that you can go to the file->export from a view and save off the data as a CSV file inside of the notes client (I tried this and it worked). However, the view that I would need to extract all the data isn’t a view listed. So from there I double click one of my items and it came up in the data form… I went to export that as a csv file but that wasn’t an option… I exported it to a text file, but that format does me no good… The data looked like an export of the form. Now I thought I could format with a vb.net app or macro. However this form uses tabs in it… when you export that it makes a huge mess to parse. So next, I thought I would try and make the view and display everything that I need.

From here I opened up Domino Designer and opened the FG in the applications explorer. I see all the forms etc. So far so good. So I went into my designer and into the views, tried to create a new view (which seemed to just copy other views at first… then I saw the new blank view). I tried the new blank view and I can not figure out how to connect to anything to pick my columns. So I tried to create a new view based off a old one. Did the insert column and once again I don’t see any fields to hook to. So at this point I’m stuck. I read online that maybe SQLNotes would work. So I downloaded SQLNotes and I keep getting errors on this application when I try to make a user DSN. So another wall. I would just say I would hand key the data but when I do my data extract I have over 1000 items and when I look at the details of each item in the form in my text editor it tells me I have over 315 lines of formatted data. That’s a ton of hand keying. Anyone have any suggestions? If this is the wrong place please point me to a place to post this. I’m not finding a ton of resources online to do this type of stuff.

2

Subject: You’re in the right place but…

… you’'re going about this wronglyYou need to define which data fields you need from your various forms, and confirm is CSV the correct output format for you?

What do you do about multi-value fields or rich text fields or attachments?

This is a fairly simple design job, but you’re not going to get to it without then assistance of a Notes developer

Where are you based?

3

Subject: reply

I’m based near SLC Utah and having a notes person come in on a fee isn’t an option. Most of the data lines up correctly. for example item number: 1234 but I do have an issue when it comes to a tab section called plant. it looks like thisplant | 1 | 2 | 3 | 4 | 5 |

branch | site1| site2| …

so when I do the export it looks like this

Plant

1

2

3

4

5

BRANCH site1 site2 …

So I was hoping to make the view have a header of Branch1, Branch 2 etc. Like I said I’m not finding a ton of resources out there on the web. (I’m sure it’s just because I don’t know where to look.) My next idea was going to have a windows macro create each file and then I was going to write a c# parser to rip though all of them and grab the data… but i’m worried it will miss stuff since I’m looking at over 1000 items. The other idea was just to down load them as Rich Text and save them in sharepoint and hand key in the items they need. But I feel there has to be a way to extract this data in notes.

4

Subject: Lotusscript

It is trivial to write a CSV exporter in Lotusscript. I even posted some code on my blog that you can use for free, exporting a view as CSV:Export Notes view to Excel – with multi-value fields – TexasSwede

You could use this code as a base for your exporter, or write something from scratch.

Here is a CSV exporter I wrote for work yesterday, by the way. It is a quick hack, so not very pretty. I am thinking about writing a generic CSV export class, I could have some use for that myself. Keep an eye on my blog (http://blog.texasswede.com) the next few days. :slight_smile:

Here is the code:

Sub Initialize

Dim session As New NotesSession

Dim db As NotesDatabase

Dim view As NotesView

Dim col As NotesViewEntryCollection

Dim entry As NotesViewEntry

Dim doc As NotesDocument

Dim claimnumber As String

Dim cnt As Long

Dim row As String

Dim i As Integer

Dim filename As String





Set db = New NotesDatabase("YourServer/Domain","database.nsf")	

Print "Creating CSV file..."

filename = "P:\Legal_ClaimantData.csv"

Open filename For Output As #1

'*** Print header to CSV file

row = |"ClaimNumber","Claimant","Received","Created","Venue","State",|

row = row & |"PlaintiffName(s)","PlaintiffCounsel","PlaintiffFirm","PlaintiffFirmAddress","PlaintiffFirmCity","PlaintiffFirmState","PlaintiffFirmZIP",|

row = row & |"DefendantName(s)","DefendantCounsel","DefendantFirm","DefendantFirmAddress","DefendantFirmCity","DefendantFirmState","DefendantFirmZIP"|

Print #1, row

'*** Get all documents and export them as CSV

Set view = db.GetView("LookupLegalSummaryEntries")

Set col = view.AllEntries

Set entry = col.GetFirstEntry()

Do While Not entry Is Nothing

	cnt = cnt + 1

	If cnt Mod 100 = 0 Then

		Print "Entry " & cnt

	End If

	Set doc = entry.Document

	Call doc.Computewithform(True,False)		

	claimnumber = doc.GetItemvalue("ClaimNumber")(0)

	If claimnumber<>"" Then

	row = |"| & claimnumber & |","| & doc.GetItemValue("Claimant")(0) 

	If IsDate(doc.GetItemValue("Received_Date")(0)) Then

		row = row & |",| & Format$(CDat(doc.GetItemValue("Received_Date")(0)),"mm/dd/yyyy")

	Else

		row = row & |",| & ""

	End If

	row = row & |,| & Format$(CDat(doc.Created),"mm/dd/yyyy")

	row = row & |,"| & doc.GetItemValue("LegalVenue")(0)

	row = row & |","| & doc.GetItemValue("LegalVenueState")(0)

	

	row = row & |","| & Join(doc.GetItemValue("PlaintiffNameList"),",")

	row = row & |","| & doc.GetItemValue("PlaintiffCounsel")(0)

	row = row & |","| & doc.GetItemValue("PlaintiffFirm")(0)

	row = row & |","| & doc.GetItemValue("PlaintiffFirmAddress")(0)

	row = row & |","| & doc.GetItemValue("PlaintiffFirmCity")(0)

	row = row & |","| & doc.GetItemValue("PlaintiffFirmState")(0)

	row = row & |","| & doc.GetItemValue("PlaintiffFirmZIP")(0)

	

	row = row & |","| & Join(doc.GetItemValue("DefendantNameList"),",")

	row = row & |","| & doc.GetItemValue("DefendantCounsel")(0)

	row = row & |","| & doc.GetItemValue("DefendantFirm")(0)

	row = row & |","| & doc.GetItemValue("DefendantFirmAddress")(0)

	row = row & |","| & doc.GetItemValue("DefendantFirmCity")(0)

	row = row & |","| & doc.GetItemValue("DefendantFirmState")(0)

	row = row & |","| & doc.GetItemValue("DefendantFirmZIP")(0) & |"|

	

	Print #1, Replace(Replace(row,Chr$(13),"\r"),Chr$(10),"")

	End If 

	Set entry = col.GetNextEntry(entry)

Loop

Close #1	

MsgBox "Exported legal status data to " & filename

End Sub

5

Subject: thank you!

Thank you! quick question do I need to have a server up and running to run this?

6

Subject: Also…

If you need a more advanced exporter, that can detach attachments and embedded images, and saves the data in XML files, you can look at a tool I wrote a few years ago:Notes XML Exporter

I charge for this tool, though.

7

Subject: No.

You can access a local database the same way as on a server, just set the sever name to “”.

8

Subject: com objects.

You can also use the COM objects…

…that seems to be the easiest for me when I export sites into SharePoint.

There are many things you can do to handle specifics of RTItems with COM.

Using a C# program, you can use Lotus’s COM objects to navigate Lotus Notes data, then use SharePoint’s Client Object Model to create new SP entries.

As for visually specific items I use a mixture of a PDF exporter (such as BullZip) that can be scripted (LotusScript) to print with the documentUNID, then collect and attached the PDF’s from a shared folder to embedd into a SharePoint.

Just note that SharePoint seems to have more limitations that there are pains in discovering when trying to shove decades of Lotus Notes data into it. One of my angst is the fixed number of maximum number of SP items for each view where are administrators set at 2,000 for “performance” reasons. Very frustrating when you can easily have Lotus Notes view with more than 10,000 of documents.

-Kyle