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 Go to next message
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 #575612 is a reply to message #575611] Thu, 24 January 2013 14:06 Go to previous messageGo to next message
BlackSwan
Messages: 22726
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: External Table for CSV delimited file skipping columns [message #575613 is a reply to message #575611] Thu, 24 January 2013 14:24 Go to previous messageGo to next message
Michel Cadot
Messages: 58958
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You miss the purpose of an external table, it is used to map the file, then you only retrieve the columns you want in the format you want, possibly using functions and expressions.
So your question is irrelevant to external table.

Regards
Michel
Re: External Table for CSV delimited file skipping columns [message #575615 is a reply to message #575613] Thu, 24 January 2013 15:23 Go to previous messageGo to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
Yes thanks i understood.

I thought even there might be an option even while mapping to skip the rows.
Re: External Table for CSV delimited file skipping columns [message #575640 is a reply to message #575615] Fri, 25 January 2013 00:58 Go to previous messageGo to next message
Michel Cadot
Messages: 58958
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
To skip the rows, yes, use SKIP option but you asked about skip columns.

Regards
Michel
Re: External Table for CSV delimited file skipping columns [message #575713 is a reply to message #575611] Fri, 25 January 2013 14:20 Go to previous message
Barbara Boehmer
Messages: 7974
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

Previous Topic: Using Nested Table (merged)
Next Topic: Fetch records based on 2 status
Goto Forum:
  


Current Time: Tue Sep 02 10:39:58 CDT 2014

Total time taken to generate the page: 0.11673 seconds