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

[Former Member]

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

[engswee]

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

[Former Member]

Hi Eng,

Thank you. Similarly when i read a numerical value from excel sheet say 100 the system is converting it to decimals as 100.0   So how to handle the same logic in coding part.?

Regards

Koti Reddy

[engswee]

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