none
Pipeline component for removing header row from incoming CSV file. RRS feed

  • Question

  • Hello all,

     public IBaseMessage Execute(IPipelineContext pContext, IBaseMessage pInMsg)
            {
    
                string[] currentRow = null;
                List<string> list1 = new List<string>();
                Stream streamList = new MemoryStream();
                BinaryFormatter binFormatter = new BinaryFormatter();
                msgPart = pInMsg.BodyPart;
                StreamReader originalStream = new StreamReader(pInMsg.BodyPart.GetOriginalDataStream());
                //Stream originalStream = pInMsg.BodyPart.GetOriginalDataStream();
    
                try
                {
                    if (msgPart != null)
                    {
                        if (originalStream != null)
                        {
                            while (originalStream.ReadToEnd() != null)
                            {
                                string S = originalStream.ReadLine();
                                list1.Add(S);
                            }
                            list1.RemoveAt(0);
                            //var binFormatter = new BinaryFormatter();
                            //streamList = new MemoryStream();
                            binFormatter.Serialize(streamList, list1);
    
                        }
                    }
                }
                catch (Exception)
                {
                    throw;
                }
                //originalStream.Seek(0, SeekOrigin.Begin);
                //msgPart.Data = originalStream;
    
                IBaseMessage outMsg = pInMsg;
                outMsg.BodyPart.Data = streamList;
    
                return outMsg;
            }

    Its not working can somebody tell me any other way so that I can achieve this

    Thanks in advance.


    • Edited by _NiLeSh Thursday, November 24, 2016 10:36 AM
    Thursday, November 24, 2016 5:47 AM

Answers

  • Hi,

    Removing a header from a csv file could be done using the flat-file wizard in BizTalk, see for example this recent post. Or in a mapping you can ignore it. I would not opt for an custom pipeline as you will introduce an extra dependency in your solution.

    Kind Regards,

    Steef-Jan Wiggers

    Microsoft Azure MVP


    BizTalk

    Thursday, November 24, 2016 6:36 AM
    Moderator
  • Sorry, but we needed to do it that way.

    My colleague need header in processing and further that we need to have file without header in it,

    It is really useless to introduce a custom component to achieve a functionality which is provided out-of-box in BizTalk.

    The easiest is to just make a record or element in your schema be the 'Header' element, and ignore it in your mapping. This is probably the most straightforward approach, even if it's annoying to have a record/field element/attribute in there that you don't use.  In the flat file schema wizard; define your first record as a singular record and then your other records as repeating records. then in whatever map or transformations you're working with you can just ignore the header record.

    Another way is that you can set the HeaderSpecName property in the 'Configure Pipeline' dialog to the header schema you have just created and then the PreserveHeader property to false  on the flat file disassembler. his is really just a more involved way of doing the same as above, but the schema you work with won't show the header data while you're mapping.

    Please we insist, don't over complicate this.


    Rachit Sikroria (Microsoft Azure MVP)

    Friday, November 25, 2016 4:28 AM
    Moderator
  • Is this the same situation as described in this thread: https://social.msdn.microsoft.com/Forums/en-US/a988d9e9-e18d-4181-a2c7-9b254022e71d/ignoring-header-while-creating-output-xml-flatfile-schema?forum=biztalkgeneral#79aaa490-c4b9-4322-9871-f8dd685ddc11

    Either way, our advice is the same.  You should not be using a Pipeline Component for this.

    The correct way to handle this is by using the Header Spec option of the Flat File Disassembler.

    The header text is still available as a Context Property, but in practice, this should not be needed since CSV headers should be static values.  If not, then you have a completely different situation.


    Friday, November 25, 2016 9:38 PM
    Moderator

