Oracle：导入 CSV 文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6198863/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle: Import CSV file
提问by cr8ivecodesmith
I've been searching for a while now but can't seem to find answers so here goes...
我一直在寻找一段时间，但似乎无法找到答案，所以这里是...
I've got a CSV file that I want to import into a table in Oracle (9i/10i).
我有一个 CSV 文件，我想将它导入到 Oracle (9i/10i) 中的表中。
Later on I plan to use this table as a lookup for another use.
稍后我计划将此表用作其他用途的查找。
This is actually a workaround I'm working on since the fact that querying using the IN clause with more that 1000 values is not possible.
这实际上是我正在研究的一种解决方法，因为使用具有超过 1000 个值的 IN 子句进行查询是不可能的。
How is this done using SQLPLUS?
这是如何使用 SQLPLUS 完成的？
Thanks for your time! :)
谢谢你的时间！:)
采纳答案by Abi
SQL Loaderhelps load csv files into tables: SQL*Loader
SQL Loader帮助将 csv 文件加载到表中：SQL*Loader
If you want sqlplus only, then it gets a bit complicated. You need to locate your sqlloader script and csv file, then run the sqlldr command.
如果你只想要 sqlplus，那么它会变得有点复杂。您需要找到 sqlloader 脚本和 csv 文件，然后运行 sqlldr 命令。
回答by Clarkey
Another solution you can use is SQL Developer.
您可以使用的另一个解决方案是 SQL Developer。
With it, you have the ability to import from a csv file (other delimited files are available).
有了它，您就可以从 csv 文件导入（其他分隔文件可用）。
Just open the table view, then:
只需打开表格视图，然后：
- choose actions
- import data
- find your file
- choose your options.
- 选择行动
- 导入数据
- 找到你的文件
- 选择您的选项。
You have the option to have SQL Developer do the inserts for you, create an sql insert script, or create the data for a SQL Loader script (have not tried this option myself).
您可以选择让 SQL Developer 为您执行插入操作、创建 sql 插入脚本或为 SQL Loader 脚本创建数据（我自己没有尝试过此选项）。
Of course all that is moot if you can only use the command line, but if you are able to test it with SQL Developer locally, you can always deploy the generated insert scripts (for example).
当然，如果您只能使用命令行，那么所有这些都没有实际意义，但是如果您能够在本地使用 SQL Developer 对其进行测试，那么您始终可以部署生成的插入脚本（例如）。
Just adding another option to the 2 already very good answers.
只需在 2 个已经非常好的答案中添加另一个选项。
回答by TTT
An alternative solution is using an external table: http://www.orafaq.com/node/848
另一种解决方案是使用外部表：http: //www.orafaq.com/node/848
Use this when you have to do this import very often and very fast.
当您必须非常频繁且非常快速地执行此导入时，请使用此选项。
回答by arjun gaur
SQL Loader is the way to go. I recently loaded my table from a csv file,new to this concept,would like to share an example.
SQL 加载程序是要走的路。我最近从一个 csv 文件加载了我的表格，这个概念的新手，想分享一个例子。
LOAD DATA
infile '/ipoapplication/utl_file/LBR_HE_Mar16.csv'
REPLACE
INTO TABLE LOAN_BALANCE_MASTER_INT
fields terminated by ',' optionally enclosed by '"'
(
ACCOUNT_NO,
CUSTOMER_NAME,
LIMIT,
REGION
)
Place the control file and csv at the same location on the server. Locate the sqlldr exe and invoce it.
将控制文件和 csv 放在服务器上的同一位置。找到 sqlldr exe 并调用它。
sqlldr userid/passwd@DBname control= Ex : sqlldr abc/xyz@ora control=load.ctl
sqlldr userid/passwd@DBname control= 例如：sqlldr abc/xyz@ora control=load.ctl
Hope it helps.
希望能帮助到你。
回答by Michael McLaughlin
Somebody asked me to post a link to the framework! that I presented at Open World 2012. This is the full blog post that demonstrates how to architect a solution with external tables.
有人让我发布一个框架的链接！我在 2012 年开放世界大会上发表的。这是完整的博客文章，演示了如何使用外部表构建解决方案。
回答by Lukasz Szozda
From Oracle 18cyou could use Inline External Tables:
从Oracle 18c你可以使用内联外部表：
Inline external tables enable the runtime definition of an external table as part of a SQL statement, without creating the external table as persistent object in the data dictionary.
With inline external tables, the same syntax that is used to create an external table with a CREATE TABLE statement can be used in a SELECT statement at runtime. Specify inline external tables in the FROM clause of a query block. Queries that include inline external tables can also include regular tables for joins, aggregation, and so on.
内联外部表支持在运行时将外部表定义为 SQL 语句的一部分，而无需将外部表创建为数据字典中的持久对象。
对于内联外部表，可以在运行时在 SELECT 语句中使用用于使用 CREATE TABLE 语句创建外部表的相同语法。在查询块的 FROM 子句中指定内联外部表。包括内联外部表的查询还可以包括用于连接、聚合等的常规表。
INSERT INTO target_table(time_id, prod_id, quantity_sold, amount_sold)
SELECT time_id, prod_id, quantity_sold, amount_sold
FROM EXTERNAL (
(time_id DATE NOT NULL,
prod_id INTEGER NOT NULL,
quantity_sold NUMBER(10,2),
amount_sold NUMBER(10,2))
TYPE ORACLE_LOADER
DEFAULT DIRECTORY data_dir1
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY '|')
LOCATION ('sales_9.csv') REJECT LIMIT UNLIMITED) sales_external;
