How to import data from an Excel file into a table?

How to import data from an Excel file into a table?

  • METHOD 1: Click Tools → Import → Import Wizard. This window can be used to import data from various kinds of source files into a table.
     
  • METHOD 2: SQL*Loader You can use TOAD's interface to a program from Oracle called sqlloader. Save the Excel file as a comma-delimited (.csv) or tab-delimited (.txt) file. If your data has commas within a text field, save your file as tab-delimited.
    1. Open DBA→Sql Loader Wizard
    2. Choose "build a new control file". Next.
    3. Click "Add". Choose your comma-delimited or tab-delimited file. Next.
    4. Click "Add". Choose your table.
    5. If you are loading a comma-delimited file, type a comma in the "all fields delimited by" combo box. If you are loading a tab-delimited file, select "TAB" from the "all fields delimited by" combo box. Next.
    6. Choose a "load method" noting the following:
      • TRUNCATE will erase whatever is in the table before loading.
      • INSERT will insert data if table is empty but will return an error if table has any data in it.
      • APPEND will insert data without deleting any existing data. You might get duplicates this way but it's a good method if you're trying to get that last line that somehow got munged for some reason or another.
    7. If your file has column headers in its first row, type a 1 in the skip box. Next.
    8. Type a control file name in the control file name box. Next.
    9. Choose either "execute now" to load the data now or "just build the control file" to build the .ctl file. Once you have the ctl file theoretically you don't even need TOAD, as you could just use sqlloader on any machine that has sqlloader and can connect to the database. But why would you want to. :)  I choose execute now.

Note: If you are working with Oracle 8.0 or earlier on a Windows 2000 or later OS, uncheck "Watch Progress"

Note: once you have a ctl file you can:

  1. Choose DBA→Sql Loader Wizard
  2. Choose "Use control file". Next.
  3. Select your control file. Next.
  4. Choose "execute now". Finish.
76144 4 /
Follow / 12.13.2013 at 7:40pm

this was not helpful.

Follow / 12.13.2013 at 8:11pm

@mdahlst

could you open issue and tell what problems you have?

Brg

Damir

Follow / 1.22.2014 at 6:54am

Nice article, thanks.  Having never tried the Import wizard, I have to say I'm quite impressed.  For one who spent far too much time hacking together scripts to massage/import data files, let alone XLS to CVS, etc... This is very refreshing to "just have it work".  Thanks for the pointer.  Go Method1!

Follow / 7.15.2014 at 3:38pm

I have a simple excel spreadsheet - one column of data.  I want to use the import function but I am having issues. I am doing the following steps:

1. In Object Palette - Right click on the table name (the table is empty)

2. Action Console

3. Import Data

My problem - I do not have the option to select the file that has my data to import.  I see Section Destination  Schema  Object Type:Table  Object Name: My Table

Where is the browser to select the data?

Please help