OraFAQ Forum: Server Utilities » Urgent

Home » RDBMS Server » Server Utilities » Urgent – SQL Loader problem

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Urgent – SQL Loader problem [message #71970]

Tue, 11 February 2003 13:48

Swamy
Messages: 78
Registered: June 2002

Member

This is a SQL Loader question. I am using SQL Loader Release 9.2.0.1.0 on Unix server. I need to load Tab Delimited file in to a table. I am using �fields terminated by X�09�� syntax and it is loading correctly, when there is no null data for any fields. As I have some times null data for some fields in data file, the resultant data in the columns are shifting to preceding columns which are supposed to be with null. To overcome this, I placed the �nullif� syntax. But this is not helping. Here I am posting my control file. I appreciate in advance for your help.

LOAD DATA
TRUNCATE
INTO TABLE TEMP_PAYERS_dupe
fields terminated by X'09' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS (
ATTRIBUTE_1 CHAR "UPPER(TRIM(:ATTRIBUTE_1))",
ATTRIBUTE_2 CHAR "UPPER(TRIM(:ATTRIBUTE_2))",
ATTRIBUTE_3 CHAR "UPPER(TRIM(:ATTRIBUTE_3))",
ATTRIBUTE_4 CHAR nullif ATTRIBUTE_4="(null)" "UPPER(TRIM(:ATTRIBUTE_4))",
ATTRIBUTE_5 CHAR nullif ATTRIBUTE_5="(null)" "UPPER(TRIM(:ATTRIBUTE_5))",
ATTRIBUTE_6 CHAR "UPPER(TRIM(:ATTRIBUTE_6))",
ATTRIBUTE_7 CHAR "UPPER(TRIM(:ATTRIBUTE_7))",
ATTRIBUTE_8 CHAR "UPPER(TRIM(:ATTRIBUTE_8))",
ATTRIBUTE_9 CHAR "UPPER(TRIM(:ATTRIBUTE_9))",
ATTRIBUTE_10 CHAR "UPPER(TRIM(:ATTRIBUTE_10))",
ATTRIBUTE_11 CHAR "UPPER(TRIM(:ATTRIBUTE_11))",
ATTRIBUTE_12 CHAR "UPPER(TRIM(:ATTRIBUTE_12))",
ATTRIBUTE_13 CHAR "UPPER(TRIM(:ATTRIBUTE_13))",
ATTRIBUTE_14 CHAR "UPPER(TRIM(:ATTRIBUTE_14))",
ATTRIBUTE_15 CHAR "UPPER(TRIM(:ATTRIBUTE_15))",
SEQ_NUM "TEMP_PAYER.NEXTVAL"
)

Thanks,

Swamy

Report message to a moderator

Re: Urgent – SQL Loader problem [message #71975 is a reply to message #71970]

Wed, 12 February 2003 00:22

Barbara Boehmer
Messages: 9097
Registered: November 2002
Location: California, USA

Senior Member

I don't know where you got your data from, but there are some programs, like FoxPro, that leave the tabs, but skip the quotes around the field when it is null. I have been able to load such data successfully by removing the OPTIONALLY ENCLOSED BY '"'.

Report message to a moderator

Re: Urgent – SQL Loader problem [message #71979 is a reply to message #71970]

Wed, 12 February 2003 16:55

Swamy
Messages: 78
Registered: June 2002

Member

Thanks Babara, it's working. But, what to do to compensate OPTIONALLY ENCLOSED BY '"'?

Report message to a moderator

Re: Urgent – SQL Loader problem [message #71981 is a reply to message #71970]

Thu, 13 February 2003 08:48

Swamy
Messages: 78
Registered: June 2002

Member

Thanks Barbara for your help. The data comes from a Bank and they gave us only two options. Either a CSV file or a Tab delimited txt file. As Comma will give a problem with data, I preferred Tab delimitation. The data first comes to Mainframe environment and from there it will come by FTP to our Unix Solaris server, where Oracle database resides. This is a daily process with automation. I hope there won�t be any Tabs in the data.

Report message to a moderator

Re: Urgent – SQL Loader problem [message #73227 is a reply to message #71975]

Fri, 05 March 2004 02:30

Aldo Valerio
Messages: 7
Registered: March 2004

Junior Member

Thanks a million. Your solution helped me out, since I don't use quotes anyway. I've been looking all over the web for this answer.

Report message to a moderator

Previous Topic:	How to schedule job for run on every first day of the month
Next Topic:	SQL*Loader: load tab-delimited file with NULL values

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Jul 01 01:33:49 CDT 2024