Home » RDBMS Server » Server Utilities » External Tables - Tab Separated Values (Oracle 9.2.0.1.0 )
External Tables - Tab Separated Values [message #436002] Mon, 21 December 2009 00:44 Go to next message
chintan.patel
Messages: 140
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
select * from temp;

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 #436009 is a reply to message #436002] Mon, 21 December 2009 01:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
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

I doubt the query gives unaligned result.
Now how can we know what is in each column and if the values are correct or not?
Use the "Preview Message" button to verify AND CORRECT.

If the separator is tab it is not a space, use X'09' instead of " ".

Regards
Michel
Re: External Tables - Tab Separated Values [message #436011 is a reply to message #436002] Mon, 21 December 2009 01:30 Go to previous messageGo to next message
Littlefoot
Messages: 20900
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

Re: External Tables - Tab Separated Values [message #436020 is a reply to message #436011] Mon, 21 December 2009 02:25 Go to previous messageGo to next message
chintan.patel
Messages: 140
Registered: July 2008
Location: Ahmedabad
Senior Member
Thank You very much sir, my problem is solve. i just want to know for my knowledge what is 0x'9' and where can i find it.

Chintan
Re: External Tables - Tab Separated Values [message #436031 is a reply to message #436020] Mon, 21 December 2009 03:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Database Utilities
Part II SQL*Loader
Part III External Tables

Regards
Michel
Re: External Tables - Tab Separated Values [message #436050 is a reply to message #436020] Mon, 21 December 2009 03:53 Go to previous message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
chintan.patel
what is 0x'9'

It is hexadecimal representation of the <Tab> character.

Here's an example of how it works; not on <Tab>, though - I'll use letter "a":

./fa/7171/0/

Check this table for more information; I guess it displays these values in a quite illustrative way.
  • Attachment: hex_a.PNG
    (Size: 5.02KB, Downloaded 1095 times)
Previous Topic: Error while importing dump
Next Topic: Export in Datapump
Goto Forum:
  


Current Time: Wed Dec 07 18:25:07 CST 2016

Total time taken to generate the page: 0.11935 seconds