1

I need to load from CSV file into an Oracle Table. The problem i m facing is that, the DESCRIPTION field is having Multiple Lines in itself.

Solution i am using for it as ENCLOSURE STRING " (Double Quotes) Using KSH to call for sqlldr.

I am getting following two problems:

  • The row having Description with multiple lines, is not getting loaded as it terminates there itself and values of further fields/columns are not visible for loader. ERROR: second enclosure string not present (Obviously " is not found.)

  • The second line(and lines beyond that) of DESCRIPTION field is being treated as NEW Row in itself and is thus getting populated. It is GARBAGE DATA.

CONTROL File:

OPTIONS(SKIP=1)
LOAD DATA
        BADFILE '/home/fimsctl/datafiles/inbound/core_po/logs/core_po_data.bad'
        DISCARDFILE '/home/fimsctl/datafiles/inbound/core_po/logs/core_po_data.dsc'
        APPEND INTO TABLE FIMS_OWNER.FINANCE_PO_INBOUND_T
        FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
        TRAILING NULLCOLS
        (
          PO_NUM,
          CREATED_DATE "to_Date(:CREATED_DATE,'mm/dd/yyyy hh24:mi:ss')",
          PO_TYPE,
          PO_STATUS,
          NOTREQ1 FILLER,
          NOTREQ2 FILLER,
          PO_VALUE,
          LINE_ITEM_NUMBER,
          QUANTITY,
          LINE_ITEM_DESCRIPTION,
          RATE_VALUE,
          CURRENCY_CODE,
          UOM_ID,
          PO_REQUESTER_WWID,
          QUANTITY_ORDERED,
          QUANTITY_RECEIVED,
          QUANTITY_BILLED terminated by whitespace
         )

CSV File Data:

COL1,8/4/2014 5:52,COL3,COL4,COL5,,,COL8,COL9,"Description Data",COL11,COL12,COL13,COL14,COL15,COL16,COL17

COL1,8/4/2014 8:07,COL3,COL4,COL5,,,COL8,COL9,,"GE MAKE 1X250 WATT HPSV NON INTEGRAL IP-65 **[NEWLINE HERE]**
DIE-CAST ALUMINIUM FIXTURE COMPLETE SET **[NEWLINE HERE]**
WITH SEPRATE CONTROL GEAR BOX WITH CHOKE, **[NEWLINE HERE]**
IGNITOR, CAPACITOR & LAMP-T",COL11,COL12,COL13,COL14,COL15,COL16,COL17

COL1,8/4/2014 8:13,COL3,COL4,COL5,,,COL8,COL9,"Description Data",COL11,COL12,COL13,COL14,COL15,COL16,COL17
Improve this question
9
  • How do you identify that that row is split? ie, at a basic level, how do you know that "IGNITOR" isn't supposed to be COL1 ? Basically you need some check condition to know that you haven't finished yet. When you do, you can use CONTINUEIF option: docs.oracle.com/cd/B19306_01/server.102/b14215/… Commented Feb 4, 2015 at 14:16
  • I'd seen CONTINUEIF, and it's seeming of no help(to me)... The data is coming from remote server and getting stored as CSV file. So can't say on what condition i can check for muliple lies or not... No control over the type of data in Description field. Commented Feb 4, 2015 at 14:19
  • Is there any difference between the line breaks within strings and between records - does one also have a carriage return? Long shot but just a thought... Is there any possibility of getting the file format changed so every field, or at least the last field, is enclosed in double quotes, or to have some dummy marker tacked on the end of each row? Commented Feb 4, 2015 at 14:28
  • Yes perhaps, that can be done(hope to make them convince!).. I's also thinking bout putting a dummy marker or to set different terminator. For now, I can't say, but urge to tell the all possible cases(I might need to give them options which they can do feasibly with the CSV file) Commented Feb 4, 2015 at 14:32
  • 1
    duplicate: unix.stackexchange.com/q/182897/4667 Commented Feb 5, 2015 at 18:51
Related questions
1
data not getting populated from sql loader
0
sql loader loading less fields than columns
1
Issues with loading data through SQL Loader
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.