Hi,

I need to convert/import data from Excel file to a XML. I cannot use any 3rd party service. Is there a way around? 

Thank you.

Regards,

Pri

Look at codeplex https://excel2007pipeline.codeplex.com/ which gives you a pipeline component to read Excel Files and convert them into XML.

If however this has nothing to do with BizTalk then have a look at https://support.microsoft.com/en-us/kb/305795 on HOW TO: Import and Export XML into Excel.

Regards.

Thanks for the reply Shanycheil.

Unfortunately the 1st link uses 3rd party services (using ICSharpCode.SharpZipLib.Zip) (using IntegrationExperts.Samples.PipelineComponents.Helpers). Also, the second link was not of great help. I am looking for a Biztalk custom pipeline component.

Thank you.

Hi Priya,

In this link you have a big list of pipeline components that can be used in BizTalk.

http://social.technet.microsoft.com/wiki/contents/articles/11679.biztalk-list-of-custom-pipeline-components.aspx

There are some Excel Pipeline components. Maybe one of them could be useful for your needs.

Regards.

Hi Priya,

If you can get the data into a CSV, it is pretty easy using PowerShell

#Example of using CSV data into XML

Get-Process | Export-Csv ProcessLog.csv
Import-csv .\ProcessLog.csv | ft
Import-csv .\ProcessLog.csv | Export-Clixml .\ProcessLog.xml
.\ProcessLog.xml


#If you already have the Excel CSV
Import-csv .\ProcessLog.csv | Export-Clixml .\ProcessLog.xml

Windows PowerShell: Text, XML and CSVOh My:
https://technet.microsoft.com/en-us/magazine/jj203552.aspx

Have a look at https://msdn.microsoft.com/en-us/library/y1xatbkd%28v=vs.80%29.aspx?f=255&MSPPError=-2147217396 on Automating Application using the Office Object Model and https://support.microsoft.com/en-us/kb/302084 on How to Automate Excel from C#

It is my understanding however that there are certain restrictions/limitation to using the Office Object Model within the pipeline architecture of BizTalk. The "Considerations for server-side Automation of Office" article at https://support.microsoft.com/en-us/kb/257757 talks about those and provide alternates such as OpenXML Formats FOP, etc.

Regards.

• Edited by ShankycheilMicrosoft community contributor 19 hours 53 minutes ago EDIT

The first link uses ICSharpCode.SharpZipLib.Zip for unzipping..

You can very well remove the unzip part and not use that. You can look at the code that transforms Excel to XML and use that in ur pipeline.

Have a look at https://msdn.microsoft.com/en-us/library/y1xatbkd%28v=vs.80%29.aspx?f=255&MSPPError=-2147217396 on Automating Application using the Office Object Model and https://support.microsoft.com/en-us/kb/302084 on How to Automate Excel from C#

It is my understanding however that there are certain restrictions/limitation to using the Office Object Model within the pipeline architecture of BizTalk. The "Considerations for server-side Automation of Office" article at https://support.microsoft.com/en-us/kb/257757 talks about those and provide alternates such as OpenXML Formats FOP, etc.

Regards.

• Edited by ShankycheilMicrosoft community contributor Wednesday, July 29, 2015 11:11 AM EDIT

Have a look at https://msdn.microsoft.com/en-us/library/y1xatbkd%28v=vs.80%29.aspx?f=255&MSPPError=-2147217396 on Automating Application using the Office Object Model and https://support.microsoft.com/en-us/kb/302084 on How to Automate Excel from C#

It is my understanding however that there are certain restrictions/limitation to using the Office Object Model within the pipeline architecture of BizTalk. The "Considerations for server-side Automation of Office" article at https://support.microsoft.com/en-us/kb/257757 talks about those and provide alternates such as OpenXML Formats FOP, etc.

Regards.

• Edited by ShankycheilMicrosoft community contributor Wednesday, July 29, 2015 11:11 AM EDIT

Have a look at https://msdn.microsoft.com/en-us/library/y1xatbkd%28v=vs.80%29.aspx?f=255&MSPPError=-2147217396 on Automating Application using the Office Object Model and https://support.microsoft.com/en-us/kb/302084 on How to Automate Excel from C#

It is my understanding however that there are certain restrictions/limitation to using the Office Object Model within the pipeline architecture of BizTalk. The "Considerations for server-side Automation of Office" article at https://support.microsoft.com/en-us/kb/257757 talks about those and provide alternates such as OpenXML Formats FOP, etc.

Regards.

• Edited by ShankycheilMicrosoft community contributor Wednesday, July 29, 2015 11:11 AM EDIT

