OraFAQ Forum: SQL & PL/SQL » loading table from excel without using util

Home » SQL & PL/SQL » SQL & PL/SQL » loading table from excel without using util (PL/SQL Release 11.1.0.6.0 - Production)

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

loading table from excel without using util [message #621660]

Fri, 15 August 2014 11:46

rkhatiwala
Messages: 178
Registered: April 2007

Senior Member

oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
PL/SQL Release 11.1.0.6.0 - Production
"CORE 11.1.0.6.0 Production"

Hello,

I have an excel file which I want to use to update a table. But cant use the utl_file. Is there any other way to do it without using DBA's help for permission on folders?

Thanks.

Report message to a moderator

Re: loading table from excel without using util [message #621661 is a reply to message #621660]

Fri, 15 August 2014 11:55

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

What do you mean by folders? Where is the file located? You could use external table for csv file.

Report message to a moderator

Re: loading table from excel without using util [message #621662 is a reply to message #621661]

Fri, 15 August 2014 11:59

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>I have an excel file which I want to use to update a table

If it is a real "excel" file (like *xls), then it can't be used by Oracle because Oracle can't access the data inside this BINARY file.

EXCEL can via ODBC connect directly to Oracle DB & issue SQL against the database.
UPDATE is a SQL statement that changes data in 0 or more rows.
Based upon the content of the data in the file, exactly how to determine which rows get changed?

Report message to a moderator

Re: loading table from excel without using util [message #621663 is a reply to message #621661]

Fri, 15 August 2014 12:03

rkhatiwala
Messages: 178
Registered: April 2007

Senior Member

I can convert it to .csv, but after that? Can I use it if its in my local folder, and not on server?

Report message to a moderator

Re: loading table from excel without using util [message #621664 is a reply to message #621662]

Fri, 15 August 2014 12:05

rkhatiwala
Messages: 178
Registered: April 2007

Senior Member

UPDATE table1 SET table.col1 = excelfile.col3, table.col2 = excelfile.col4 WHERE table.col11 = excelfile.col33 AND table.col12 = excelfile.col32

Report message to a moderator

Re: loading table from excel without using util [message #621665 is a reply to message #621663]

Fri, 15 August 2014 12:06

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

rkhatiwala wrote on Fri, 15 August 2014 10:03

I can convert it to .csv, but after that? Can I use it if its in my local folder, and not on server?

sqlldr could be used to INSERT the data into some compatible table in the DB

Report message to a moderator

Re: loading table from excel without using util [message #621666 is a reply to message #621663]

Fri, 15 August 2014 12:09

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

rkhatiwala wrote on Fri, 15 August 2014 22:33

I can convert it to .csv, but after that? Can I use it if its in my local folder, and not on server?

No, you cannot do that if you cannot move file to the directory object which Oracle points to the server.
You can simply use sqlldr in the client to load into a table, then execute the necessary update. Read SQL*Loader

Ed : Didn't see BS replied too. Added link to SQL*Loader wiki.

[Updated on: Fri, 15 August 2014 12:12]

Report message to a moderator

Previous Topic:	multiple currency value
Next Topic:	get number of week day - no matter what national locale is used

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 05:32:41 CDT 2024