Re: Using SQLLOAD to load string into valid date
Date: 1998/03/31
Message-ID: <6frolh$dnc$1_at_nnrp1.dejanews.com>#1/1
You can do this using following method:
- Create another table t2 for temporary use to hold the information.
You may use following statement:-
create table t2 (id VARCHAR2(9), temp_load_date VARCHAR2(22))
2. Create a SQL*LOADER control file to load the data as follows:-
-----t1.ctl-------Start-----
LOAD DATA
INFILE *
INTO TABLE t2
FIELDS TERMINATED BY ","
(id, temp_load_date)
BEGINDATA
ABCDEFGHI,1997-09-17-15.04.57.00
---end of t1.ctl file----
3. Load the data using above t1.ctl file in SQL*LOADER.
4. Verify if the data is loaded properly in table t2.
Use following query to verify:
Select * from t1;
5. It will populate table t2 ( intermediate table ) we created. 6. Now you have data in table t2, and you can insert rows into t1 using table t2. You can use following SQL command at SQL*PLUS :--
insert into t1 select id, to_date(substr(temp_load_date,1,19),
'YYYY-MM-DD-HH24.MI.SS')
FROM T2;
It will insert rows into table t1.
7. You can check if the data is loaded properly in table t1 using query: Select * from t1;
8. If you are satisfied, then you may now drop the intermediate table t2. SQL
for that is:
Drop table t2;
I used the above process and here is the output of select in step-7 is:
SQL> r
1* SELECT * FROM T1
ID LOAD_DATE
--------- ---------
ABCDEFGHI 17-SEP-97
SQL>
Hope this is helpful,
Manoj Jain
Oracle DBA
------Question Asked-------
Subject: Using SQLLOAD to load string into valid date
From: "Mike Guerrero" <mguerrer_at_mail.sdsu.edu>Date: 1998/03/30 newsgroups: comp.databases.oracle.tools[Subscribe to comp.databases.oracle.tools]
Hi, everyone: I want to load a number of records from a file into a table, I
know I can use SQLLOAD
to do it, however I am having troubles loading the second field(the oneafter
the first comma), I wonder
if anyone has had to load a field like this one and would be kind to share
that information,
thanks and take care.
RECORD: ABCDEFGHI,1997-09-17-15.04.57.00 the second field : '1997-09-17-15.04.57.00'needs to be loaded/parsed as a valid date format.
Can I do it using SUBSTR in SQLLOADER or is there simpler way?
The table is t1 (id VARCHAR2(9), load_date DATE).Thanks,
-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/ Now offering spam-free web-based newsreading Received on Tue Mar 31 1998 - 00:00:00 CEST