Home » RDBMS Server » Server Utilities » SQL Loader
| SQL Loader [message #324225] |
Sat, 31 May 2008 14:25  |
mandys
Messages: 14
Registered: February 2008
Location: Pittsburgh
|
Junior Member |
|
|
Hi,
Need some urgent help with this.I have a csv file in which I have
one logical record on multiple lines.The number of lines is not fixed.I tried inserting a coulmn which has (*) character in it and used the continueif this option but it is not working right.
please let me know if there is some option that will take care of this.
this is the table structure..
(oracle 10g ..windows o/s)
EMPLOYER_NAME VARCHAR2(50),
ACCOUNT_NO VARCHAR2(30),
EMPLOYEE_COUNT VARCHAR2(5),
EMPLOYEE_NAME VARCHAR2(30),
STATUS VARCHAR2(20),
SDATE DATE,
CITY_CODE NUMBER(3),
ADDR_1 VARCHAR2(30),
FED_ID VARCHAR2(10),
EMPLOYEE_TITLE VARCHAR2(40),
CITY VARCHAR2(30),
STATE VARCHAR2(2),
ZIP VARCHAR2(5),
PHONE_1 VARCHAR2(15),
PHONE_2 VARCHAR2(30)
------------------------------------------------------
-OPTIONS (SKIP=1)
LOAD DATA
INFILE 'c:\pitts\pitts.csv'
BADFILE 'C:\pitts\pit.bad'
DISCARDFILE 'C:\pitts\pit.dis'
continueif this(01:01='*')
INTO TABLE pit_employers
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
--FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY ' ' TRAILING NULLCOLS
( employer_name "UPPER(trim(:EMPLOYER_NAME))" ,
ACCOUNT_NO "TRIM(:account_no)" ,
employee_count ,
employee_name "upper(trim(:employee_name))" ,
status constant 'ADDED' ,
sdate ,
Filler1 Filler ,
city_code constant 110 ,
Filler2 Filler ,
--how do i get into the next line
Addr_1 "trim(:addr_1)" ,
Fed_id "trim(replace(fed_id,'-',''))" ,
Filler3 Filler ,
employee_title "trim(:employee_title)" ,
Filler4 Filler ,
Filler5 Filler ,
City "trim(:city)" ,
state "trim(:state)" ,
Zip ,
phone_1 ,
Filler6 Filler ,
phone_2 Filler ,
)
--"REPLACE(:phone_1,'()-','')"
Thanks,
Mandy
|
|
|
|
| Re: SQL Loader [message #324229 is a reply to message #324225] |
Sat, 31 May 2008 15:25  |
 |
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
Senior Member |
|
|
http://www.orafaq.com/forum/t/88153/0/
Please read & follow Posting Guidelines as stated in URL above.
>one logical record on multiple lines.The number of lines is not fixed.
So how do you know where 1 record ends & the next record begins?
I hope you realize that if each record in the csv contained a complete "logical" record, it would be trivial to load.
There is NO guarantee that sqlldr can load every flat file.
[Updated on: Sat, 31 May 2008 15:25] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: SQL Loader [message #324291 is a reply to message #324244] |
Sun, 01 June 2008 10:07 |
mandys
Messages: 14
Registered: February 2008
Location: Pittsburgh
|
Junior Member |
|
|
Thanks a lot barb!I will try using the continueif option you mentioned and see if that works..
Thanks again!!!
|
|
|
|
|
|
|
|
| Re: SQL Loader [message #324488 is a reply to message #324481] |
Mon, 02 June 2008 14:34 |
mandys
Messages: 14
Registered: February 2008
Location: Pittsburgh
|
Junior Member |
|
|
|
Thanks for your prompt response..I have manually inserted the asterisk.By using sql loader will I be able to load data which is in that kind of a format???
|
|
|
|
| Re: SQL Loader [message #324491 is a reply to message #324225] |
Mon, 02 June 2008 15:14 |
|
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
Senior Member |
|
|
>.I have manually inserted the asterisk.
Are you going to do the same for every file that gets loaded?
What happens when you insert an asterisk in the wrong spot or forget to add one where it is needed?
>By using sql loader will I be able to load data which is in that kind of a format???
I give up. Will you?
What happened when you tried it?
[Updated on: Mon, 02 June 2008 15:15] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: SQL Loader [message #324494 is a reply to message #324492] |
Mon, 02 June 2008 15:37 |
 |
Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
If you put your asterisks in the right places and make your control file match, and make sure your columns are large enough to hold the data, then you can load it. You will need to figure out how to parse out your city, state, zip, and phone. It would be much better if you could get the data in a better format. Where does it come from? Does somebody give it to you like this or do you extract it from some other source yourself?
-- mandy.csv (with asterisks at beginning of each record):
,Employer Master Listing Of Active Accounts For 110 - CUMBERLAND TOWNSHIP - ADAMS COUNTY,,,,,,
,,"Listed By ""Account"", ""Name"", ""Name2"", ""Address"", ""Address2"", ""City Code""",,,,,
,,(Employee Statistics are for Tax Year 2006),,,,,
*abs,account no:0000000,281,,KAY STUFFLE,Added,7/1/1976
,P O BOX 3129,fed id:00000000000,,,ACCOUNTING ADMINISTRATOR,,
,GETTYSBURG PA 17325,Phone: (717)334-3161,"7,315,354.03",,000-000-000,,
,,Fax: (717)338-2130,,,,,
*sec,account no:0000000,394,,ELANA,Added,8/8/1990
,20 WHIPPANY RD,fed id:00000000000,,,PAYROLL,,
,MORRISTOWN NJ 07960,Phone: (973)397-3700,"15,805,114.17",,000-000-000,,
,,Fax: (973)397-3710,,,,,
*tns,account no:0000000,200,,DON KOONTZ,Added,8/19/1993
,1560 FAIRFIELD RD,fed id:00000000000,,,000-000-000,,
,P O BOX 3669,Phone: (717)334-1131,"3,615,320.93",,,,
,GETTYSBURG PA 17325,Fax: 334-4243,,,,,
*GREEN ACRES,account no:0000000,60,,NANCY C TYLER,Added,5/13/1994
,zzzz,fed id:00000000000,,,BUS OFFICE MGR,,
,595 BIGLERVILLE RD,Phone: (717)334-6249,"1,194,137.43",,595 BIBLERVILLE RD,,
,GETTYSBURG PA 17325,Fax: 334-7847,,,"GETTYSBURG, PA 17325",,
,,,,,000-000-000,,
*xyz,account no:00000000,64,,DAVID MENO,Added,1/3/1997
,1219 CHAMBERSBURG RD,fed id:0000000000000,,,CONTROLLER,,
,GETTYSBURG PA 17325,Phone: (717)337-1224,"2,231,643.25",,13 SQUIRE CIR,,
,,Fax: (717)337-3182,,,"MCSHERRYSTOWN, PA 17344",,
,tbs inc,,,,000-000-000,,
*,account no:00000000,39,,DONNA WHITMOYER,Added,12/27/2000
,1750 EMMITSBURG RD,fed id:0000000000000,,,OFFICE MGR,,
,P O BOX 4537,Phone: (717)334-6777,"1,938,092.97",,,,
,GETTYSBURG PA 17325,Fax: (717)334-0854,,,,,
*suz inc,account no:00000000,7,,SUZANNE LONKY,Added,5/13/2004
,abc inn,fed id:0000000000000,,,OWNER,,
,40 HOSPITAL RD,Phone: (717)337-1342,"12,680.19",,40 HOSPITAL RD,,
,GETTYSBURG PA 17325,,,,"GETTYSBURG, PA 17325",,
*m and m associates,account no:00000000,2,,MARSHALL L MILLER,Added,1/6/2006
,REAL ESTATE LLC,fed id:0000000000000,,,MEMBER/OWNER,,
,914 FAIRFIELD RD,Phone: (717)337-9950,"48,893.01",,534 CAMP GETTYSBURG RD,,
,GETTYSBURG PA 17325,Fax: (717)337-1133,,,"GETTYSBURG, PA 17325",,
,,,,,000-000-000,,
-- test.ctl to match mandy.csv:
OPTIONS (SKIP=1)
LOAD DATA
INFILE 'mandy.csv'
BADFILE 'test.bad'
DISCARDFILE 'test.dis'
CONTINUEIF NEXT PRESERVE (1) <> '*'
INTO TABLE pit_employers
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
( employer_name "UPPER (LTRIM (TRIM (:employer_name), '*'))"
, account_no "TRIM (:account_no)"
, employee_count
, filler1 FILLER
, employee_name "UPPER (TRIM (:employee_name))"
, status
, sdate "TO_DATE (:sdate, 'MM/DD/YYYY')"
, city_code CONSTANT 110
, addr_1 "TRIM (:addr_1)"
, fed_id "TRIM (REPLACE (:fed_id, '-', ''))"
, filler2 FILLER
, filler3 FILLER
, employee_title "TRIM (:employee_title)"
, filler4 FILLER
, filler5 FILLER
)
-- table with columns of adequate size:
SCOTT@orcl_11g> CREATE TABLE pit_employers
2 (EMPLOYER_NAME VARCHAR2(50),
3 ACCOUNT_NO VARCHAR2(30),
4 EMPLOYEE_COUNT VARCHAR2(5),
5 EMPLOYEE_NAME VARCHAR2(30),
6 STATUS VARCHAR2(20),
7 SDATE DATE,
8 CITY_CODE NUMBER(3),
9 ADDR_1 VARCHAR2(30),
10 FED_ID VARCHAR2(20),
11 EMPLOYEE_TITLE VARCHAR2(40))
12 /
Table created.
SCOTT@orcl_11g>
-- load and result:
SCOTT@orcl_11g> HOST SQLLDR scott/tiger CONTROL=test.ctl LOG=test.log
SCOTT@orcl_11g> COLUMN employer_name FORMAT A13 WORD_WRAPPED
SCOTT@orcl_11g> COLUMN account_no FORMAT A19
SCOTT@orcl_11g> COLUMN employee_name FORMAT A17
SCOTT@orcl_11g> COLUMN status FORMAT A6
SCOTT@orcl_11g> COLUMN employee_title FORMAT A24
SCOTT@orcl_11g> SELECT * FROM pit_employers
2 /
EMPLOYER_NAME ACCOUNT_NO EMPLO EMPLOYEE_NAME STATUS SDATE CITY_CODE ADDR_1 FED_ID EMPLOYEE_TITLE
------------- ------------------- ----- ----------------- ------ ----------- ---------- ------------------------------ -------------------- ------------------------
ABS account no:0000000 281 KAY STUFFLE Added 01-JUL-1976 110 P O BOX 3129 fed id:00000000000 ACCOUNTING ADMINISTRATOR
SEC account no:0000000 394 ELANA Added 08-AUG-1990 110 20 WHIPPANY RD fed id:00000000000 PAYROLL
TNS account no:0000000 200 DON KOONTZ Added 19-AUG-1993 110 1560 FAIRFIELD RD fed id:00000000000 000-000-000
GREEN ACRES account no:0000000 60 NANCY C TYLER Added 13-MAY-1994 110 zzzz fed id:00000000000 BUS OFFICE MGR
XYZ account no:00000000 64 DAVID MENO Added 03-JAN-1997 110 1219 CHAMBERSBURG RD fed id:0000000000000 CONTROLLER
account no:00000000 39 DONNA WHITMOYER Added 27-DEC-2000 110 1750 EMMITSBURG RD fed id:0000000000000 OFFICE MGR
SUZ INC account no:00000000 7 SUZANNE LONKY Added 13-MAY-2004 110 abc inn fed id:0000000000000 OWNER
M AND M account no:00000000 2 MARSHALL L MILLER Added 06-JAN-2006 110 REAL ESTATE LLC fed id:0000000000000 MEMBER/OWNER
ASSOCIATES
8 rows selected.
SCOTT@orcl_11g>
|
|
|
|
| Re: SQL Loader [message #324634 is a reply to message #324494] |
Tue, 03 June 2008 07:00 |
mandys
Messages: 14
Registered: February 2008
Location: Pittsburgh
|
Junior Member |
|
|
Hi Barb,
Thanks a lot for taking time..I will make certain modifications and see how it works out...
Thanks again!!!
Mandy
|
|
|
|
|
|
|
|
| Re: SQL Loader [message #324721 is a reply to message #324652] |
Tue, 03 June 2008 15:36 |
|
Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
| mandeepmandy wrote on Tue, 03 June 2008 07:03 | Hi Barb
I have almost same .ctl for this loading.But the thing is how can one insert a new line indicator say '*' or anything manually.I mean to say that if the file has many records.Any idea how to do it by some script(.sh or .bat)? May be by reading some file and data attributes.
Thanks
Mandeep
|
It is, of course, ridiculous to have to manually insert anything. Obviously, this data comes from somewhere, but the original poster has not responded to questions regarding the source of the data. If it is extracted from some other database into a text file, then it can be selected with an asterisk or some other distinguishing character(s) concatenated in front of the first column or after the last column. With some data, there are identifying things within the data that can be used. Wherever, this data comes from, it should be extractable in a more easily loadable format. There is nothing distinctive about this data, that would allow you to automatically insert an asterisk in the right place. If you could tell where to insert the asterisk, you wouldn't need the asterisk.
|
|
|
|
| Re: SQL Loader [message #324896 is a reply to message #324721] |
Wed, 04 June 2008 08:04 |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
| Barbara Boehmer wrote on Tue, 03 June 2008 16:36 |
It is, of course, ridiculous to have to manually insert anything. Obviously, this data comes from somewhere, but the original poster has not responded to questions regarding the source of the data.
|
Somehow, I think mandy has transformed into mandeepmandy, even though their IP addresses are different. Mandy claims to be from Pittsburgh and the IP address of mandeepmandy is located in Pittsburgh.
[Updated on: Wed, 04 June 2008 08:08] Report message to a moderator |
|
|
|
|
|
| Re: SQL Loader [message #325043 is a reply to message #324494] |
Wed, 04 June 2008 21:56 |
mandys
Messages: 14
Registered: February 2008
Location: Pittsburgh
|
Junior Member |
|
|
Hi Barb,
Thanks for your help.But it was very difficult to load that data so had some one write a vb script to get the data in a single row..I usually get the data from our clients..
Also I am not Mandeep ..He is a different guy..
Thanks again and once again appreciate your time and help.
Mandy
|
|
|
|
Goto Forum:
Current Time: Tue Dec 24 11:22:58 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
