-
data loading
Hi,
I tried to load raw data (text format) to my database. One of the column contains telephone number like 416-999-9999 on the text file, the format the user want is 4169999999. How can I get rid of hyphens. I am using sqlldr to load the data.
Thanks for your help
-
-
Thank you for your help. I took a quick look at the document you sent but could not find a proper command for this. The replace is to delete the data before loading. This is the first time I tried to use sqlldr and and here is my control file:
...
LOAD DATA
INFILE '/home/oracle/LOAD/T28548518.txt'
insert
INTO TABLE pager_data
fields terminated by ","
(
...
charge decimal external,
phone_num char,
capnum char,
c7 filler,
c8 filler,
...
)
and my data file contains:
...,2.00,416-719-2729,A01-E0414589,...
from the data file, you can see the phone number. What I try to is load these three columns into oracle database, the phone number should be just numbers without hyphens, and if the capnum is the same in each row, add the charge together. Do you think sqlldr have the functionality or I have to manipulate the data from database.
Thanks
-
For your original problem, try:
phone_num char "REPLACE(:phone_num,'-')",
and if the capnum is the same in each row, add the charge together
. . . and what if the phone_number etc are different?
I've never seen an example of loading totals like that - why don't you do some research and let us know?
-
Thanks a lot. The replace function works perfect. I will ask my user about the total charges and get back to you. Thanks for your help!
-
I just found out I need to add up the total charge if the phonenum is the same. Is there anyway I can do this in the sqlldr?
Thanks
-
Originally Posted by lwangf
Thank you for your help. I took a quick look at the document you sent but could not find a proper command for this. The replace is to delete the data before loading. This is the first time I tried to use sqlldr and and here is my control file:
...
LOAD DATA
INFILE '/home/oracle/LOAD/T28548518.txt'
insert
INTO TABLE pager_data
fields terminated by ","
(
...
charge decimal external,
phone_num char,
capnum char,
c7 filler,
c8 filler,
...
)
and my data file contains:
...,2.00,416-719-2729,A01-E0414589,...
from the data file, you can see the phone number. What I try to is load these three columns into oracle database, the phone number should be just numbers without hyphens, and if the capnum is the same in each row, add the charge together. Do you think sqlldr have the functionality or I have to manipulate the data from database.
Thanks
The first post on this thread is for a text file with commas. What if you have no commas to separate rows or fields? The fields are separated at specific character lengths...?
-
Originally Posted by lwangf
I just found out I need to add up the total charge if the phonenum is the same. Is there anyway I can do this in the sqlldr?
Thanks
What do you need done with the total charge? It seems unlikely that you'd want it posted to every line. One option for more sophisticated maipulation of the loaded data is to define the file as an external table. You can then use SQL statements to read it.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|