Skip to Content
avatar image
Former Member

Dealing with empty spaces in field name in Header Column of Excel sheet

Dear all,

I am developed a custom adapter module to convert .xls file to xml file and its working fine as shown below.

Input File 1

Outout file 1

But my requirement is... In the .xls input file, in header fields they are sepaated by a space like "Job Req Id". In PI we dont have a provision to use spaces in name of field tags. So i have taken thedata type field name as JobReqId. Now when i test the scenario the result is as follows:

Actual Input

Actual error output

So please let me know how to deal with the space in header column field of excel (.xls) file

Regards

Koti Reddy

in1.JPG (33.1 kB)
out1.JPG (27.5 kB)
in2.JPG (33.4 kB)
out2.JPG (37.1 kB)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Oct 07, 2014 at 08:53 AM

    Hi Koti

    In your adapter module, when you are processing the first line of the Excel file to get the column header names, you can remove the spaces from the string.

    You can achieve this using the replaceAll method of the String class, supplying the proper regex. Example below:-

    String columnName = <your logic>;
    columnName = columnName.replaceAll("\\s+","");
    

    More details in the link below.

    Removing whitespace from strings in Java - Stack Overflow

    Rgds

    Eng Swee

    Add comment
    10|10000 characters needed characters exceeded

    • Hi Koti

      I think the conversion to values with decimals is related to the behavior of the Excel API you are using - not sure if you are using JExcel or Apache POI.

      Anyway, there are two ways you can handle this:-

      Option 1

      Handle this in message mapping using standard function FormatNum (in category Arithmetic). You can set the pattern (refering to DecimalFormat) for the FormatNum function as #########0 (total length of pattern should be equals to maximum length of the field, in my example length = 10)

      Option 2

      Handle this in your custom module with the following Java logic. Note that variables fieldName and fieldValue should be retrieve via the Excel API prior to this logic.

           if (fieldName.equals("JobReqID")) {
            DecimalFormat form = (DecimalFormat) NumberFormat.getInstance();
            form.applyPattern("#########0");
            fieldValue = form.format(Double.parseDouble(fieldValue));
           }
      

      I would suggest you go for Option 1, as it is much simpler to implement and test.

      Rgds

      Eng Swee