Hi.
I have a CSV file with fields delimited by comma.
Some fields, however are also enclosed by double-quotes, specifically the ones whose text itself has commas, as a way of differentiating commas in the text vs commas that are field delimiters.
So, something like this:
--------------
123,abcd,"abc,d"
456,defg,"def,g"
Three fields, delimited by comma. However, the third field is enclosed by double-quotes and has commas in its text. This comma is not a delimited, but a part of the text itself.
I have having a hard time using ODI to load this file into Oracle.
In Oracle External table it is easy, I would just do:
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
In ODI, in the datastore "Files" tab I don't see anything that specifies "optionally enclosed by".
Also, in the mapping, I am using the "LKM SQL to Oracle (Built-In).Global" module, which does not have the "optionally enclosed by" attribute. Neither does the "LKM File to Oracle (EXTERNAL TABLE)" module.
So, how can I load double-quoted fields without the actual double-quotes in the resulting Oracle column and, more importantly, escape the text-related commas and not treat them as field separators?
Thank you
Boris