OraFAQ Forum: Server Utilities » Flat file Tab Delimeter import Messed up with SQL Loader !!

Home » RDBMS Server » Server Utilities » Flat file Tab Delimeter import Messed up with SQL Loader !! (Sql Loader wizard through Toad for Oracle 10.0.1.1)

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

Flat file Tab Delimeter import Messed up with SQL Loader !! [message #538226]

Wed, 04 January 2012 14:07

rkrishna4774
Messages: 4
Registered: January 2012
Location: Wheeling IL

Junior Member

Hi Guys,

Iam trying to import data from below content.

Flat File

PARENT	CHILD	ALIAS
PLAN_PCOT	Default	Planning Customer	
1001_BTPCOT	Default	General Planning Customer	
2000_BTPCOT	Default	National Account Planning Customer	
3000_BTPCOT	Default	Distributor Planning Customer	
3010_BTPCOT	Default	Education Planning Customer	
3020_BTPCOT	Default	Research Planning Customer	
OPT1_PCOT	Default	Option 1 Planning customer	
OPT2_PCOT	Default	Option 2 Planning customer	
OPT3_PCOT	Default	Option 3 Planning customer

The problem here is , When you try to import to a table which has same columns . I skipped the first line when loading .
The issue here is the second field is getting split in to the two columns . for eg :- DEfault goes to Child and Remaining goes to the Alias.

infact there is a tab at the end of the each line.

Pls help me when how to set the Sql loader settings correctly so that I can populated the end column in CHILD column only.!!!!

OPTIONS ( SKIP=1)
LOAD DATA
INFILE 'FlatFile.txt'
BADFILE ''FlatFile.bad'
DISCARDFILE ''FlatFile.dsc'

INTO TABLE "table"
FIELDS TERMINATED BY X'9'
OPTIONALLY ENCLOSED BY "''" TRAILING NULLCOLS
(PARENT,
CHILD,
ALIAS CONSTANT '')

[mod-edit: code tags added by bb; next time please add them yourself]

Attachment: FlatFile.txt
(Size: 0.44KB, Downloaded 1854 times)

[Updated on: Wed, 04 January 2012 20:07] by Moderator

Report message to a moderator

Re: Flat file Tab Delimeter import Messed up with SQL Loader !! [message #538235 is a reply to message #538226]

Wed, 04 January 2012 15:39

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

Senior Member

Looks like the correct behavior to me.

first value goes to parent
next value goes to child
next value goes to alias
tab between first and second field
tab between second and third field
no more tabs
3 columns

what do you think is supposed to happen?

Report message to a moderator

Re: Flat file Tab Delimeter import Messed up with SQL Loader !! [message #538238 is a reply to message #538235]

Wed, 04 January 2012 16:19

rkrishna4774
Messages: 4
Registered: January 2012
Location: Wheeling IL

Junior Member

First of all thank you for responding ,

What you said is right , i am also with you on that but it seems i did not make it clear.

In the flat file i attached, the first line is header as we know. It has three values

from 2nd line to last line , each line contians only two values, so first column datavalue goes to parent and second should go to child and nothing to Alias.

But when i try to load it with sql loader, its doing like below

the second data value is getting split and its going to child and alias columns.

is it someting wrong with the flat file !! or something with sql loader behaviour.

Hope i made it clear.

Ravi.

Report message to a moderator

Re: Flat file Tab Delimeter import Messed up with SQL Loader !! [message #538240 is a reply to message #538238]

Wed, 04 January 2012 16:23

rkrishna4774
Messages: 4
Registered: January 2012
Location: Wheeling IL

Junior Member

for eg :- PLAN_PCOT Default Planning Customer

Here PLAN_PCOT goes to Parent.
Default Planning Customer goes to Child
Nothing should go to Alias
but "Default Planning Customer goes " is getting split and is going to child and Alias columns !!!!! through SQL loader from Toad for Oracle wizard.

Report message to a moderator

Re: Flat file Tab Delimeter import Messed up with SQL Loader !! [message #538243 is a reply to message #538240]

Wed, 04 January 2012 20:47

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

Senior Member

I assume that you typed your control file, instead of copying and pasting, because it has multiple errors that would prevent it from loading anything.

In your data file, although it looks like a space, if you copy and paste it into an ascii function, you can see that there is actually a tab after each "Default", so the data is loading as expected. If this is not what you want then you can load the data into two filler fields and concatenate them, as demonstrated below.

-- 'FlatFile.txt':

PARENT	CHILD	ALIAS
PLAN_PCOT	Default	Planning Customer
1001_BTPCOT	Default	General Planning Customer
2000_BTPCOT	Default	National Account Planning Customer
3000_BTPCOT	Default	Distributor Planning Customer
3010_BTPCOT	Default	Education Planning Customer
3020_BTPCOT	Default	Research Planning Customer
OPT1_PCOT	Default	Option 1 Planning customer
OPT2_PCOT	Default	Option 2 Planning customer
OPT3_PCOT	Default	Option 3 Planning customer

-- test.ctl:

OPTIONS ( SKIP=1)
LOAD DATA
INFILE 'FlatFile.txt'
BADFILE 'FlatFile.bad'
DISCARDFILE 'FlatFile.dsc'
INTO TABLE "table"
FIELDS TERMINATED BY X'9'
OPTIONALLY ENCLOSED BY "''"
TRAILING NULLCOLS
(PARENT,
filler1 BOUNDFILLER,
filler2 BOUNDFILLER,
CHILD ":filler1 || :filler2",
ALIAS CONSTANT '')

-- table:

SCOTT@orcl_11gR2> create table "table"
  2    (parent	varchar2(11),
  3  	child	varchar2(42),
  4  	alias	varchar2( 5))
  5  /

Table created.

-- load:

SCOTT@orcl_11gR2> host sqlldr scott/tiger control=test.ctl log=test.log

-- results:

SCOTT@orcl_11gR2> select * from "table"
  2  /

PARENT      CHILD                                      ALIAS
----------- ------------------------------------------ -----
PLAN_PCOT   DefaultPlanning Customer
1001_BTPCOT DefaultGeneral Planning Customer
2000_BTPCOT DefaultNational Account Planning Customer
3000_BTPCOT DefaultDistributor Planning Customer
3010_BTPCOT DefaultEducation Planning Customer
3020_BTPCOT DefaultResearch Planning Customer
OPT1_PCOT   DefaultOption 1 Planning customer
OPT2_PCOT   DefaultOption 2 Planning customer
OPT3_PCOT   DefaultOption 3 Planning customer

9 rows selected.

Report message to a moderator

Re: Flat file Tab Delimeter import Messed up with SQL Loader !! [message #538398 is a reply to message #538226]

Thu, 05 January 2012 14:08

rkrishna4774
Messages: 4
Registered: January 2012
Location: Wheeling IL

Junior Member

Hi Barbara,

Thank you very much for your solution which worked for me .

I have one more situation.
For Eg :- When you open the same files in Notepad and see sometimes the tab only moves to one space and sometimes tab moves cursor to 8 spaces or default no: . Your solution can overcome these scenarios.

Col1 Col2 Col3 Col4 Col5
Example Record:-
hello krishna how are you.

If you observe there is a tab between krishna and how with only one space, these are the scenarios which i dont want to deal with SQL loader to treat like space or sometimes tab.

I have 3 more files which have tabs and i realized the tabs and spaces dont go well when we are loading the data with SQL loader.

So i requested my Flatfile source to use Pipe or ~ as delimiters , now i have no issues with loading .

Thank you for your time and solution.

BTW , Happy New Year to you and the guys at this forum,
rkrishna4774

Report message to a moderator

Previous Topic:	export fails
Next Topic:	Schema export in command prompt mode

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Dec 23 06:53:35 CST 2024