OraFAQ Forum: Server Utilities » Problem Loading data using sqlldr

Home » RDBMS Server » Server Utilities » Problem Loading data using sqlldr

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

Problem Loading data using sqlldr [message #130176]

Thu, 28 July 2005 10:11

sikyala
Messages: 33
Registered: May 2005

Member

I have an Excel file with records that I need to load into an Oracle table. I want to use sql loader. However, some of the text in each row contains tabs and commas. Therefore, I don't think I can save it as a tab delimited text file or a comma delimited csv file. Is there a way I can specify in Excel how I want the file to be delimited or can someone provide me with a workaround? Thanks!

Report message to a moderator

Re: Problem Loading data using sqlldr [message #130187 is a reply to message #130176]

Thu, 28 July 2005 11:42

smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida

Senior Member

You can save it as a comma separated file, but put quotes around all of your columns that might have a comma as part of that column's text. Like:

100,12-DEC-2005,hello,"this, I'm afraid, has commas in it",23

Then in sqlldr control file you can use the clause "optionally enclosed by"

Report message to a moderator

Re: Problem Loading data using sqlldr [message #130189 is a reply to message #130187]

Thu, 28 July 2005 11:47

sikyala
Messages: 33
Registered: May 2005

Member

how would I do that? Is there are way to do it in Excel without going to each row to add the quotes? There are over 10,000 rows.

Report message to a moderator

Re: Problem Loading data using sqlldr [message #130191 is a reply to message #130189]

Thu, 28 July 2005 11:58

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

>>l without going to each row to add the quotes?

No way. No manual edits.
What kind of OS are you using ( while invoking sql*loader)
You can write some simple script to do it.

Report message to a moderator

Re: Problem Loading data using sqlldr [message #130211 is a reply to message #130176]

Thu, 28 July 2005 16:16

sikyala
Messages: 33
Registered: May 2005

Member

I am using windows XP

Report message to a moderator

Re: Problem Loading data using sqlldr [message #130352 is a reply to message #130211]

Fri, 29 July 2005 08:30

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

Please post some sample data,controlfile and ddl of table

[Updated on: Fri, 29 July 2005 08:30]

Report message to a moderator

Re: Problem Loading data using sqlldr [message #130357 is a reply to message #130352]

Fri, 29 July 2005 08:42

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

or try this.
You can multiple delimiters above oracle 8i!.
--FIELDS TERMINATED BY 'list of delimiters'
--If file has commas and hashes
FIELDS TERMINATED BY ',#'

Report message to a moderator

Previous Topic:	SQL Loader and End of File Character
Next Topic:	DIM-00018: Failed to stop Oracle Service

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Jul 03 07:39:58 CDT 2024