Sql Loader Import Csv File
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.
Sql Loader To Load Csv File In Oracle
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) Ran SQL LoaderI created a new table with the columns that I needed.
For example create table Billing ( TAPID char(10), ACCTNUM char(10), MRID char(10), HOUSENUM char(10), STREET char(30), NAME char(50)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 datainfile GIS.csvinto table Billingfields terminated by ','(TAPID, ACCTNUM, MRID, HOUSENUM, STREET, NAME)3) Run sqlldr from command line to use the control file sqlldr myusername/mypassword Billing.ctlThis 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?UpdateI 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.Expecting keyword LOAD, found 'SERV TAP ID'.' SERV TAP ID','ACCT NUMBER','MTR ID','SERV HOUSE','SERV STREET','SERV^'I dont understand why its coming up with that error. My billing.ctl has a load.
LOAD datainfile GIS.csvinto table Billingfields terminated by ','(TAPID, ACCTNUM, MTRID, SERVHOUSE, SERVSTREET, SERVTOWN, BILNAME, MTRDATESET, BILPHONE, MTRSIZE, BILLCYCLE, MTRRMTID)Any thoughts? I hade a csv file named FARTSNSA.csv that i wanted to import in oracle database directly. For this i have done the following steps and it worked absolutely fine. Here are the steps that u vand follow out:HOW TO IMPORT CSV FILE IN ORACLE DATABASE?.
Get a.csv format file that is to be imported in oracle database. Here this is named “FARTSNSA.csv”.Create a table in sql with same column name as there were in.csv file.create table Billing ( ioclid char(10), ioclconsumerid char(10));.Create a Control file that contains sql.loder script.In notepad type the script as below and save this with.ctl extension, in selecting file type as All Types(.). Here control file is named as Billing. And the Script is as Follows: LOAD DATAINFILE 'D:FARTSNSA.csv'INSERT INTO TABLE BillingFIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY 'TRAILING NULLCOLS(ioclid,ioclconsumerid).Now in Command prompt run command: Sqlldr UserId/PasswordControl = “ControlFileName”-Here ControlFileName is Billing.
Seems you got syntax error in your ctl file. The fact, that the sqlldr points to line 1 where i don't see any problem could mean, that you got your file written with unix newlines (carriage return) so that (windows version of) sqlldr see is just one line. (otherwise i see no problem there - but you newer know with oracle command line tools:-)) Anyway, try to check the way u execute the sqlldr command, i tend to use following way: sqlldr control=file.ctl data=file.dat bad=file.bad.–Jun 1 '12 at 13:24. If your text is:Joe said, 'Fred was here with his 'Wife'.This is saved in a CSV as:'Joe said, 'Fred was here with his 'Wife'.' (Rule is double quotes go around the whole field, and double quotes are converted to two double quotes).
So a simple Optionally Enclosed By clause is needed but not sufficient. CSVs are tough due to this rule. You can sometimes use a Replace clause in the loader for that field but depending on your data this may not be enough. Often pre-processing of a CSV is needed to load in Oracle.
Or save it as an XLS and use Oracle SQL Developer app to import to the table - great for one-time work, not so good for scripting.