Excel -> CSV -> Oracle

SQL> create table instruktur(

  2  inisial char(3),

  3  namainstruktur varchar2(20),

  4  honor number(12),

  5  spv char(3));

Table created.

File C:\budi\h10\instr.*(dat, txt, ctl, d.l.l.)

LOAD DATA

INFILE *

INTO TABLE INSTRUKTUR

FIELDS TERMINATED BY ','

OPTIONALLY ENCLOSED BY '"'

(INISIAL, NAMAINSTRUKTUR, HONOR, SPV)

BEGINDATA

YSH, "YUNI SHARA", 500000, DBM

KDY, "KRISDAYANTI", 300000, DBM

DBM, "DREW BARRYMORE", 400000, DBM

CZJ, "CATHERINE ZETA JONES", 300000, DBM

Dari command prompt jalankan perintah ini:

c:\>sqlldr userid=badung/badungjuga@nakula, control='C:\budi\h10\instr.ctl'

 Excel -> CSV -> Oracle

Save the Excel spreadsheet as file type 'CSV' (Comma-Separated Values).

Transfer the .csv file to the Oracle server.

Create the Oracle table, using the SQL CREATE TABLE statement to define the table's column lengths and types. Here's an example of an sqlplus 'CREATE TABLE' statement:

CREATE TABLE SPECIES_RATINGS

(SPECIES VARCHAR2(10),

COUNT NUMBER,

RATING VARCHARC2(1));

Use sqlload to load the .csv file into the Oracle table. Create a sqlload control file like this:

load data

infile spec_rat.csv

replace

into table species_ratings

fields terminated by ','

(species,count,rating)

Invoke sqlload to read the .csv file into the new table, creating one row in the table for each line in the .csv file. This is done as a Unix command:

% sqlload userid=username/password control=<filename.ctl> log=<filename>.log

This will create a log file <filename>.log. Check it for loading errors.

Use these sqlplus commands to check the Oracle table:

DESCRIBE SPECIES_RATINGS;

SELECT COUNT(*) FROM SPECIES_RATINGS;

SELECT * FROM SPECIES_RATINGS WHERE ROWNUM < 6;