Hi All,
I would like to know the format file equivalent of SQL Server in Oracle. I see there is something called Control File in Oracle to load data from a text file to my Oracle database table. However, I would like to load only few columns from the text file to my database.
The delimiter in my text file for each column would be either '|' or ',' and one or two columns of the text file I'm trying to load may contain other characters like ' " ' (double quotes), ':' (colon) etc.
Could someone please help me with this?
Here is the DDL for my table and the load control table I've prepared for the following data:
CREATE TABLE test.flat_file_table
(
Col1 DATE,
Col2 VARCHAR2(50 BYTE),
Col3 CHAR(9 BYTE),
Col4 VARCHAR2(50 BYTE),
Col5 VARCHAR2(50 BYTE),
Col6 VARCHAR2(75 BYTE),
Col7 VARCHAR2(75 BYTE),
Col8 CHAR(1 BYTE),
Col9 FLOAT(63),
Col10 CHAR(1 BYTE),
Col11 VARCHAR2(50 BYTE),
Col12 NUMBER(38),
Col13 NUMBER(38),
Col14 NUMBER(38),
Col15 VARCHAR2(50 BYTE),
Col16 NUMBER(38),
Col17 FLOAT(63),
Col18 VARCHAR2(100 BYTE),
Col19 VARCHAR2(1000 BYTE),
Col20 VARCHAR2(50 BYTE)
);
2012-07-23 08:03:09.920|A8uT9aOLy1kdyoutlst|u=ii ddls=dd HILM MDION 2 MIOGO, souhd, dpdpud|null|"test_sql_file","test_user_file","test_more_files","user_testing"|10000000|JOHB|MY 3 HIS|1.1.1.1|/test/|Customer|xyz.org.user.cascade|null|J|1234
LOAD DATA
TRUNCATE INTO test.flat_file_table T1
FIELDS TERMINATED BY '|'
( field1,
field2,
field3 FILLER,
field4 FILLER,
field5 FILLER,
field6,
field7,
field8,
field9 FILLER,
field10,
field11,
field12,
field13,
field14,
field15
)
Thank you,
Julaayi
Added DDL, sample data