Home » SQL & PL/SQL » SQL & PL/SQL » loading table from excel without using util (PL/SQL Release 11.1.0.6.0 - Production)
loading table from excel without using util [message #621660] Fri, 15 August 2014 11:46 Go to next message
rkhatiwala
Messages: 144
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.
Re: loading table from excel without using util [message #621661 is a reply to message #621660] Fri, 15 August 2014 11:55 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2449
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.
Re: loading table from excel without using util [message #621662 is a reply to message #621661] Fri, 15 August 2014 11:59 Go to previous messageGo to next message
BlackSwan
Messages: 22911
Registered: January 2009
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?
Re: loading table from excel without using util [message #621663 is a reply to message #621661] Fri, 15 August 2014 12:03 Go to previous messageGo to next message
rkhatiwala
Messages: 144
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?
Re: loading table from excel without using util [message #621664 is a reply to message #621662] Fri, 15 August 2014 12:05 Go to previous messageGo to next message
rkhatiwala
Messages: 144
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
Re: loading table from excel without using util [message #621665 is a reply to message #621663] Fri, 15 August 2014 12:06 Go to previous messageGo to next message
BlackSwan
Messages: 22911
Registered: January 2009
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
Re: loading table from excel without using util [message #621666 is a reply to message #621663] Fri, 15 August 2014 12:09 Go to previous message
Lalit Kumar B
Messages: 2449
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:
  


Current Time: Sat Oct 25 10:48:20 CDT 2014

Total time taken to generate the page: 0.13979 seconds