none
Reading Excel File in BizTalk RRS feed

  • Question

  • Hi All,

    I have to read an excel file and map the data and send to SAP.

    I thought of converting excel file to flat file and then handle the same in biztalk 2013 using flat file schema.

    Below I am pasting 3 screenshots.

    1. Sample of excel file.

    2. Code to read the excel and generate a flat file.

    3. Output in console format.

    My question here is, when I am giving an alphanumeric value in any of the cell, the data is coming as an incremented number.

    Please refer to the screenshot and kindly help .  Or if there is any other way to find a solution of the above requirement.


    Thanks, Varun

    Wednesday, July 29, 2015 2:47 PM

Answers

  • Hi Varun,

    Have debugged your code and fixed to show the text character that you were facing.

    • Observe the inner for loop for converting cell values to the text format (SharedStringTable).
    • Link1
    • Link2

    UsingOpenXML

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Data.OleDb;
    using System.Data;
    using DocumentFormat.OpenXml.Packaging;
    using DocumentFormat.OpenXml.Spreadsheet;
    
    namespace ConsoleApplicationExcel
    {
        class ExcelToXMLClass
        {
            static void Main(string[] args)
            {
                // Read Excel using Open XML SDK
                ExcelOpenXML(@"ExcelInput.xlsx");
                Console.ReadKey();
            }
            static void ExcelOpenXML(string fileName)
            {
    
                using (SpreadsheetDocument document = SpreadsheetDocument.Open(fileName, false))
                {
                    SharedStringTable sharedStringTable = document.WorkbookPart.SharedStringTablePart.SharedStringTable;
                    string cellValue = null;
    
                    foreach (WorksheetPart worksheetPart in document.WorkbookPart.WorksheetParts)
                    {
                        foreach (SheetData sheetData in worksheetPart.Worksheet.Elements<SheetData>())
                        {
                            foreach (Row row in sheetData.Elements<Row>())
                            {
                                foreach (Cell cell in row.Elements<Cell>())
                                {
                                    cellValue = cell.InnerText;
                                    if (cell.DataType == null)
                                    {
                                        Console.Write(cellValue + "\t");
                                    }
                                    else
                                    {
                                        Console.Write(sharedStringTable.ElementAt(Int32.Parse(cellValue)).InnerText + "\t");
                                    }
                                }
                                Console.WriteLine();
                            }
                        }
                    }
                }
            }
        }
    }
    


    Thanks, SMS Vikas K

    • Proposed as answer by Priya VS Friday, August 7, 2015 9:12 AM
    • Marked as answer by Angie Xu Monday, August 10, 2015 2:29 AM
    Monday, August 3, 2015 3:27 AM
    Answerer