All replies

  • Hi,

    Removing a header from a csv file could be done using the flat-file wizard in BizTalk, see for example this recent post. Or in a mapping you can ignore it. I would not opt for an custom pipeline as you will introduce an extra dependency in your solution.

    Kind Regards,

    Steef-Jan Wiggers

    Microsoft Azure MVP


    BizTalk

    Thursday, November 24, 2016 6:36 AM
    Moderator
  • HI Nilesh,

    First thing it depend how you are creating your schema .

    there are two ways you can create flat file schema for a file which have header .

    1) use the same no of colomns and delimiter for header details .

    2) simple  create a node with string data type for the header and for the records create nodes as per no of colomn in records.

    Now in the map :

    1) if your schema is according to first pattern then you need to follow below rule

    You can easily do this in MAP , you just need to skip first record when you will do mapping . For this you can use iterate functiod to get the current count and if current record no is =1 then , map the false to destination record node and for all set the true .

    2) If your schema is according to second approach then only map from your record data to destination nodes.

    Regards

    Abhay Giri

    Mark this as answered or vote , if this help you.

    Thursday, November 24, 2016 7:37 AM
  • Hi Nilesh,

    Definitely not the best and recommended approach to achieve what you are intending for.

    You should so this in the solution if you want to ignore the header. When you create ur flat file schema you define header in there and then when you are processing/ mapping you just ignore the header for your consumers.

    What is the rationale behind using pipeline ? Why do you want to have it that way ? Any specific reason or issue you are facing ?


    Pi_xel_xar

    Blog: My Blog

    BizTalkApplicationDeploymentTool: BizTalk Application Deployment Tool/

    Thursday, November 24, 2016 7:39 AM
    Answerer
  • Sorry, but we needed to do it that way.

    My colleague need header in processing and further that we need to have file without header in it,

    so I am just trying that.

    Below is format of CSV

    Client ID, Bank Name/Number, Date of Order, Shipment ID
    101,4526,22112016,123
    102,4526,22112016,123
    103,4526,22112016,123

    can  anybody help me please also can we able to debug the class library used in Pipeline component

    Thanks.



    • Edited by _NiLeSh Thursday, November 24, 2016 11:19 AM
    Thursday, November 24, 2016 11:09 AM
  • Hi Nilesh,

    Then you can use only a map. same schema need to use in both direction , you just need to put one logic :

    use one non -equal functiod and give input from Iteration functoid and compare it to 1 , and map the output of your logical functoid to record node (which may be repeating here) . Your header values will skip here.

    Regards

    Abhay Giri

    Thursday, November 24, 2016 12:37 PM
  • Sorry, but we needed to do it that way.

    My colleague need header in processing and further that we need to have file without header in it,

    so I am just trying that.

    Below is format of CSV

    Client ID, Bank Name/Number, Date of Order, Shipment ID
    101,4526,22112016,123
    102,4526,22112016,123
    103,4526,22112016,123

    can  anybody help me please also can we able to debug the class library used in Pipeline component

    Thanks.



    U can have the file with header and when further down the system you need to remove it you can just use a map and not map the header to the destination message. Consider header as another record section of ur csv but under a different recordnode in schema. There is nothing special about a header in csv.

    http://anshukumarmishra.blogspot.nl/2015/05/remove-header-from-csv-file-in-biztalk.html

    Doing it the way you are intending is not the right way.


    Pi_xel_xar

    Blog: My Blog

    BizTalkApplicationDeploymentTool: BizTalk Application Deployment Tool/

    Thursday, November 24, 2016 11:55 PM
    Answerer
  • Sorry, but we needed to do it that way.

    My colleague need header in processing and further that we need to have file without header in it,

    It is really useless to introduce a custom component to achieve a functionality which is provided out-of-box in BizTalk.

    The easiest is to just make a record or element in your schema be the 'Header' element, and ignore it in your mapping. This is probably the most straightforward approach, even if it's annoying to have a record/field element/attribute in there that you don't use.  In the flat file schema wizard; define your first record as a singular record and then your other records as repeating records. then in whatever map or transformations you're working with you can just ignore the header record.

    Another way is that you can set the HeaderSpecName property in the 'Configure Pipeline' dialog to the header schema you have just created and then the PreserveHeader property to false  on the flat file disassembler. his is really just a more involved way of doing the same as above, but the schema you work with won't show the header data while you're mapping.

    Please we insist, don't over complicate this.


    Rachit Sikroria (Microsoft Azure MVP)

    Friday, November 25, 2016 4:28 AM
    Moderator
  • Is this the same situation as described in this thread: https://social.msdn.microsoft.com/Forums/en-US/a988d9e9-e18d-4181-a2c7-9b254022e71d/ignoring-header-while-creating-output-xml-flatfile-schema?forum=biztalkgeneral#79aaa490-c4b9-4322-9871-f8dd685ddc11

    Either way, our advice is the same.  You should not be using a Pipeline Component for this.

    The correct way to handle this is by using the Header Spec option of the Flat File Disassembler.

    The header text is still available as a Context Property, but in practice, this should not be needed since CSV headers should be static values.  If not, then you have a completely different situation.


    Friday, November 25, 2016 9:38 PM
    Moderator
  • The statement:

    while (originalStream.ReadToEnd() != null

    reads the fullstring and puts the streamposition at the end of the stream, if you replace that by:

    while (!originalStream.EndOfStream)

    you get a better result.



    Tuesday, November 29, 2016 4:15 PM