Skip to Content
avatar image
Former Member

How to skip specific lines in a .csv file during FCC in sender file Channel?

Hi All,

I have a requirement where a .csv file sent to PI would be in format as shown below. The source structure required for mapping is also as shown here. Please tell me is it possible to achieve this using FCC alone? or Should I go for an intermediate mapping ?

Sample File -

World Data

Country Data

Date - 25/7/2017

<Blank line>

Name,Country,Continent

ABC,India,Asia

DEF,Iceland,Europe

XYZ,Uganda,Africa

Source Structure -

Employee Record

Header

Key = World Data

Code = Country Data

Date = Date - 25/7/2017

Details

Name = ABC

Country = India

Continent = Europe

Here Details node will occur multiple times.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

4 Answers

  • Best Answer
    Oct 25, 2017 at 09:16 AM

    Hello Poorinma,

    I think this is not possible using FCC then ,If your file structure is constant you can try the below approach.

    Create intermediate Mapping like below(this will be the first mapping followed by your actual mapping).Keep the field separator as 'nl' so that all values will come in field node.

    For Header Map it with constant:


    For Header Fields use the below UDF(all Values of Queue):

    result.addValue(var1[0]);
    result1.addValue(var1[1]);
    result2.addValue(var1[2]);

    For Details use the below UDF(All Values of queue):

    int j=0;
    for(int i=0;i<var1.length;i++)
    {
    if(var1[i].equals("Name,Country,Continent"))
    {
    j=i+1;
    }
    }
    if(j>0);
    for(int k=0;k<=var1.length-j;k++)
    {
    result.addValue(var1[j]);
    j++;
    }

    For Details Fields Use the below UDF(all Values of context) followed by the previous UDF(details one):

    String Out[]=var1[0].split(",");
    result1.addValue(Out[0]);
    result2.addValue(Out[1]);
    result3.addValue(Out[2]);

    Sample Input and Output:

    Add comment
    10|10000 characters needed characters exceeded

  • Oct 24, 2017 at 03:24 PM

    Hello Poornima,

    There are no keys for header and Details?

    Add comment
    10|10000 characters needed characters exceeded

  • Oct 25, 2017 at 08:52 AM

    Hi Poornima,

    Please use a general format for datatype.

    Recordset ---1...1

    line ---0 to unbounded

    Then skip or accept record at mapping level . In mapping level you can create necessary target structure.

    In case header comes only once you can skip the same in FCC using the parameter

    Under Document Offset: specify the number of lines that are to be ignored at the beginning of the document.

    This enables you to skip comment lines or column names during processing. If you do not make an entry, the default value is zero lines.

    Regards

    Anupam

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi Anupam,

      Thank you for your inputs. But Header data must be sent to target as per the requirement. So, document offset cannot be used here. But yes, as you have suggested, I've created an intermediate source structure same as you've mentioned, to read the file. I'll write required UDFs in mapping to bifurcate the files.

      Thank you.

  • avatar image
    Former Member
    Oct 25, 2017 at 09:37 AM

    Can the process that is pushing the file to PI execute OS level post-processing? if it can, this could quite easily be fixed with awk:

    awk -v pre1="Key," -v pre2="Code," -v pre3="Date," -v pre4="Line," '
    /Name,Country,Continent/ {next} ;
    NR==1 && NF>0 {print pre1 $1} ;
    NR==2 && NF>0 {print pre2 $1} ;
    NR==3 && NF>0 {print pre3 $1} ;
    NR>3  && NF>0 {print pre4 $1} ; ' yourfile.txt > temp && mv temp yourfile.txt
    
    

    This will prefix the header fields with Key, Code and Date and each line record with Line. It will also throw out the empty line and the line containing the field names.

    Regards,

    Patrick

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi Patrick,

      I agree with you. This option could have solved the complication in design issue. But unfortunately, this cannot be implemented at OS level before delivering it to PI, as per the business requirement.

      Anyway, thank you for sharing this approach. It might help in future requirements.