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.

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 ShankycheilMicrosoft community contributor 15 hours 33 minutes ago edit

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 ShankycheilMicrosoft community contributor Wednesday, July 29, 2015 3:30 PM edit

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 ShankycheilMicrosoft community contributor Wednesday, July 29, 2015 3:30 PM edit

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 ShankycheilMicrosoft community contributor Wednesday, July 29, 2015 3:30 PM edit

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 ShankycheilMicrosoft community contributor Wednesday, July 29, 2015 3:30 PM edit

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

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

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,

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

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);
            }
        }
    }
}

Thanks,

SMS Vikas K

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

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

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);
            }
        }
    }
}

Thanks,

SMS Vikas K

• Proposed as answer by Priya VS 21 hours 53 minutes ago

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);
            }
        }
    }
}

Thanks,

SMS Vikas K

• Proposed as answer by Priya VS Friday, August 07, 2015 9:12 AM

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);
            }
        }
    }
}

Thanks,

SMS Vikas K

• Proposed as answer by Priya VS Friday, August 07, 2015 9:12 AM

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

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 21 hours 53 minutes ago

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

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 07, 2015 9:12 AM
• Marked as answer by Angie xuMicrosoft contingent staff, Moderator 4 hours 33 minutes ago

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

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 07, 2015 9:12 AM
• Marked as answer by Angie xuMicrosoft contingent staff, Moderator Monday, August 10, 2015 2:29 AM