- This topic has 3 replies, 1 voice, and was last updated 9 years, 2 months ago by
.
-
Posts
-
-
Hi everyone,
I'm using BTS2006 and want to write a custome pipeline component to extract data from an excel file.
this is what i got so far from other examples…
Public Sub Disassemble(ByVal pContext As Microsoft.BizTalk.Component.Interop.IPipelineContext, ByVal pInMsg As Microsoft.BizTalk.Message.Interop.IBaseMessage) Implements Microsoft.BizTalk.Component.Interop.IDisassemblerComponent.Disassemble
'Get Data from Message
Public Sub Disassemble(ByVal pContext As Microsoft.BizTalk.Component.Interop.IPipelineContext, ByVal pInMsg As Microsoft.BizTalk.Message.Interop.IBaseMessage) Implements Microsoft.BizTalk.Component.Interop.IDisassemblerComponent.Disassemble
'Get Data from Message
'Get Data from Message
Dim msgPart As IBaseMessagePart
msgPart = pInMsg.BodyPart
Dim binaryReader As New BinaryReader(msgPart.Data)
'Prepare to write ExcelSheet to temporary folder
Dim msgPart As IBaseMessagePart
msgPart = pInMsg.BodyPart
Dim binaryReader As New BinaryReader(msgPart.Data)
'Prepare to write ExcelSheet to temporary folder
Dim binaryReader As New BinaryReader(msgPart.Data)
'Prepare to write ExcelSheet to temporary folder
'Prepare to write ExcelSheet to temporary folder
Dim filename As String = System.IO.Path.GetRandomFileName
filename = filename.Remove(8) + ".xls"
Dim filename As String = System.IO.Path.GetRandomFileName
filename = filename.Remove(8) + ".xls"
".xls"
Dim binaryWriter As BinaryWriter
Dim folderName As String = "C:/MyTempExcelFiles/" 'Ohoh teh hardcoded value…
Dim binaryWriter As BinaryWriter
Dim folderName As String = "C:/MyTempExcelFiles/" 'Ohoh teh hardcoded value…
Dim folderName As String = "C:/MyTempExcelFiles/" 'Ohoh teh hardcoded value…
Dim folderNameAndFileName As String = folderName + filename
Dim fileStream As New FileStream(folderNameAndFileName, FileMode.CreateNew)
binaryWriter = New BinaryWriter(fileStream)
'Get this thing to disk
Dim folderNameAndFileName As String = folderName + filename
Dim fileStream As New FileStream(folderNameAndFileName, FileMode.CreateNew)
binaryWriter = New BinaryWriter(fileStream)
'Get this thing to disk
Dim fileStream As New FileStream(folderNameAndFileName, FileMode.CreateNew)
binaryWriter = New BinaryWriter(fileStream)
'Get this thing to disk
New BinaryWriter(fileStream)
'Get this thing to disk
'Get this thing to disk
binaryWriter.Write(binaryReader.ReadBytes(Convert.ToInt32(binaryReader.BaseStream.Length)))
binaryWriter.Close()
binaryReader.Close()
Try
Try
'Read the file
'Read the file
Dim xlWB As Excel.Workbook
xlWB = GetObject(folderNameAndFileName)
'go to the correct worksheet
Dim xlWB As Excel.Workbook
xlWB = GetObject(folderNameAndFileName)
'go to the correct worksheet
'go to the correct worksheet
Dim xlWS As Excel.Worksheet
xlWS = xlWB.Worksheets("Formular€")
'read the necessary data
Dim xlWS As Excel.Worksheet
xlWS = xlWB.Worksheets("Formular€")
'read the necessary data
"Formular€")
'read the necessary data
'read the necessary data
Dim ID As String = xlWS.Range("T_ID").Value
Dim xmlDoc As New Xml.XmlDocument
Dim head As XmlElement
head = xmlDoc.CreateElement("ContractHead")
head.SetAttribute("ID", ID)
xmlDoc.AppendChild(head)
Dim strm As New MemoryStream
xmlDoc.Save(strm)
strm.Position = 0
msgPart.Data = strm
outMsg = pContext.GetMessageFactory().CreateMessage()
outMsg.AddPart("Body", pContext.GetMessageFactory().CreateMessagePart(), True)
outMsg.BodyPart.Data = strm
'pContext.ResourceTracker.AddResource(strm)
Dim ID As String = xlWS.Range("T_ID").Value
Dim xmlDoc As New Xml.XmlDocument
Dim head As XmlElement
head = xmlDoc.CreateElement("ContractHead")
head.SetAttribute("ID", ID)
xmlDoc.AppendChild(head)
Dim strm As New MemoryStream
xmlDoc.Save(strm)
strm.Position = 0
msgPart.Data = strm
outMsg = pContext.GetMessageFactory().CreateMessage()
outMsg.AddPart("Body", pContext.GetMessageFactory().CreateMessagePart(), True)
outMsg.BodyPart.Data = strm
'pContext.ResourceTracker.AddResource(strm)
Dim xmlDoc As New Xml.XmlDocument
Dim head As XmlElement
head = xmlDoc.CreateElement("ContractHead")
head.SetAttribute("ID", ID)
xmlDoc.AppendChild(head)
Dim strm As New MemoryStream
xmlDoc.Save(strm)
strm.Position = 0
msgPart.Data = strm
outMsg = pContext.GetMessageFactory().CreateMessage()
outMsg.AddPart("Body", pContext.GetMessageFactory().CreateMessagePart(), True)
outMsg.BodyPart.Data = strm
'pContext.ResourceTracker.AddResource(strm)
Dim head As XmlElement
head = xmlDoc.CreateElement("ContractHead")
head.SetAttribute("ID", ID)
xmlDoc.AppendChild(head)
Dim strm As New MemoryStream
xmlDoc.Save(strm)
strm.Position = 0
msgPart.Data = strm
outMsg = pContext.GetMessageFactory().CreateMessage()
outMsg.AddPart("Body", pContext.GetMessageFactory().CreateMessagePart(), True)
outMsg.BodyPart.Data = strm
'pContext.ResourceTracker.AddResource(strm)
"ContractHead")
head.SetAttribute("ID", ID)
xmlDoc.AppendChild(head)
Dim strm As New MemoryStream
xmlDoc.Save(strm)
strm.Position = 0
msgPart.Data = strm
outMsg = pContext.GetMessageFactory().CreateMessage()
outMsg.AddPart("Body", pContext.GetMessageFactory().CreateMessagePart(), True)
outMsg.BodyPart.Data = strm
'pContext.ResourceTracker.AddResource(strm)
"ID", ID)
xmlDoc.AppendChild(head)
Dim strm As New MemoryStream
xmlDoc.Save(strm)
strm.Position = 0
msgPart.Data = strm
outMsg = pContext.GetMessageFactory().CreateMessage()
outMsg.AddPart("Body", pContext.GetMessageFactory().CreateMessagePart(), True)
outMsg.BodyPart.Data = strm
'pContext.ResourceTracker.AddResource(strm)
Dim strm As New MemoryStream
xmlDoc.Save(strm)
strm.Position = 0
msgPart.Data = strm
outMsg = pContext.GetMessageFactory().CreateMessage()
outMsg.AddPart("Body", pContext.GetMessageFactory().CreateMessagePart(), True)
outMsg.BodyPart.Data = strm
'pContext.ResourceTracker.AddResource(strm)
"Body", pContext.GetMessageFactory().CreateMessagePart(), True)
outMsg.BodyPart.Data = strm
'pContext.ResourceTracker.AddResource(strm)
'pContext.ResourceTracker.AddResource(strm)
Catch ex As Exception
folderName = "C:/MyTempExcelFiles/Errors/" 'Ohoh teh hardcoded value…
Catch ex As Exception
folderName = "C:/MyTempExcelFiles/Errors/" 'Ohoh teh hardcoded value…
"C:/MyTempExcelFiles/Errors/" 'Ohoh teh hardcoded value…
folderNameAndFileName = folderName + filename
fileStream = New FileStream(folderNameAndFileName, FileMode.CreateNew)
binaryWriter = New BinaryWriter(fileStream)
'Get this thing to disk
New FileStream(folderNameAndFileName, FileMode.CreateNew)
binaryWriter = New BinaryWriter(fileStream)
'Get this thing to disk
New BinaryWriter(fileStream)
'Get this thing to disk
'Get this thing to disk
binaryWriter.Write(binaryReader.ReadBytes(Convert.ToInt32(binaryReader.BaseStream.Length)))
binaryWriter.Close()
End Try
End Try
i = 1
End Sub
End Sub
Public Function GetNext(ByVal pContext As Microsoft.BizTalk.Component.Interop.IPipelineContext) As Microsoft.BizTalk.Message.Interop.IBaseMessage Implements Microsoft.BizTalk.Component.Interop.IDisassemblerComponent.GetNext
If i = 1 Then
Public Function GetNext(ByVal pContext As Microsoft.BizTalk.Component.Interop.IPipelineContext) As Microsoft.BizTalk.Message.Interop.IBaseMessage Implements Microsoft.BizTalk.Component.Interop.IDisassemblerComponent.GetNext
If i = 1 Then
If i = 1 Then
Return outMsg
i = 0
Else
Return outMsg
i = 0
Else
Else
Return Nothing
Return Nothing
End If
End If
but it just does not work… anyone got an idea what i'm missing or how to do it in a better way?
-
Seems like i'm going to the right direction. I dropped the iDisassambler Interface and implemented IComponent. it runs through debgugging without error… the only thing is that i get at the en an error whicht says Source : PipelineObjects| Message: Invalid Pointer | HRESULT 80004003
any suggestions?
-
Can you transform the Excel to XML and get better access? Seeing you gong to binary seems like a difficult road…
Another possibility is to write an extract routine in the spreadsheet with VBA and have it produce the file for Biztalk.
You can register the Spreadsheet as a Datasource and access that way…
These are just some other ideas if your initial approach is unworkable.
-wa
-
I realize you are rolling your own component to achieve this, but you might want to take a look at FarPoint Spread for BizTalk. Spread for BizTalk is a BizTalk Server 2006 Pipeline Disassembler that parses Excel XLS files into XML that map to schemas created through a Spreadsheet Schema Wizard that is integrated into Visual Studio 2005.
Please check out our Spread for BizTalk Resource Center: http://www.FarPointSpread.com/BizTalk.
-Robby
-
-
- The forum ‘BizTalk 2004 – BizTalk 2010’ is closed to new topics and replies.