Solved

Load data with sqlldr

Posted on 2013-06-17
3
469 Views
Last Modified: 2013-06-18
Hi people, I have the data:
23/04/13 11:10:46|| 1366715446 || 134.537186 || 443 || || SUR_3B_1_____VC || 330127
23/04/13 11:29:56|| 1366716596 || 133.877991 || 699 || || SUR_3B_1_____VC || 330127
23/04/13 11:30:09|| 1366716609 || 136.075317 || 220 || || SUR_3B_1_____VC || 330127
23/04/13 11:36:17|| 1366716977 || 137.536545 || 636 || || SUR_3B_1_____VC || 330127
23/04/13 11:55:23|| 1366718123 || 136.921295 || 619 || || SUR_3B_1_____VC || 330127
23/04/13 11:59:29|| 1366718369 || 137.426682 || 402 || || SUR_3B_1_____VC || 330127
23/04/13 12:00:00|| 1366718400 || 137.426682 || 0 || || SUR_3B_1_____VC || 330127
23/04/13 12:10:26|| 1366719026 || 136.712540 || 313 || || SUR_3B_1_____VC || 330127
23/04/13 12:55:38|| 1366721738 || 136.031372 || 962 || || SUR_3B_1_____VC || 330127
23/04/13 12:58:15|| 1366721895 || 136.646622 || 17 || || SUR_3B_1_____VC || 330127
23/04/13 13:00:00|| 1366722000 || 136.536758 || 0 || || SUR_3B_1_____VC || 330127
23/04/13 13:16:50|| 1366723010 || 135.646835 || 530 || || SUR_3B_1_____VC || 330127
23/04/13 13:22:51|| 1366723371 || 136.415909 || 296 || || SUR_3B_1_____VC || 330127
23/04/13 13:31:54|| 1366723914 || 135.910522 || 119 || || SUR_3B_1_____VC || 330127
23/04/13 13:34:15|| 1366724055 || 136.646622 || 255 || || SUR_3B_1_____VC || 330127
23/04/13 13:37:56|| 1366724276 || 136.108276 || 914 || || SUR_3B_1_____VC || 330127

Open in new window

and after I executed the sql loader string, just the line :
23/04/13 12:00:00|| 1366718400 || 137.426682 || 0 || || SUR_3B_1_____VC || 330127
23/04/13 13:00:00|| 1366722000 || 136.536758 || 0 || || SUR_3B_1_____VC || 330127

Open in new window

Is inserted in the table target.
I've  tried with the position of data, inside the control file, but I had no success
Can give me an idea for the solution?
Here the control file:
LOAD DATA
CHARACTERSET WE8ISO8859P1
INTO TABLE XAJTDB.A_5MIN_033_temp
Append
fields terminated by "||"
OPTIONALLY ENCLOSED BY '"'
(UTCTIME Date "DD-MM-YYYY HH24:Mi:SS",
EPOCH Integer external,
VALOR_INST,
MS Integer,
TLQ Integer,
TAG Char,
PUNTO Integer external)

Open in new window

Here the script to table creation:
CREATE TABLE A_5MIN_033_TEMP
(
  UTCTIME     DATE                              NOT NULL,
  EPOCH       INTEGER                          NOT NULL,
  VALOR_INST  FLOAT(126),
  MS          INTEGER,
  TLQ         VARCHAR2(20 CHAR),
  TAG         VARCHAR2(30 CHAR),
  PUNTO       INTEGER
)

Open in new window

Thanks!
0
Comment
Question by:carlino70
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39254364
With dates in the file like 23/04/13 12:00:00 and your control file using "DD-MM-YYYY HH24:Mi:SS",  I'm surprised any data is being loaded.

What does the log file say about the 'bad' data?
0
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 39254428
Check your log file.  For WS and TLQ you should see it didn't have the 'terminated by ||' and said NEXT 4.

Declare them external as well.  I was able to load with that.

I also modified the date string to match the file format.

LOAD DATA
CHARACTERSET WE8ISO8859P1
INTO TABLE A_5MIN_033_temp
Append
fields terminated by "||"
OPTIONALLY ENCLOSED BY '"'
(UTCTIME Date "DD/MM/YY HH24:MI:SS",
EPOCH Integer external,
VALOR_INST,
MS Integer external,
TLQ Integer external,
TAG Char,
PUNTO Integer external
)

Open in new window

0
 

Author Comment

by:carlino70
ID: 39257161
Thanks, I will implement this solution and then I will comment on the results.

regards
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question