Tuesday, 11. October 2005
Importing MS Excel into Lotus Notes via XML -- the code05:58:36 PMWritten By : Stephan H. WisselCategory : XML
Location : Singapore
In August we published a XSLT stylesheet, that can transfom Excel XML into a more record like XML structure. The missing part is the Notes side of the exercise, which I will show today. There is a functional database in the download section. The agent code reads some values from a configuration document and applies the listed transformations to the xml before invoking the sax parser to read the values. We opted for multipe transformation runs to be able to massage the data after it has been transformed from the rather crude Excel XML form.
But you are not limited to Excel. Any XML you can transform into the <record><field>... format can be imported.
Let's look at the core of the agent code:
'make sure all conversion files are detached
Call DetachTransformationFiles(tempDir,doc,transformationNames)
'Pipelined call to XSLT Transformation an SAX Parser
Call TransformFileToSax(transformationNames,fileNames(0),doc, xsltParameters)
After detaching the XSLT files to the system temp directory we run a cascaded transform:
Function TransformFileToSax(transformationNames As Variant,fileName As String, doc As NotesDocument, Parameters List As String) As Boolean
'Transformes the input file in a series of transformation, one each for each name in transformationNames
'Then hands over the stream to the Sax parser for further processing
Dim s As New NotesSession
Dim i As Integer
Dim sourceStream As NotesStream
Dim tempDir As String
Dim transformers() As NotesXSLTransformer
Dim styleStreams() As NotesStream
Dim loopcount As Integer
Dim saxParser As NotesSAXParser
On Error Goto Err_TransformFileToSax
'Create the Import Definition
Set obj_ImportDefinition = New clsImportDefinition(doc) '<-- this is where the magic is
'Create the Sax parser
Set saxParser = s.CreateSAXParser()
On Event SAX_Characters From saxParser Call SAXCharacters
On Event SAX_EndElement From saxParser Call SAXEndElement
On Event SAX_StartElement From saxParser Call SAXStartElement
On Event SAX_EndDocument From saxParser Call SAXEndDocument
If Isempty(transformationNames) Then
'No transformation has to be done, just load a stream and hand it to the sax parser
Set sourceStream = s.CreateStream
Call sourceStream.Open(fileName)
Call saxParser.SetInput(sourceStream)
Call saxParser.Process
Goto Exit_TransformFileToSax
End If
'We need to feed the result of the first transformation to the second, second to third etc.
'We will use an array of transformers and use the pipelineing capabilities of NotesXSLT to chain them
tempDir = Environ("Temp")+"\"
loopcount = Ubound(transformationNames) 'How many loops do we have
Redim transformers(loopcount) 'We initialize the exact number of transformers...
Redim styleStreams(loopcount) 'Also the exact numbers of styles
Set sourceStream = s.CreateStream 'the source file
Call sourceStream.Open(fileName,"Unicode") 'Open the source
'Now the chaining loop <-- this is the interesting part, we chain the Notes Streams from file i/o until SAX Parser,
For i = 0 To loopcount Step 1
'Create the transformer
Set transformers(i) = s.CreateXSLTransformer
'Feed in the parameters
Forall p In Parameters
Call transformers(i).AddParameter(Listtag(p), p)
End Forall
'Create the style sheet and link it
Set styleStreams(i) = s.CreateStream
Call styleStreams(i).Open(tempDir+transformationNames(i),"Unicode")
Call transformers(i).SetStylesheet(styleStreams(i))
'Link the input
If i = 0 Then
Call transformers(i).SetInput(sourceStream)
Else
Call transformers(i).SetInput(transformers(i-1))
End If
Next
'Link the final output --- all other outputs are linked via input of the next one
Call transformers(loopcount).SetOutput(saxParser)
'Process
Call transformers(0).Process 'Just start the first one, pipelining will take care of the others
'Wind down
Call SourceStream.Close
For i = 0 To loopcount
Call styleStreams(i).close
Next
TransformFileToSax = True 'If we got here it worked
Exit_TransformFileToSax:
Exit Function
Err_TransformFileToSax:
Print Error$ + " in TransformFileToSax"
TransformFileToSax = False
Resume exit_TransformFileToSax
End Function
The Sax stuff is quite simple...
Sub SAXStartElement (Source As Notessaxparser,_
Byval elementname As String, Attributes As NotesSaxAttributeList)
Dim i As Integer
'We could be inside a record or inside a field or elsewhere
'If we are inside a record we are interested in the field
If obj_ImportDefinition.flagRecordFound Then
'We are inside a record, so we look out for <field> elements
If elementname="field" Then
'Found a field, need to record it
If Attributes.Length > 0 Then
Dim attrname As String
For i = 1 To Attributes.Length
attrname = Attributes.GetName(i)
If attrname = "name" Then
obj_ImportDefinition.flagFieldFound = True
obj_ImportDefinition.lastFieldName = Attributes.GetValue(i)
Call obj_ImportDefinition.LogInternalAction("Field: "+Attributes.GetValue(i))
Exit For
End If
Next
End If
End If
Elseif elementname = "record" Then
obj_ImportDefinition.flagRecordFound = True
Call obj_ImportDefinition.StartDocument
End If
End Sub
Sub SAXCharacters (Source As Notessaxparser, Byval Characters As String, _
Count As Long)
'Here we write the field value into the obj_ImportDefinition
If obj_ImportDefinition.flagFieldFound Then
Call obj_ImportDefinition.LogInternalAction("Chars: "+Characters)
Call obj_ImportDefinition.AddTextBuffer(Characters)
End If
End Sub
Sub SAXEndElement (Source As Notessaxparser, Byval ElementName As String)
If ElementName = "field" Then
'We need to write back the combined values
Call obj_ImportDefinition.AddCurrentField() 'Saves the last field found
obj_ImportDefinition.flagFieldFound = False 'Reset the flag
Elseif ElementName = "record" Then
Call obj_ImportDefinition.EndDocument
obj_ImportDefinition.flagFieldFound = False 'Reset the flag
obj_ImportDefinition.flagRecordFound = False 'Reset the flag
End If
End Sub
Sub SAXEndDocument (Source As NotesSAXParser)
obj_ImportDefinition.LogAction("End of XML Document reached")
Print "Documents processed: " + Cstr(obj_ImportDefinition.DocumentCount)
Call obj_ImportDefinition.ImportCompleted
Delete obj_ImportDefinition 'We don't need it anymore
End Sub
Of course the real magic happens in the obj_ImportDefinition. That's a story for another time. Meanwhile, grab the database in the download section and play yourself.
Comments :
No documents found
Site purpose and disclaimer
You consider to extend or replace your Domino infrastructure. You found a lot of information about messaging migration. You didn't find much about the applications, other than tool vendors advertisements. You realized that Domino migration is an emotional mine field. Bookmark this site, we will provide information and discuss the move from Domino to J2EE and other environments (both retaining and replacing Domino). We focus on applications, not on messaging.
The articles on this site mention products and phrases, that might be subject to copyright or trademarks. So we acknowledge, that the copyrights belong to the owner of the respective copyright or trademark.The links on this page are provided for convenience and are constitute no endorsement of the content of the target site.
So once your ready to discuss if and/or how to move away from Domino contact us.
The articles on this site mention products and phrases, that might be subject to copyright or trademarks. So we acknowledge, that the copyrights belong to the owner of the respective copyright or trademark.The links on this page are provided for convenience and are constitute no endorsement of the content of the target site.
So once your ready to discuss if and/or how to move away from Domino contact us.