Have a look at https://msdn.microsoft.com/en-us/library/y1xatbkd%28v=vs.80%29.aspx?f=255&MSPPError=-2147217396 on Automating Application using the Office Object Model and https://support.microsoft.com/en-us/kb/302084 on How to Automate Excel from C#

It is my understanding however that there are certain restrictions/limitation to using the Office Object Model within the pipeline architecture of BizTalk. The "Considerations for server-side Automation of Office" article at https://support.microsoft.com/en-us/kb/257757 talks about those and provide alternates such as OpenXML Formats FOP, etc.

Regards.

• Edited by ShankycheilMicrosoft community contributor Wednesday, July 29, 2015 11:11 AM EDIT

Have a look at https://msdn.microsoft.com/en-us/library/y1xatbkd%28v=vs.80%29.aspx?f=255&MSPPError=-2147217396 on Automating Application using the Office Object Model and https://support.microsoft.com/en-us/kb/302084 on How to Automate Excel from C#

It is my understanding however that there are certain restrictions/limitation to using the Office Object Model within the pipeline architecture of BizTalk. The "Considerations for server-side Automation of Office" article at https://support.microsoft.com/en-us/kb/257757 talks about those and provide alternates such as OpenXML Formats FOP, etc.

Regards.

• Edited by ShankycheilMicrosoft community contributor Wednesday, July 29, 2015 11:11 AM EDIT

You can write custom pipeline component for this and refer below links.

http://www.codeproject.com/Articles/37286/Excel-Reading-Custom-Pipeline-for-BizTalk-Server

https://mohammedatef.wordpress.com/2009/06/07/biztalk-custom-excel-pipeline-component/

Hi Priya,

You dont need to use any third party tool to convert xls to xml . You can always create your own custom Pipeline component which can do this task for you .

There are multiple Pipeline component description exists over TechNet Wiki from which you can take reference.If you face any problem then let us know 

http://social.technet.microsoft.com/wiki/contents/articles/11679.biztalk-list-of-custom-pipeline-components.aspx

Thanks

Abhishek

Hi Priya,

Please find the below options.

• You can use Microsoft Jet Engine to connect Excel from C# code, it provides many option to convert excel data to xml using DataSet.
• You can use Open XML SDK for Reading, Writing Excel files.
• Another forum thread discussing on similar topic Excel To XML

Please let us know if you need further information the above topics.

Thanks,

SMS Vikas K

Hi Priya,

Please find the below options.

• You can use Microsoft Jet Engine to connect Excel from C# code, it provides many option to convert excel data to xml using DataSet.
• You can use Open XML SDK for Reading, Writing Excel files.
• Another forum thread discussing on similar topic Excel To XML

Please let us know if you need further information the above topics.

Thanks,

SMS Vikas K

• Marked as answer by Priya VS 23 hours 53 minutes ago

Hi Priya,

Please find the below options.

• You can use Microsoft Jet Engine to connect Excel from C# code, it provides many option to convert excel data to xml using DataSet.
• You can use Open XML SDK for Reading, Writing Excel files.
• Another forum thread discussing on similar topic Excel To XML

Please let us know if you need further information the above topics.

Thanks,

SMS Vikas K

• Marked as answer by Priya VS Monday, August 03, 2015 7:12 AM

Hi Priya,

Please find the below options.

• You can use Microsoft Jet Engine to connect Excel from C# code, it provides many option to convert excel data to xml using DataSet.
• You can use Open XML SDK for Reading, Writing Excel files.
• Another forum thread discussing on similar topic Excel To XML

Please let us know if you need further information the above topics.

Thanks,

SMS Vikas K

• Marked as answer by Priya VS Monday, August 03, 2015 7:12 AM

Hi Priya,

Please find the below options.

• You can use Microsoft Jet Engine to connect Excel from C# code, it provides many option to convert excel data to xml using DataSet.
• You can use Open XML SDK for Reading, Writing Excel files.
• Another forum thread discussing on similar topic Excel To XML

Please let us know if you need further information the above topics.

Thanks,

SMS Vikas K

• Marked as answer by Priya VS Monday, August 03, 2015 7:12 AM

Hi Priya,

Please find the below options.

• You can use Microsoft Jet Engine to connect Excel from C# code, it provides many option to convert excel data to xml using DataSet.
• You can use Open XML SDK for Reading, Writing Excel files.
• Another forum thread discussing on similar topic Excel To XML

Please let us know if you need further information the above topics.

Thanks,

SMS Vikas K

• Marked as answer by Priya VS Monday, August 03, 2015 7:12 AM