Dear Friends :

Below is the data which i have to load


SQL*Plus: Release 11.1.0.6.0 - Production on Wed Jan 18 17:47:01 2012

Copyright © 1982, 2007, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL> SQL> SQL> SQL> SQL> 2 3 4 5 6
57140002205124| 23| ST04| 9418285932| 17-JAN-12 11.17.31.820253 AM| Used
54171025176597| 49.86| TU03| 9411165512| 17-JAN-12 11.20.32.943855 AM| Used
54171025182725| 49.86| TU03| 9456310464| 17-JAN-12 11.37.14.346299 AM| Used


(1) first thing i want to remove the txt which is in the bold

(2) my query for creating the table is
CREATE TABLE VMSDATA
(
SERIALNO NUMBER(20),
AMOUNT NUMBER(7,2),
CLASS VARCHAR2(10),
MSISDN NUMBER(12),
VDATE TIMESTAMP(6),
STATUS VARCHAR2(8 BYTE)
)

and my control file for loading the data is

load data
infile 'path'
badfile 'path'
DISCARDFILE 'path'
truncate into table vmsdata
fields terminated by ","
trailing nullcols
(
SERIALNO,
AMOUNT,
CLASS,
MSISDN,
VDATE TIMESTAMP,
STATUS
)
Pls guide me to resolve this issue


Regards
Kaushal

Hi Kaushal,

Can you change all of the | to commas (,)?

If so, you can query the data where it sits, as an external table, no loading:

Please read:

http://www.dba-oracle.com/art_ext_tabs.htm

**********************************************
>> (1) first thing i want to remove the txt which is in the bold

You would have to load it and the "alter table vmsdata drop column xxx";

***********************************************
>> fields terminated by ","

No, they are not terminated by commas!



The Oracle Utilties book has working samples you can download of sql*Loader:

http://www.rampant-books.com/book_0801_oracle_utilities.htm

Good Luck!