Home » SQL & PL/SQL » SQL & PL/SQL » External Table for CSV delimited file skipping columns (Oracle 11g)
| External Table for CSV delimited file skipping columns [message #575611] |
Thu, 24 January 2013 13:50  |
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
|
Senior Member |
|
|
Hi All,
I need to load csv file using an external table.
Structure of External Table:
---------------------------
create table A (col1 varchar2(30), col3 varchar2(30), col5 varchar2(30));
CSV FILE:
-----------
col1,col2,col3,col4,col5
A,B,C,D,E
1,2,3,4,5
The table data should look like
COL1 COL3 COL5
A C E
1 3 5
need to skip the columns in CSV file.
Thanks in advance
|
|
|
|
|
|
|
|
|
|
|
|
| Re: External Table for CSV delimited file skipping columns [message #575713 is a reply to message #575611] |
Fri, 25 January 2013 14:20  |
 |
Barbara Boehmer
Messages: 7668
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
To skip columns, list all of the columns in the csv file in the lower portion of the create external table statement, but list only the columns that you want in the upper portion of the create external table statement, as demonstrated below.
-- contents of file c:\my_oracle_files\test.csv on server:
col1,col2,col3,col4,col5
A,B,C,D,E
1,2,3,4,5
-- create oracle directory object that maps to directory that csv file is in:
SCOTT@orcl_11gR2> create or replace directory ext_dir as 'c:\my_oracle_files'
2 /
Directory created.
-- create external table:
SCOTT@orcl_11gR2> create table A
2 (col1 varchar2(10),
3 col3 varchar2(10),
4 col5 varchar2(10))
5 ORGANIZATION external
6 (TYPE oracle_loader
7 DEFAULT DIRECTORY EXT_DIR
8 ACCESS PARAMETERS
9 (FIELDS TERMINATED BY ","
10 (col1,
11 col2,
12 col3,
13 col4,
14 col5))
15 location ('test.csv'))
16 /
Table created.
-- select from external table:
SCOTT@orcl_11gR2> select * from a
2 /
COL1 COL3 COL5
---------- ---------- ----------
col1 col3 col5
A C E
1 3 5
3 rows selected.
[Updated on: Fri, 25 January 2013 14:21] Report message to a moderator |
|
|
|
Goto Forum:
Current Time: Tue May 21 12:25:24 CDT 2013
Total time taken to generate the page: 0.15238 seconds
|
] 
Members
Search
Help
Register
Login
Home
