Loading CSV files into Oracle tables

pts.
Tags:
Corporate Financial Applications
Database Management Systems
ERP
Financials
Human Resources
Informix
iReceivables
Microsoft Access
Oracle
Payables
Procurement
Projects
I want to load data from the Microsoft Excel sheet to my Oracle 8i database. Can I achieve this using TOAD? This question posed on 10 March 2005 You cannot use TOAD to move your data from MS Excel to Oracle. You can save the Excel spreadsheet as a comma-separated-values (CSV) or text file. Then use Oracle's SQL*Loader utility to load the data.

Answer Wiki

Thanks. We'll let you know when a new response is added.

Hi,

You can use the INTO TABLE clause to specify a table-specific loading method (INSERTAPPENDREPLACE, or TRUNCATE) that applies only to that table. That method overrides the global table-loading method. The global table-loading method is INSERT, by default, unless a different method was specified before any INTO TABLE clauses.

So by default your table load will be in INSERT mode, which does require the table to be empty.

The documentation also explains how to load data into a non-empty table; in your case you want to preserve the existing data:

If data already exists in the table, then SQL*Loader appends the new rows to it. If data does not already exist, then the new rows are simply loaded. You must have SELECT privilege to use the APPEND option.

So your control file will need to say something like this (as shown in their example):

LOAD DATA
INFILE 'my_file.dat'
BADFILE 'my_file.bad'
DISCARDFILE 'my_file.dsc'
APPEND
INTO TABLE my_table
...

You could also consider using the new CSV data as an external table and inserting to your real table from that, which might be a bit more flexible.

Hope this helps.

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Daksys
    Using the TOAD V 9.0.1, there is a option to import Excel,CSV and test files direclty into the database tables.
      Right Click on the table and you will find 'Import using Excel,CSV,Text" as an option. Click on the menu item
      Select the tabel you want to import the data into. You can leave he options provided as is or change it as you like..
      Click on the "Show Data" button
      It would display all the data present in the selected table
      Next click on the "Execute Now" button and the wizard open to import the file. From here it should be pretty easy to follwo the wizard
    10 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following