如何将含NAME、RICE等列的Excel数据导入Oracle数据库表

Got it, let's walk through how to import your Excel data (with NAME, RICE, SUGAR, TEA columns) into Oracle. I'll cover a few reliable methods depending on what tools you have handy—pick the one that fits your workflow best:

This is the most straightforward option if you prefer a visual interface:

• Open SQL Developer and connect to your Oracle database.
• Right-click on the Tables folder under your target schema, then select Import Data.
• In the import wizard, choose Excel as the data source, then browse to select your Excel file. Click Next.
• Verify column mapping: Make sure NAME, RICE, SUGAR, TEA align with your Oracle table's columns. If you haven't created the target table yet, the wizard lets you generate one on the fly—just match data types (e.g., use VARCHAR2 for NAME, NUMBER for the numeric columns).
• Adjust any optional settings (like skipping the header row or setting batch commit size), then finish the import.

Use this if you need to script the import or handle large datasets:

1. First, save your Excel file as a CSV (File > Save As > CSV UTF-8). This makes it easier for SQL*Loader to process.
2. Create a control file (e.g., excel_import.ctl) with this structure:

LOAD DATA
INFILE 'your_data.csv'
BADFILE 'import_errors.bad'
DISCARDFILE 'discarded_records.dsc'
APPEND -- Use INSERT to create new records, REPLACE to overwrite existing data
INTO TABLE YOUR_TARGET_TABLE
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
    NAME,
    RICE,
    SUGAR,
    TEA
)

3. Run the SQL*Loader command (replace placeholders with your actual credentials and file paths):

sqlldr your_username/your_password@your_oracle_service control=excel_import.ctl log=import_log.log

This method treats your CSV as an Oracle "external table" that you can query directly before inserting into your target table:

1. Convert your Excel file to CSV (same as Method 2) and place it in a directory accessible to the Oracle server.
2. Create a directory object (you'll need DBA privileges or ask your admin to do this):

CREATE OR REPLACE DIRECTORY CSV_IMPORT_DIR AS '/path/to/your/csv/folder';
GRANT READ, WRITE ON DIRECTORY CSV_IMPORT_DIR TO YOUR_USER;

3. Create the external table:

CREATE TABLE EXCEL_DATA_EXT
(
    NAME VARCHAR2(100),
    RICE NUMBER,
    SUGAR NUMBER,
    TEA NUMBER
)
ORGANIZATION EXTERNAL
(
    TYPE ORACLE_LOADER
    DEFAULT DIRECTORY CSV_IMPORT_DIR
    ACCESS PARAMETERS
    (
        RECORDS DELIMITED BY NEWLINE
        SKIP 1 -- Skip the header row from your CSV
        FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
        MISSING FIELD VALUES ARE NULL
    )
    LOCATION ('your_data.csv')
)
REJECT LIMIT UNLIMITED;

4. Insert the data into your target table:

INSERT INTO YOUR_TARGET_TABLE (NAME, RICE, SUGAR, TEA)
SELECT NAME, RICE, SUGAR, TEA FROM EXCEL_DATA_EXT;
COMMIT;

Quick Tips to Avoid Issues #

• Data Type Matching: Double-check that Excel's numeric columns (RICE, SUGAR, TEA) map to Oracle's NUMBER type, and NAME uses VARCHAR2 with enough length.
• Header Rows: Always configure your method to skip the Excel header—otherwise, the column names will be imported as a data row.
• Encoding: If your data has special characters or non-English text, save the CSV as UTF-8 to match Oracle's common character set (AL32UTF8).

内容的提问来源于stack exchange，提问作者user757321