How to insert rows in an Excel sheet into SQL Server 2005 or oracle

Question

How to insert rows in an Excel sheet into SQL Server 2005 or oracle ?

For example if I have 5000 rows in Excel sheet how can I insert these rows into a table in any database

Answer 1

I would recommend saving the Excel file as a tab or comma separated values file, and importing into the database using the appropriate command. For example, MySQL uses LOAD DATA INFILE. For MS-SQL see this page at msdn. I am unsure how to import data into Oracle, though many searches indicate it is possible. I think the export to a format such as CSV will be the step you need, though.

Answer 2

And for Oracle (see JYelton's answers for MySQL and MS-SQL), the command would be:

sqlldr username@server/password control=loader.ctl

The CTL file will have details about the file you need to import. It is necessary that you have convert the excel file into CSV format.

Sample entries in Control file.

load data
 infile 'c:\data\mydata.csv'
 into table emp
 fields terminated by "," optionally enclosed by '"'          
 ( empno, empname, sal, deptno )

More details as ORAFAQ for SQL Loader . You can also use UTL_FILE.

Or, think of creating an external table with CSV file as data and manipulate details on them.

Sample CREATE statement below:

CREATE TABLE countries_ext (
  EMP_ID        VARCHAR2(5),
  EMP_NAME      VARCHAR2(50),
  DEPT          VARCHAR2(50)
)
ORGANIZATION EXTERNAL (
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY ext_tables
  ACCESS PARAMETERS (
    RECORDS DELIMITED BY NEWLINE
    FIELDS TERMINATED BY ','
    MISSING FIELD VALUES ARE NULL
    (
      employee_id      CHAR(5),
      employee_name    CHAR(50),
      department       CHAR(50)
    )
  )
  LOCATION ('employee.txt')
)
PARALLEL 5
REJECT LIMIT UNLIMITED;

Details here: Oracle documentation for External Tables.