All replies

  • Refer to https://msdn.microsoft.com/en-us/library/aa577936.aspx for "How to Use BizTalk Flat File Schema Wizard" and https://msdn.microsoft.com/en-us/library/aa559306.aspx for "Creating Schemas Using BizTalk Flat File Schema Wizard" to create a schema and then map it to an IDOC format to pass it into SAP.

    Regards.

    • Edited by Shankycheil Wednesday, July 29, 2015 3:30 PM edit
    Wednesday, July 29, 2015 3:30 PM
  • Hi Varun,

    Refer below custom pipeline.

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

    For insert into SAP you have to generate schema from SAP add generated items and select add adapter metadata.

    After that map what u want to insert into SAP.

    Refer below links.

    https://msdn.microsoft.com/en-us/library/dd788567.aspx

    http://kentweare.blogspot.in/2010/01/biztalk-adapter-pack-20-sap-adapter.html 


    Kind Regards, Anurag Prajesh (BizTalk Developer)


    Thursday, July 30, 2015 3:33 AM
  • Hi Anurag,

    I have checked the below link, but I am not able to fetch the data of excel. It is returning the empty data and saving the same excel to c:\temp folder.  I tried to debug the pipeline to see, but not sure how to proceed. 

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

    Can you help me with the same ?

    Thanks,

    Varun


    Thanks, Varun

    Thursday, July 30, 2015 8:43 AM
  • Hi Varun,

    Please create custom pipeline to convert excel data to flat file and then handle your scenario using flat file schema.

    Please refer below C# code to convert your Excel data to flat file.

    using System;
    using System.IO;
    using System.Data;
    using System.Data.OleDb;
    using System.Collections.Generic;
    using System.Text;
    
    namespace XlsToCsv
    {
        class Program
        {
            
            static void Main(string[] args)
            {
                string sourceFile, worksheetName, targetFile;
                
    
                // TO convert Excel file to CSV formate
                //sourceFile = "aabbcc.xls"; worksheetName = "aabbcc"; targetFile = "abc.csv";
    
                // TO convert Excel file to txt formate
                sourceFile = "aabbcc.xls"; worksheetName = "aabbcc"; targetFile = "abc.txt";
    
                convertExcelToCSV(sourceFile, worksheetName, targetFile);
            }
    
    
            static void convertExcelToCSV(string sourceFile, string worksheetName, string targetFile)
            {
                string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + sourceFile +
                ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";
    
                OleDbConnection conn = null;
                StreamWriter wrtr = null;
                OleDbCommand cmd = null;
                OleDbDataAdapter da = null;
    
                try
                {
                    conn = new OleDbConnection(strConn);
                    conn.Open();
    
                    cmd = new OleDbCommand("SELECT * FROM [" + worksheetName + "$]", conn);
                    cmd.CommandType = CommandType.Text;
                    wrtr = new StreamWriter(targetFile);
    
                    da = new OleDbDataAdapter(cmd);
                    DataTable dt = new DataTable();
                    da.Fill(dt);
    
                    for (int x = 0; x < dt.Rows.Count; x++)
                    {
                        string rowString = "";
                        for (int y = 0; y < dt.Columns.Count; y++)
                        {
                            rowString += "\"" + dt.Rows[x][y].ToString() + "\",";
                        }
                        wrtr.WriteLine(rowString);
                    }
                    Console.WriteLine();
                    Console.WriteLine("Done! Your " + sourceFile + " has been converted into " + targetFile+".");
                    Console.WriteLine();
                }
                catch (Exception exc)
                {
                    Console.WriteLine(exc.ToString());
                    Console.ReadLine();
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                        conn.Close();
                    conn.Dispose();
                    cmd.Dispose();
                    da.Dispose();
                    wrtr.Close();
                    wrtr.Dispose();
                }
            }
        }
    }
    

    Thanks,


    If my reply is helpful please mark as answer or vote as helpful.

    Thursday, July 30, 2015 3:57 PM
    Moderator
  • Hi Varun ,

    Why you need to convert excel to flatfile and then Flat file to xml ?

    What you can try is to convert your xls file directly to xml and then you can map your Idoc schema with the xml file generated  from xml . You may need to loop through the records inside your Orchestration and map each row with sap Idoc schema .

    you can refer list of pipeline component available for this over technet wiki

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

    Thanks

    Abhishek

    Friday, July 31, 2015 2:37 PM
  • Hi Varun,

    Please have a look at the below code, it uses the Microsoft Jet Engine to connect Excel from C# code, Query the required data in Dataset, then convert to xml using GetXml method.

    • You can convert the Excel to XML, using Dataset.
    • It provides an option to include SQL queries Select, Insert, Update and Delete.
    • You can use this in pipeline component and customize the input parameters, xml structure.
    • Then disassemble the file, Map to IDOC schema then send to SAP.

    Console Application just to illustrate and corresponding xml file for the scenario requested.

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Data.OleDb;
    using System.Data;
    
    namespace ConsoleApplicationExcel
    {
        class ExcelToXMLClass
        {
            static void Main(string[] args)
            {
                ExcelToXML(@"ExcelInput.xlsx");
                Console.WriteLine("Xml File is saved Successfully");
                Console.ReadKey();
            }
            public static void ExcelToXML(string file)
            {
                try
                {
                    OleDbConnection con = null;
                    DataSet ds;
                    OleDbDataAdapter cmd;
                    con = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + file + ";Extended Properties='Excel 12.0;HDR=No;'");
                    con.Open();
                    // You have option to query to the Dataset, (Similar to SQL)
                    cmd = new OleDbDataAdapter("select * from [Sheet1$]", con);
                    cmd.TableMappings.Add("Table", "Rows");
                    ds = new DataSet("Root");
                    cmd.Fill(ds);
                    
                    // GetXml To String Type
                    string stringXml = ds.GetXml();
    
                    // WriteXml -  Xml To File Location
                    ds.WriteXml("ExcelToXML.xml");
                    con.Close();
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                }
            }
        }
    }


    ExcelToXmlInputOutput

    Thanks,

    SMS Vikas K

    • Proposed as answer by Priya VS Friday, August 7, 2015 9:12 AM
    Monday, August 3, 2015 1:16 AM
    Answerer
  • Hi Varun,

    Have debugged your code and fixed to show the text character that you were facing.

    • Observe the inner for loop for converting cell values to the text format (SharedStringTable).
    • Link1
    • Link2

    UsingOpenXML

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Data.OleDb;
    using System.Data;
    using DocumentFormat.OpenXml.Packaging;
    using DocumentFormat.OpenXml.Spreadsheet;
    
    namespace ConsoleApplicationExcel
    {
        class ExcelToXMLClass
        {
            static void Main(string[] args)
            {
                // Read Excel using Open XML SDK
                ExcelOpenXML(@"ExcelInput.xlsx");
                Console.ReadKey();
            }
            static void ExcelOpenXML(string fileName)
            {
    
                using (SpreadsheetDocument document = SpreadsheetDocument.Open(fileName, false))
                {
                    SharedStringTable sharedStringTable = document.WorkbookPart.SharedStringTablePart.SharedStringTable;
                    string cellValue = null;
    
                    foreach (WorksheetPart worksheetPart in document.WorkbookPart.WorksheetParts)
                    {
                        foreach (SheetData sheetData in worksheetPart.Worksheet.Elements<SheetData>())
                        {
                            foreach (Row row in sheetData.Elements<Row>())
                            {
                                foreach (Cell cell in row.Elements<Cell>())
                                {
                                    cellValue = cell.InnerText;
                                    if (cell.DataType == null)
                                    {
                                        Console.Write(cellValue + "\t");
                                    }
                                    else
                                    {
                                        Console.Write(sharedStringTable.ElementAt(Int32.Parse(cellValue)).InnerText + "\t");
                                    }
                                }
                                Console.WriteLine();
                            }
                        }
                    }
                }
            }
        }
    }
    


    Thanks, SMS Vikas K

    • Proposed as answer by Priya VS Friday, August 7, 2015 9:12 AM
    • Marked as answer by Angie Xu Monday, August 10, 2015 2:29 AM
    Monday, August 3, 2015 3:27 AM
    Answerer