| External Tables - Tab Separated Values [message #436002] |
Mon, 21 December 2009 00:44  |
chintan.patel
Messages: 162
Registered: July 2008
Location: Ahmedabad
|
Senior Member |
|
|
Hi Friends
I have some data in tab separated format like below
AA AA 11 19-DEC-09 AA AA AA
BB BB 22 29-DEC-09 BB BB BB
CC CC 33 39-DEC-09 CC CC CC
DD DD 44 49-DED-09 DD DD DD
i am tying to use this data from external table so i had created this by below syntax
CREATE TABLE TEMP
(
Col_1 VARCHAR2(100 BYTE),
Col_2 VARCHAR2(100 BYTE),
Col_3 VARCHAR2(100 BYTE),
Col_4 VARCHAR2(100 BYTE),
Col_5 VARCHAR2(100 BYTE)
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY TSV_FILES
ACCESS PARAMETERS
( records delimited by newline
badfile TSV_FILES_BAD:'temp.bad'
logfile TSV_FILES_LOG:'temp.log'
fields terminated by " "
)
LOCATION (TSV_FILES:'temp.tsv')
)
REJECT LIMIT UNLIMITED
LOGGING
NOCACHE
NOPARALLEL;
After creation of this table when i am tring to run query
this query gives below output
COL_1 COL_2 COL_3 COL_4 COL_5
-------------------------------- ------ ------ --------- ---------
AA AA 11 19-DEC-09 AA AA AA
BB BB 22 29-DEC-09 BB BB BB
CC CC 33 39-DEC-09 CC CC CC
DD DD 44 49-DED-09 DD DD DD
Actually my problem is that data can not extract properly from temp.tsv file. result of sql query shows 4 of the 5 columns merged into first column though it separated by tabs. How can i solve this problem.
Thanks in advance
Chintan
|
|
|
|
|
|
| Re: External Tables - Tab Separated Values [message #436011 is a reply to message #436002] |
Mon, 21 December 2009 01:30  |
 |
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
Try not to simulate <Tab> with number of space characters, but rather use its ASCII code (9), such as fields terminated by 0x'9'
Doing so, your example (adjusted to my environment) looks like this:SQL> CREATE TABLE test
2 (
3 Col_1 VARCHAR2(100 BYTE),
4 Col_2 VARCHAR2(100 BYTE),
5 Col_3 VARCHAR2(100 BYTE),
6 Col_4 VARCHAR2(100 BYTE),
7 Col_5 VARCHAR2(100 BYTE)
8 )
9 ORGANIZATION EXTERNAL
10 ( TYPE ORACLE_LOADER
11 DEFAULT DIRECTORY ext_dir
12 ACCESS PARAMETERS
13 ( records delimited by newline
14 badfile 'temp.bad'
15 logfile 'temp.log'
16 fields terminated by 0x'9'
17 )
18 LOCATION ('temp.csv')
19 )
20 REJECT LIMIT UNLIMITED;
Table created.
SQL> select * from test;
COL_1 COL_2 COL_3 COL_4 COL_5
---------- ---------- ---------- ---------- ----------
AA AA 11 19-DEC-09 AA AA AA
BB BB 22 29-DEC-09 BB BB BB
CC CC 33 39-DEC-09 CC CC CC
DD DD 44 49-DED-09 DD DD DD
SQL>
P.S. Right; while I was composing my example, Michel has already answered the question.
[Updated on: Mon, 21 December 2009 01:32] Report message to a moderator |
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
