Discussions
Categories
- 17.9K All Categories
- 3.4K Industry Applications
- 3.3K Intelligent Advisor
- 63 Insurance
- 536.4K On-Premises Infrastructure
- 138.3K Analytics Software
- 38.6K Application Development Software
- 5.8K Cloud Platform
- 109.5K Database Software
- 17.5K Enterprise Manager
- 8.8K Hardware
- 71.1K Infrastructure Software
- 105.3K Integration
- 41.6K Security Software
ODI cannot load CSV with fields enclosed by double-quotes, delimited by comma
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
Best Answer
-
when you define the file (on the designer, models), on the files tab:
In the field separator section:
put other ,
In the Text Delimiter: put " (the double quote).
Try this and let me know if this work for you.
Answers
-
The way I understand it, the Load Knowledge Module can be tweaked to treat fields optionally enclosed by '"'.
The LKM File to Oracle (EXTERNAL TABLE) module has the "Create External Table" task. In it, I do see the following code:
FIELDS TERMINATED BY x'<%=odiRef.getSrcTablesList("", "[XFILE_SEP_FIELD]", "", "")%>'
<% if(odiRef.getSrcTablesList("", "[FILE_ENC_FIELD]", "", "").equals("")){%>
<%} else {%>OPTIONALLY ENCLOSED BY '<%=odiRef.getSrcTablesList("", "[FILE_ENC_FIELD]", "", "").substring(0,1)%>' AND '<%=odiRef.getSrcTablesList("", "[FILE_ENC_FIELD]", "", "").substring(1,2)%>' <%}%>
<%=odiRef.getOption("EXT_MISSING_FIELD")%>I am not sure how this code can be tweaked to get this type of behavior:
OPTIONALLY ENCLOSED BY '"'Also, the LKM SQL to Oracle module (the built-in version of which I am using), has a bunch of tasks, but I am not even sure which task would be responsible for controlling this behavior and what the code might look like.
We are mostly using the "LKM SQL to Oracle" built-in module to load CSV files into our Oracle tables, so finding a way to do it with this module would be actually more preferable than with the "LKM File to Oracle (EXTERNAL TABLE)" module.Thanks
Boris
-
when you define the file (on the designer, models), on the files tab:
In the field separator section:
put other ,
In the Text Delimiter: put " (the double quote).
Try this and let me know if this work for you.
-
Thanks JimmyOTNC.
This is working now.
I already had the comma (,) in the Field Separator field.
I just added the double quote (") in the Text Delimiter field.
In essence ODI's Text Delimiter is synonymous with "OPTIONALLY ENCLOSED BY" attribute. It wasn't intuitive enough for me.
At some point, I would love to learn how to manage/control things like that with Knowledge Modules, as I think that would have been another approach, if more complicated. Looks like that FILE_ENC_FIELD could have been defined as a flexfield, but that's where my digging stopped before I used your recommendation and it worked.
Thanks!
-
Glad it worked for you. Yes, the doc is not intuitive at all.