How To Load Data That Has Commas In Customer Name Using Sqlloader (Doc ID 796098.1)

Last updated on JUNE 23, 2017

Applies to:

Oracle Demantra Advanced Forecasting and Demand Modeling - Version 2.5.9 and later
Information in this document applies to any platform.
***Checked for relevance on 07-Nov-2013***

Goal

How do I set the line OPTIONALLY ENCLOSED BY '"' AND '"'in the FormatFile.fmt so that the
sqlloader loads the data that have a comma in their name? Customer is loading data into the BIIO
tables using the sqlloader. They have noticed that when loading customers which have a comma in
their name they do not load. When they removed the comma from the customer name it worked just fine.


An example of the data:

09-Mar-2009,123456,MYNAME:XYZ,"MYNAME Co.,Inc.:1234:City(1234)-ST-01:CS",0

The company name in the above example has a comma --> MYNAME Co., Inc.:1234:City(1234)-ST-01:CS
Customer is loading using the load_text_file_cs_mip_forecasts.bat. With the comma, the sqlloader
log gives the following error:

Record 1: Rejected - Error on table BIIO_CS_MIP_FORECASTS, column
CS_MIP_FCST.
ORA-01722: invalid number

If this line(OPTIONALLY ENCLOSED BY '"' AND '"') was in the FormatFile.fmt it would work.
However, this file gets overwritten. So customer wants to know if there a way for them to have the
load_text_cs_mip_forecast.bat file not overlay/overwrite the FormatFile.fmt when it runs.
The load_text_file_cs_mip_forecasts.bat is calling load_text.exe The program calls it with
load_text 464. What does 464 stand for? Are there different values that they can pass so the
program does not overwrite FormatFile.fmt which is in their sqlldr control file?



Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms