在Oracle中使用SQL LOADER导入CSV文件 [英] Using SQL LOADER in Oracle to import CSV file

问题描述

我对数据库和编程非常陌生.我对计算机术语不是很好，所以请坚持.我有一个csv文件，我正在尝试将其加载到我的Oracle数据库中.它包含帐户信息，例如姓名，电话号码，服务日期等.我已经安装了Oracle 11g Release 2.到目前为止，这是我逐步完成的工作.

I'm pretty new to databases and programming. I'm not very good with the computer lingo so stick with me. I have a csv file that I'm trying to load into my Oracle database. It contains account information such as name, telephone number, service dates etc. I've installed Oracle 11g Release 2. This is what I've done so far step by step..

1)运行SQL Loader

1) Ran SQL Loader

我用所需的列创建了一个新表.例如

I created a new table with the columns that I needed. For example

create table Billing ( TAP_ID char(10), ACCT_NUM char(10), MR_ID char(10), HOUSE_NUM char(10), STREET char(30), NAME char(50)

2)它提示我该表已创建.接下来，我为记事本中的数据创建了一个控制文件，该文件位于与Billing表相同的目录中，并具有.ctl扩展名. GIS.csv是从中获取数据的文件，并且在同一目录中，并将其命名为Billing.ctl，看起来像这样.

2) It prompted me that the Table was created. Next I created a control file for the data in notepad which was located in the same directory as my Billing table and has a .ctl extension. GIS.csv is the file im getting the data from and is also in the same directory and named it Billing.ctl, which looked like so..

load data
infile GIS.csv
into table Billing
fields terminated by ','
(TAP_ID, ACCT_NUM, MR_ID, HOUSE_NUM, STREET, NAME)

3)从命令行运行sqlldr以使用控制文件

3) Run sqlldr from command line to use the control file

sqlldr myusername/mypassword  Billing.ctl

这就是我被困住的地方.我看过关于我正在做什么的视频教程，但出现此错误:

This is where I am stuck. Ive seen video tutorials of exactly what I'm doing but I get this error:

SQL*Loader-522: lfiopn failed for file (Billing.log)

关于我在这里可能做错什么的任何想法?

Any ideas on what I could be doing wrong here?

更新

我只是将文件移动到一个单独的目录中，并且我想摆脱了先前的错误. 顺便说一句，Billing.ctl和GIS.csv在同一目录中.

I just moved the files into a separate directory and I suppose I got past the previous error. By the way yes Billing.ctl and GIS.csv are in the same directory.

但是现在我有另一个错误:

But now I have another error:

'SQL*Loader-350: Syntax error at line 1.

期望关键字LOAD，找到"SERV TAP ID". "SERV TAP ID"，"ACCT号码"，"MTR ID"，"SERV HOUSE"，"SERV STREET"，"SERV ^'

Expecting keyword LOAD, found "SERV TAP ID". "SERV TAP ID","ACCT NUMBER","MTR ID","SERV HOUSE","SERV STREET","SERV ^'

我不明白为什么会出现该错误.我的billing.ctl负载很大.

I dont understand why its coming up with that error. My billing.ctl has a load.

LOAD data
infile GIS.csv
into table Billing
fields terminated by ','
(TAP_ID, ACCT_NUM, MTR_ID, SERV_HOUSE, SERV_STREET, SERV_TOWN, BIL_NAME, MTR_DATE_SET, BIL_PHONE, MTR_SIZE, BILL_CYCLE, MTR_RMT_ID)

有什么想法吗?

推荐答案

Sqlldr希望在控制文件所在的目录中写入一个日志文件.但是显然不能.它可能没有所需的权限.

Sqlldr wants to write a log file in the same directory where the control file is. But obviously it can't. It probably doesn't have the required permission.

如果您使用的是Linux或Unix，请尝试以与运行sqldr相同的方式运行以下命令:

If you're on Linux or Unix, try to run the following command the same way you run sqldr:

touch Billing.log

它将显示您是否具有权限.

It will show whether you have the permissions.

更新

正确的命令行是:

sqlldr myusername/mypassword control=Billing.ctl

这篇关于在Oracle中使用SQL LOADER导入CSV文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！