使用sql将数据从文件csv导入到Oracle [英] import data from file csv to Oracle using sql

问题描述

我使用的是Oracle 10g，SQL Developer
我想知道是否有任何方法从CSV文件导入数据，其中字段由'，'分隔，行由'\\\
'使用sql查询
i尝试这个查询

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

LOAD DATA INFILE'C：/tmp.csv'INTO TABLE CSVTEST2 FIELDS TERMINATED BY'，'LINES TERMINATED BY'\\\
'IGNORE 1 lines（ID，FIRSTNAME，LASTNAME，BIRTHDATE）;

但它没有工作，我总是从sql开发人员的错误消息告诉我 Unknow命令

But it didn't work and i always get error message from sql Developer telling me Unknow command

推荐答案

您需要使用sqlldr实用程序来加载数据。首先根据您的要求创建一个控制文件（以.ctl的扩展名结尾），如下所述。

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.

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
)

现在执行sqlldr实用程序。

Now execute sqlldr utility to load data as mentioned below.

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

这篇关于使用sql将数据从文件csv导入到Oracle的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！