Home » RDBMS Server » Server Utilities » Loader loading some garbage characters at the end of the data field (Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production)
Loader loading some garbage characters at the end of the data field [message #501096] Fri, 25 March 2011 15:10 Go to next message
rappaj
Messages: 147
Registered: June 2010
Location: NYC
Senior Member
Hi,

After exporting some data to excel, I noticed that on one row all the columns shifted over some. So I queried this record in the database and noticed that the ADDRESS field has some unknown characters at the end of it. They are little squares. I think they are TABS.

2630 LINDEN BLVD, APT. #8G(2 squares are in here)

ADDRESS_1 "TRIM(:ADDRESS_1)",

Besides trimming the data, is there some other function I can use to clean up the address further?


Thanks,
Joe

Re: Loader loading some garbage characters at the end of the data field [message #501097 is a reply to message #501096] Fri, 25 March 2011 15:16 Go to previous messageGo to next message
BlackSwan
Messages: 26757
Registered: January 2009
Location: SoCal
Senior Member
>Loader loading some garbage characters at the end of the data field
You got the content of the data field. The loader did its job correctly.
Garbage In, Garbage Out (GIGO)!
Rather than fight the symptoms, you'd be better off ensuring no "garbage characters" are allowed in the DB.

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
Re: Loader loading some garbage characters at the end of the data field [message #501098 is a reply to message #501097] Fri, 25 March 2011 15:21 Go to previous messageGo to next message
rappaj
Messages: 147
Registered: June 2010
Location: NYC
Senior Member
Ok, thanks. I'll see what I can find out there in google WORLD.

Joe
Re: Loader loading some garbage characters at the end of the data field [message #501102 is a reply to message #501098] Fri, 25 March 2011 15:40 Go to previous messageGo to next message
Michel Cadot
Messages: 67569
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
They are little squares. I think they are TABS.

Use the DUMP funtion after the load to know what they are then you can use TRIM.
Or you can use an hexadecimal editor on the source file.

Regards
Michel
Re: Loader loading some garbage characters at the end of the data field [message #502628 is a reply to message #501102] Thu, 07 April 2011 11:20 Go to previous messageGo to next message
rappaj
Messages: 147
Registered: June 2010
Location: NYC
Senior Member
Hi all,

Just letting you know that the garbage character that was in the input file that I loaded were 2 TABS.

So right from the initial load in the ctl file, I used the replace function. I know that it would only work for TABS, but that seems to be the only type of garbage that I've been getting....hopefully

ADDRESS_1                "trim(replace (:address_1, chr(9),' '))",


Thanks,
Joe
Re: Loader loading some garbage characters at the end of the data field [message #502632 is a reply to message #502628] Thu, 07 April 2011 12:29 Go to previous messageGo to next message
Michel Cadot
Messages: 67569
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
rtrim(:address1, chr(9))

Regards
Michel
Re: Loader loading some garbage characters at the end of the data field [message #502633 is a reply to message #502632] Thu, 07 April 2011 13:06 Go to previous messageGo to next message
rappaj
Messages: 147
Registered: June 2010
Location: NYC
Senior Member
Hi Michel,

I wanted to try your version too, so I reloaded the data with the garbage tabs, but the rtrim(:address1, chr(9)) did not work. The data loaded with the garbage. Even when I used it in SQL, the data still returns garbage.

No need to go crazy with this if you don't want to........the REPLACE works.

The attached snapshot shows this version used...

Thank you
Re: Loader loading some garbage characters at the end of the data field [message #502634 is a reply to message #502633] Thu, 07 April 2011 13:10 Go to previous messageGo to next message
rappaj
Messages: 147
Registered: June 2010
Location: NYC
Senior Member
P.S. Sorry, the attachment didn't load because it was a bmp file. Here is the jpg version.

Can't see it still. Not working out to well here....but you get my drift.
  • Attachment: gigo2.JPG
    (Size: 61.28KB, Downloaded 263 times)

[Updated on: Thu, 07 April 2011 13:12]

Report message to a moderator

Re: Loader loading some garbage characters at the end of the data field [message #502799 is a reply to message #502634] Sat, 09 April 2011 23:57 Go to previous messageGo to next message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member

Is it character set issue ?
Re: Loader loading some garbage characters at the end of the data field [message #502804 is a reply to message #502634] Sun, 10 April 2011 00:40 Go to previous message
Michel Cadot
Messages: 67569
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So your garbage characters are not tab (at least the last one).

select address_1, dump(address_1) from ...

Use SQL*Plus then you will be able to post in text inside the post and have no more problem to load an image.

Regards
Michel
Previous Topic: I have a one question regarding external table use in oracle?
Next Topic: importing error
Goto Forum:
  


Current Time: Thu Dec 03 22:32:58 CST 2020