SQL 如何将数据直接从 Excel 插入 Oracle 数据库

How to insert data directly from Excel to Oracle Database

提问by Iftekhar

I would like to know how to insert data from Excel to Oracle meaning lets say i have a worksheet full of data and i want to insert them all into Oracle database.

我想知道如何将数据从 Excel 插入 Oracle 意思是说我有一个充满数据的工作表，我想将它们全部插入到 Oracle 数据库中。

how do i do that?

我怎么做？

thanks in advance.

提前致谢。

回答by thatjeffsmith

Many Oracle IDEs offer this feature.

许多 Oracle IDE 提供此功能。

Oracle SQL Developer:

Oracle SQL 开发人员：

• imports from CSV to a new or existing table
• imports from XLS/XLSX files to a new or existing table
• can setup an External Table or SQL*Loader session to import from a delimited file to a new or existing table
• offers a command-line interface for automation purposes

• 从 CSV 导入到新表或现有表
• 从 XLS/XLSX 文件导入到新表或现有表
• 可以设置外部表或 SQL*Loader 会话以从分隔文件导入到新表或现有表
• 提供用于自动化目的的命令行界面

I talk about these features here.

我在这里谈论这些功能。

回答by Tharunkumar Reddy

convert your excel file format to .csv format with using save as option

使用另存为选项将您的 excel 文件格式转换为 .csv 格式

Save below content in a file with .ctl extension in the D drive under folder_name folder. Before running this .ctl make sure that table should be present in your schema with the distinct column names not like in your posted image. And the column name should be match with names of .ctl file(sc_id, sal, etc). And the datatypes of your columns should be match with the data present in a .csv file. And also make sure that your tableshould be emptyotherwise you should use truncateor appendoptions in your .ctl file.

将以下内容保存在文件夹名称文件夹下的 D 驱动器中扩展名为 .ctl 的文件中。在运行此 .ctl 之前，请确保该表应该存在于您的架构中，并且具有不同于您发布的图像中的不同列名。并且列名应与 .ctl 文件（sc_id、sal 等）的名称匹配。并且您的列的数据类型应与 .csv 文件中的数据匹配。还要确保您table应该在 .ctl 文件中empty使用truncate或append选项。

 LOAD DATA
    INFILE 'D:\folder_name\csvfile_name.CSV'
    BADFILE 'D:\folder_name\csvfile_name.BAD'
    DISCARDFILE 'D:\folder_name\csvfile_name.log'
    logfile 'D:\folder_name\csvfile_name.DSC'
    iNTO TABLE schema_name.table_name
    FIELDS TERMINATED BY ','
    TRAILING NULLCOLS

    (
    sc_id,
    sal,
    sc_cd1,
    coll,
    sc_cd2,
    bill_mas,
   sc_cd3,
   wk_sal,
   check_bill_month,
   check_sale_wk
    ) 

Run your .ctl file in sql plus with use of below commands

使用以下命令在 sql plus 中运行 .ctl 文件

sqlldr schema_name/password@databasename control=your control file path.

If any error occurs while loading data into table those will logged into .log file. For learn more about sqlloader Refer http://docs.oracle.com/cd/B10501_01/server.920/a96652/ch05.htm

如果在将数据加载到表中时发生任何错误，这些错误将记录到 .log 文件中。有关 sqlloader 的更多信息，请参阅http://docs.oracle.com/cd/B10501_01/server.920/a96652/ch05.htm

回答by Biswabid

Try creating Insert query in Excel(if data is not that huge):
like :
in AA1 cell write : 'INSERT INTO <TABLE> VALUES('
then respective values from different cells(i.e. 'A1,'+'B1,'...+')')
it will create Insert scripts in excel. Just copy the scripts and execute.

these are sample queries, plz write your own queries according to your requirement for INSERT STATEMENT.

尝试在 Excel 中创建插入查询（如果数据不是那么大）：
例如：
在 AA1 单元格中写入：'INSERT INTO <TABLE> VALUES('
然后来自不同单元格的相应值（即 'A1,'+'B1,'...+')'）
它将创建在excel中插入脚本。只需复制脚本并执行即可。

这些是示例查询，请根据您对 INSERT STATEMENT 的要求编写您自己的查询。

回答by MarioAna

You can use sql loader, but if you want a more robust solution (especially if the data will be modified directly in the spreadsheet) then look into external tables in Oracle. You can build a database table directly from the spreadsheet, the code looks like this:

您可以使用 sql 加载器，但如果您想要一个更强大的解决方案（尤其是如果数据将直接在电子表格中修改），那么请查看 Oracle 中的外部表。您可以直接从电子表格中构建数据库表，代码如下所示：

CREATE TABLE YOUR_SCHEMA.YOUR_TABLE
(
  ACCT_NO                   VARCHAR2(15 BYTE),
  PRODUCT_TYPE              VARCHAR2(50 BYTE),
  ORGINATION_DATE           VARCHAR2(20 BYTE),
  MATURITY_DATE             VARCHAR2(20 BYTE),
  FIXED_PERIOD_START_DATE   VARCHAR2(30 BYTE),
  FIXED_PERIOD_END_DATE     VARCHAR2(30 BYTE),
  ORIGINAL_LOAN_AMOUNT      NUMBER,
  CURRENT_BALANCE           NUMBER
)
ORGANIZATION EXTERNAL
  (  TYPE ORACLE_LOADER
     DEFAULT DIRECTORY YOUR_DIRECTORY
     ACCESS PARAMETERS 
       ( RECORDS DELIMITED  BY NEWLINE
         LOGFILE YOUR_DIRECTORY:'your_log.log'
         BADFILE YOUR_DIRECTORY:'your_bad.bad'
         FIELDS TERMINATED BY ','
         MISSING FIELD VALUES ARE NULL )
     LOCATION (YOUR_DIRECTORY:'your_file.csv')
  )

Note that anything with the word "your" in it above must be changed to whatever you want to name your files and directories. Also note that "YOUR_DIRECTORY" is an actual database object you must create:

请注意，上面带有“您的”一词的任何内容都必须更改为您要命名的文件和目录。另请注意，“YOUR_DIRECTORY”是您必须创建的实际数据库对象：

CREATE OR REPLACE DIRECTORY YOUR_DIRECTORY AS 'C:\workspaces\test\XL2ExternalTables'

回答by Rogger296

Please refer for the answer. https://community.oracle.com/thread/2166713?start=0

请参考答案。https://community.oracle.com/thread/2166713?start=0

I am just copying the approach what worked for me from above link

我只是从上面的链接复制对我有用的方法

In Oracle sql developer go to Tables --> select import data ---> select your excel or csv file --> it will display the column --> import the data into a table.

在 Oracle sql 开发人员中，转到表 --> 选择导入数据 ---> 选择您的 excel 或 csv 文件 --> 它将显示列 --> 将数据导入表中。

回答by user3792741

I have a query, I already have a table ABC and I deleted few rows from it, but before that I took export of rows I deleted in excel sheet. Now I need to insert those records back into the table. Since record count is more, I cannot write Insert statement for all. So is there any way, by which I can import the data back into table using excel sheet. Thanks, Abhishek

我有一个查询，我已经有一个表 ABC，我从中删除了几行，但在此之前，我导出了在 Excel 表中删除的行。现在我需要将这些记录插入回表中。由于记录数更多，我无法为所有人编写 Insert 语句。那么有什么方法可以使用excel表将数据导入回表中。谢谢，阿布舍克