« Movin' On | Main | The Health and Wellness Center »
December 9, 2010
How To Import Data From a File To A Table Using PL/SQL
If you have some columns of data in Excel (or any other way you can get either comma delimited or other standard data format), you can import this data into an Oracle table pretty easily using PL/SQL. Here's how:
Create the table you want to import the data into.
Create a .csv comma delimited file of your data. (You can use a lot of other ways to delimit your data, but this is an easy example.)
Next, go to PL/SQL and open Tools -> Text Importer. There are two tabs in the Text Importer - one to set up your data to import, and one to tell it which table and column to put the data in. Do not go to the second tab until you have got your data set up on the first tab - it just erases all of it if you go back to the first tab and change anything.
Just above the first tab, named "Data from Textfile" there is a row of icons. The very first icon is sort of an "open" icon, and sure enough that is what you use to open your .csv file.
Once your data file is open, PL displays a list of the data in the top pane. You go in to the Configuration pane and set up Fieldcount (how many columns). It will then display entries such as Field1 (+0 ..",") and Field2(blah,blah). Just highlight field one and see if it picks the first field of data from your file. And so on for each field. If it doesn't pick the right field, adjust Field Start or Field End (in this case select Character and ",").
Once you have it set so that it picks the correct data for each field, you are ready for Tab 2 - Data to Oracle. This is pretty simple. Pick your schema, table name, and select each field displayed. Make sure each field is mapped to the correct table column.
Press Import.
That's it!
Posted by rossm at December 9, 2010 2:57 PM
Comments
Post a comment
Thanks for signing in, . Now you can comment. (sign out)
(If you haven't left a comment here before, you may need to be approved by the site owner before your comment will appear. Until then, it won't appear on the entry. Thanks for waiting.)