How can we import values with semi colon from the CSV data by impex

[Former Member]

We're trying to import CSV data via hot folder mapping, but when the data contains semi colon, it's generating the incorrect impex for few records, so it's failing due to mismatch happens in the mapping as semi colon is considered as column separator as per impex systax.

Example: below CSV record is mapped to 4 columns as per impex header, 3rd column data contains semi colon, so generated impex considering as 2 different value entries ("MR" and "Hybris"), but should be considered as simple value entry.

"123"|"ABC"|"MR; Hybris"|"experts.hybris.com"|

Any solutions / suggestions please?

[former_member636435]

Hello .

First of all check in your hot-folder-spring.xml whether the fieldSeparator of ImpexTransformerTask is set to different value than semicolon. Here’s the code fragment I’m talking about:

 ...
 <bean id="batchTransformerTask" 
           class="de.hybris.platform.acceleratorservices.dataimport.batch.task.ImpexTransformerTask" 
           init-method="initConvertersMap">
         <property name="fieldSeparator" value="," />
         <property name="encoding" value="UTF-8" />
         <property name="linesToSkip" value="0"/>
         <property name="cleanupHelper" ref="cleanupHelper" />
     </bean>
 ...

But that is not enough, due to the fact that the OOTB converter de.hybris.platform.acceleratorservices.dataimport.batch.converter.impl.DefaultImpexConverter in it’s escapeQuotes method is splitting string with semicolon as a token. Because of that, if there is a semicolon in the data, the whole impex is distorted.

There are 2 ways of dealing with that:

1. Insert the enclosing character extra double quote again for both csv and impex:

   “”MR; Hybris””

2. The other way is changing the DefaultImpexConverter, so that it splits strings different way. Here’s how to do that:

   Copy the existing DefaultImpexConverter, rename it (eg. TestingImpexConverter) and paste it somewhere.

Check the override method called convert(...)
In the near middle, there is a code snippet as below:

 String colValue = row.get(mapIdx);
 // mapIdx is the column number from your csv file

When doing debug, you will find that the colValue is the Impex version of your data but without the enclosing characters you need. If you want to use enclosing character without changing the csv file, you better do some work around and put the snippet code like below for testing purpose.

 // Do this only for your data in specific column. (The third column for this example)
 if (mapIdx.intValue() == 2){
   // For testing purpose 
   // Put the double quote manually for enclosing character purpose
   colValue = "\"" + colValue + "\"";
 }

Update your mapping:

 <bean id="testingConverter" class="your.path.to.converter.impl.TestingImpexConverter">
       <property name="header">
       ...

Whichever method you are using from above, the last step would be overriding the escapeQuotes method to make it understand that we are using double quote as enclosing character:

 @Override
 protected String escapeQuotes(final String input)
 {
     // final String[] splitedInput = StringUtils.splitPreserveAllTokens(input, SEMICOLON_CHAR);
     // Using StrTokenizer from apache common package
     // Using semicolon as delimiter and double quote as enclosing quote for your data
     final String[] splitedInput = new StrTokenizer(input, ';', '"').getTokenArray();
 
     final List<String> tmp = new ArrayList<String>();
     for (final String string : splitedInput)
     {
         if (doesNotContainNewLine(string))
         {
             tmp.add(StringEscapeUtils.escapeCsv(string));
         }
         else
         {
             tmp.add(string);
         }
     }
     return StringUtils.join(tmp, SEMICOLON_CHAR);
 }

Hope that helps,