Wednesday, May 13, 2015

Enclosing double quotes or apostrophes in your data? Don't worry, be happy!

Hi folks,

Is 11.1.2.4 your first contact with FDMEE? or are you coming from 11.1.2.3.x?
Why I'm asking that? because what we are going to discuss about today is something new in 11.1.2.3.530 and 11.1.2.4.

One year ago I posted about issues when your source data file had either enclosing double quotes or apostrophe.
First scenario typically happens when text/csv files are generated from Excel or other systems which configured to use "" as text delimiter.
Apostrophes may be used within account descriptions or any other description field extracted from your source system.

We can summarize my post and tell you that:
  • Double quotes were not removed when importing data
  • Apostrophes often made your import fail as single quotes (or apostrophes) are also used as string delimited in Jython
Oracle even published some documents for these issues:
  • FDMEE Script Samples (Doc ID 1613836.1)
  • FDMEE: Unable To Import File With Apostrophe In Account Description (Doc ID 1629889.1)
And now what? well from 11.1.2.3.530 these two issues are handled by FDMEE (PSU530 and PS4 have mostly same code) so no need to worry about any scripting to parse source fields anymore.
Let's see how it works in 11.1.2.4.

Importing a file with double quotes as text delimiter
Let's say we have the following file generated from an Excel file:
We have created a simple import format and data load rule having assigned the import format:
In order to make this example easier we have pre-selected the source file in DLR. In that way we don't have to select it when importing data:
As you can see we have assigned the import format to the DLR. This is not needed if you have only one import format for your location (file-base loads).
Time to import data. We can either execute the DLR from Data Load Workbench or from DLR page (even from a batch...). Results will be the same:
Data has been imported with no double quotes as text delimiter...and no need of import scripts...good!

Importing a file with apostrophe in description
What about this one?
We have defined a DLR using the same import format so no import scripts being used:
The source file has been also pre-selected. Let's import it!
VoilĂ ! data being imported without issues...and descriptions as well. 

BTW, If you were wondering, any of the source files have been modified, so FDMEE keeps your original file as it is :-)

This enhancement is a really good one we don't need additional import scripts to parse our data.

Finally, if you are upgrading your FDMEE from 11.1.2.3.x to PSU530 or PS4, don't forget to remove your scripts for that purpose!

Take care!