database 使用sql将数据从文件csv导入Oracle
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/35646883/
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
import data from file csv to Oracle using sql
提问by Meher Jebali
I'm using Oracle 10g , SQL Developer I want to know if there is any way to import data from CSV file , which the fields separated by ',' and the lines terminated by '\n' using sql query i tried this query
我正在使用 Oracle 10g ，SQL Developer 我想知道是否有任何方法可以从 CSV 文件中导入数据，其中的字段用 ',' 分隔，行使用 sql 查询以 '\n' 终止我试过这个查询
LOAD DATA INFILE 'C:/tmp.csv' INTO TABLE CSVTEST2 FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 lines (ID,FIRSTNAME,LASTNAME,BIRTHDATE);
LOAD DATA INFILE 'C:/tmp.csv' INTO TABLE CSVTEST2 FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 lines (ID,FIRSTNAME,LASTNAME,BIRTHDATE);
But it didn't work and i always get error message from sql Developer telling me Unknow command
但它不起作用，我总是从 sql Developer 收到错误消息，告诉我Unknow 命令
回答by Sandeep
You need to use sqlldr utility in order to load data. Firstly create a control file (ends with an extension of .ctl) as per your requirements like mentioned below.
您需要使用 sqlldr 实用程序才能加载数据。首先根据您的要求创建一个控制文件（以 .ctl 的扩展名结尾），如下所述。
load data
infile 'path_where_file_is_placed_followed_by_file_name'
into table table_name_where_you_want_to_insert_the_data_of_csv_file
fields terminated by ',' lines terminated by '\n'
(
field1 datatype,
field2 datatype,
field3 datatype
)
Now execute sqlldr utility to load data as mentioned below.
现在执行 sqlldr 实用程序来加载数据，如下所述。
sqlldr userid=database_username/password@instance_name control=path_where_control_file_is_placed_followed_by_control_file_name LOG=path_for_log_file BAD=path_for_bad_records Discard=path_for_discard_records
回答by MaxU
You should be using Oracle SQL Loaderfor that, notsqlplus or SQL Developer
您应该为此使用Oracle SQL Loader，而不是sqlplus 或 SQL Developer
Alternatively you can use external tables:
或者，您可以使用外部表：
-- this command must be executed on the Oracle server machine, NOT on the client:
create directory ext_tab_dir as '/path/to/dir/where/you/will/put/your/csv/files';
CREATE TABLE emp_load (
employee_number CHAR(5),
employee_last_name CHAR(20),
employee_first_name CHAR(15),
employee_middle_name CHAR(15)
)
ORGANIZATION EXTERNAL (
TYPE ORACLE_LOADER
DEFAULT DIRECTORY ext_tab_dir
ACCESS PARAMETERS (RECORDS FIXED 62
FIELDS (
employee_number CHAR(2),
employee_dob CHAR(20),
employee_last_name CHAR(18),
employee_first_name CHAR(11),
employee_middle_name CHAR(11)
)
)
LOCATION ('tmp.csv'));
回答by Jorge T
Example on windows 10 and Oracle 12c
Windows 10 和 Oracle 12c 上的示例
if you have a text file with records of each table delimited by comma, you can do this:
如果你有一个文本文件，每个表的记录用逗号分隔，你可以这样做：
Create a control file for each table, called table_name.ctl (C:\Users\user\Desktop\directory\table_name.ctl)
为每个表创建一个控制文件，名为 table_name.ctl (C:\Users\user\Desktop\directory\table_name.ctl)
load data
infile 'C:\Users\user\Desktop\directory\table_name.txt'
append
into table table_name
fields terminated by ","
(id, field2,field3)
After, In windows you should open Cmd and load data in each table, and then load data remotely for example in a aws server.
之后，在 Windows 中，您应该打开 Cmd 并在每个表中加载数据，然后远程加载数据，例如在 aws 服务器中。
sqlldr userid=USER@AWS_PDB1/password
control='C:\Users\user\Desktop\directory\table_name.ctl' log='C:\Users\user\Desktop\directory\table_name.log'
or
或者
sqlldr control='C:\Users\user\Desktop\directory\table_name.ctl' log='C:\Users\user\Desktop\directory\table_name.log'
and then ask them the user and password
If you have the following error:“The program can't start because oranfsodm12.dll is missing from your computer. Try reinstalling the program to fix this problem.”
如果您遇到以下错误：“程序无法启动，因为您的计算机缺少 oranfsodm12.dll。尝试重新安装程序以解决此问题。”
it is because SQL * Loader is disabled and can not be used in the console windows, this is solved enabling the following steps (as http://www.dallasmarks.com/installing-two-oracle-12c-clients-on-one-server/):
这是因为 SQL * Loader 被禁用，无法在控制台窗口中使用，这是通过启用以下步骤解决的（如http://www.dallasmarks.com/installing-two-oracle-12c-clients-on-one -服务器/）：
Should go to the folder C:\oracle\client\user\product\12.1.0\client_1\BIN
Make a copy of oraodm12.dll file, calling the new file oranfsodm12.dll, and paste it in the same BIN folder.
Run the command again from cmd.
应该转到文件夹 C:\oracle\client\user\product\12.1.0\client_1\BIN
复制oraodm12.dll文件，调用新文件oranfsodm12.dll，粘贴到同一个BIN文件夹中。
从 cmd 再次运行该命令。
回答by Raunak Thomas
In Oracle SQL developer you can simply go to the list of tables on the left which should be under connections.
在 Oracle SQL developer 中，您只需转到左侧的表列表，该列表应位于connections.
Then Double click the table > actions > import data
然后双击表>操作>导入数据
You can then use the import wizard with your desired settings. I'm not sure if the feature was present in Oracle SQL developer when you asked this question, but it is available now.
然后，您可以使用具有所需设置的导入向导。当您问这个问题时，我不确定该功能是否存在于 Oracle SQL 开发人员中，但它现在可用。
