OraFAQ Forum: Server Utilities » Excel Spreadsheet - SQLLDR

Home » RDBMS Server » Server Utilities » Excel Spreadsheet - SQLLDR - New Line Issues (Oracle 10)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Excel Spreadsheet - SQLLDR - New Line Issues [message #527183]

Sun, 16 October 2011 09:24

carolmarol123
Messages: 11
Registered: October 2011

Junior Member

Hi,

I am trying to load a .csv file using SQLLDR. My control file is perfectly fine. The file has approximately 80K rows, but sqlldr loads 9206 records, then just stops... no error message whatsoever.

I suspect there must be "NEW LINES" in the file? Does anyone know how I can massage my input file to make it "clean" for sqlldr?

(I am currently loading it via TOAD which will take about 6 hours)....I'd rather not have to spend all Sunday doing this! And at the same time, I need to figure out how to load these types of files using sqlldr (i.e, this is an ongoing requirement of my job.)

Thanks!

Report message to a moderator

Re: Excel Spreadsheet - SQLLDR - New Line Issues [message #527184 is a reply to message #527183]

Sun, 16 October 2011 09:35

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

>I suspect there must be "NEW LINES" in the file?
I suspect that you simply guessing & are more likely to be wrong as correct.

post first 20 & last 20 lines from log file

consider using EXTERNAL TABLE instead

Report message to a moderator

Re: Excel Spreadsheet - SQLLDR - New Line Issues [message #527191 is a reply to message #527184]

Sun, 16 October 2011 12:30

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Using TOAD to do SQL*Loader's job is stupid (OK, politically correct would be "is wrong"). From my experience, the fact that TOAD offers such a functionality doesn't mean you must use it.

Obviously, you already have SQL*Loader installed. Simply invoke it from your operating system's command prompt and see what happens. 80.000 of records should take minutes to load, not hours. Here's how it might go:

> sqlldr scott/tiger@ora10 control=load_me.ctl log=load_me.log

Report message to a moderator

Re: Excel Spreadsheet - SQLLDR - New Line Issues [message #527192 is a reply to message #527191]

Sun, 16 October 2011 13:26

carolmarol123
Messages: 11
Registered: October 2011

Junior Member

OK.

One:

I agree 100% that using Toad to do ANY type of batch processing is completely ridiculous!!!

Two:
I've invoked sqlldr from the unix command prompt for goodness sakes... The job simply stops after loading 9206 records, no errors -- or it's possible that I am not seeing the errors as I do not have a log file specified, but am simply looking at the screen output.

Here is my control file:

load data
infile '/home/oracle/input.csv'
into table toad.ttp43425_load
fields terminated by "," optionally enclosed by '"'
(ACCTNUM, METERNUM, ERTNUM, PREMNUM, PREMADDR, CURCYCLE, CURROUTE, NEWCYCLE, NEWROUTE)

I agree 100% that using Toad to do ANY type of batch processing is completely ridiculous!!!

Report message to a moderator

Re: Excel Spreadsheet - SQLLDR - New Line Issues [message #527193 is a reply to message #527192]

Sun, 16 October 2011 13:32

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

Is COPY & PASTE broken for you?

>I do not have a log file specified
PLEASE specify log file!

post first 20 & last 20 lines from log file

Report message to a moderator

Re: Excel Spreadsheet - SQLLDR - New Line Issues [message #527194 is a reply to message #527193]

Sun, 16 October 2011 13:36

carolmarol123
Messages: 11
Registered: October 2011

Junior Member

Apparently, COPY and PASTE is broken for me, as is COURTESY for you. Good day. I won't waste my time on this forum anymore with your rude BS.

Possible problems and workarounds:

The spreadsheet may contain cells with newline characters (ALT+ENTER). SQL*Loader expects the entire record to be on a single line. Run the following macro to remove newline characters (Tools -> Macro -> Visual Basic Editor):

' Removing tabs and carriage returns from worksheet cells
Sub CleanUp()
Dim TheCell As Range
On Error Resume Next

For Each TheCell In ActiveSheet.UsedRange
With TheCell
If .HasFormula = False Then
.Value = Application.WorksheetFunction.Clean(.Value)
End If
End With
Next TheCell
End Sub

Report message to a moderator

Re: Excel Spreadsheet - SQLLDR - New Line Issues [message #527195 is a reply to message #527194]

Sun, 16 October 2011 14:09

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Quote:

SQL*Loader expects the entire record to be on a single line

Well, that's not true. See Assembling Logical Records from Physical Records in SQL*Loader documentation.

You should really specify LOG file, without it you (and we) are kind of blind. Maybe there were too many errors which made SQL*Loader quit. Is there a .BAD file, perhaps?

You should supply as many information as possible - someone might get the idea (for example, Barbara, who is a true expert).

If possible, post CREATE TABLE statement and attach several sample records so that we could try to load them into our databases. I understand that some information might be confidential, so it is entirely up to you.

Report message to a moderator

Re: Excel Spreadsheet - SQLLDR - New Line Issues [message #527196 is a reply to message #527194]

Sun, 16 October 2011 14:19

Barbara Boehmer
Messages: 9103
Registered: November 2002
Location: California, USA

Senior Member

If you specify a log file, then that log file would likely clearly show what the problem is, instead of guessing at it. That is the main purpose of the log file. Also, make sure you are not running it in silent mode, so that you can see what is happening as it commits blocks of records or stops due to some problem. If it loads 9206 rows successfully, then it is not likely due to something that all rows have, such as newlines. If you do have newlines mid-record, then you can either eliminate them before loading or use continueif within your SQL*Loader control file, or specify a record terminator. In order to use continueif or a record terminator there must be some character(s) that can be used to determine whether to continue or the record terminates. It sounds more likely that you have exceeded some default limit on something or hit some unusual error in some part of the file. The log file should show that. You might also try dividing your file up and loading sections to determine where the problem is. If each section loads separately but not together, then once again you are looking at a limit problem rather than a problem with the data and loading process. It helps if you provide your table structure, a few records of some sample data, and the resulting log file. There are all kinds of things that can be done to solve problems, but we need to know what the problem is first, by seeing what the log file says.

Report message to a moderator

Re: Excel Spreadsheet - SQLLDR - New Line Issues [message #527199 is a reply to message #527196]

Sun, 16 October 2011 15:08

carolmarol123
Messages: 11
Registered: October 2011

Junior Member

Thank you both for your time and input. I must admit that I haven't used sqlldr in years, so I'm a bit rusty and once I did specify a log file, I had a "Duh!" moment. With the log file, I immediately realized what the problem was. (The data was too large for the column precision). I adjusted it, and "Hey ho!" it loaded in about 10 seconds, versus Toad, which would have taken 6 hours. Unfortunately, in the company that I have just joined, the DBAs insist on using Toad for everything. Hopefully I can change that!!!

Thank you so much for your informational replies!

Carol

Report message to a moderator

Re: Excel Spreadsheet - SQLLDR - New Line Issues [message #527315 is a reply to message #527199]

Mon, 17 October 2011 08:28

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

carolmarol123 wrote on Sun, 16 October 2011 16:08

Unfortunately, in the company that I have just joined, the DBAs insist on using Toad for everything. Hopefully I can change that!!!

How that person got to be a DBA is beyond me. TOAD is just a lazy person's way to do a job. It hides commands and errors. Ask this person to solve any real problem and they will be shaking in their boots.

p.s. How were those years on "Let's Make A Deal?"

[Updated on: Mon, 17 October 2011 08:29]

Report message to a moderator

Previous Topic:	SQL*Loader: Error inserting number with U.S. formatting using modified CHARACTERSET (merged 2)
Next Topic:	impdp error

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Jan 26 10:43:12 CST 2025