OraFAQ Forum: Server Utilities » Problems Loading Data Into Table

Home » RDBMS Server » Server Utilities » Problems Loading Data Into Table - SQL Loader (11.2.0.2.0)

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

Problems Loading Data Into Table - SQL Loader [message #521616]

Wed, 31 August 2011 10:17

Mdubois
Messages: 3
Registered: August 2011

Junior Member

Hi,

I'm not sure if this is so much a SQL Loader problem as it is a database understanding problem, but here it is. I am having trouble loading data into a table (using SQL Loader) due to the fact that I am trying to load data row by row, into corresponding columns.

TestFile.csv

testvalue1, 123445
testvalue2, test
testvalue3, 455321
testvalue4, 65742
testvalue5, 5719

So, using the above data, I am trying to load the value for 'testvalue1' into a column defined as 'testvalue1'; the value for 'testvalue2' into a column defined as 'testvalue2' and so on. From my understanding, SQL loader loads by column not by row, so I am not even sure if this is possible.

I am fairly new to databases in general, so any advice would greatly be appreciated. Also my description of my problem may be a little hard to understand, and I will be more than happy to clarify if need be.

Thank You

Report message to a moderator

Re: Problems Loading Data Into Table - SQL Loader [message #521619 is a reply to message #521616]

Wed, 31 August 2011 10:26

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

when all else fails Read The Fine Manual as below

http://download.oracle.com/docs/cd/E11882_01/server.112/e16536/toc.htm

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

SQLLDR loads each record in the input data file into a row in the target table.

Report message to a moderator

Re: Problems Loading Data Into Table - SQL Loader [message #521638 is a reply to message #521619]

Wed, 31 August 2011 11:57

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

Senior Member

If the column names are consistent and you can add a comma at the end of each line and there are always five rows of text file per row of table, then you can use concatenate 5. Otherwise, if the column names are consistent and you can add a comma at the end of each line and each set always begins with a known value such as testvalue1, then you can use continueif, as demonstrated below.

-- testfile.csv:

testvalue1, 123445,
testvalue2, test,
testvalue3, 455321,
testvalue4, 65742,
testvalue5, 5719,
testvalue1, 54321,
testvalue2, test2,
testvalue3, 98765,
testvalue4, 87654,
testvalue1, 67890,
testvalue2, test3,
testvalue3, 98760,

-- test.ctl:

load data
infile testfile.csv
continueif next preserve (1:10) != 'testvalue1'
into table test_tab
fields terminated by ','
trailing nullcols
(col1 filler,
testvalue1,
col3 filler,
testvalue2,
col5 filler,
testvalue3,
col7 filler,
testvalue4,
col9 filler,
testvalue5)

-- table, load, and results:

SCOTT@orcl_11gR2> create table test_tab
  2    (testvalue1  varchar2 (10),
  3  	testvalue2  varchar2 (10),
  4  	testvalue3  varchar2 (10),
  5  	testvalue4  varchar2 (10),
  6  	testvalue5  varchar2 (10))
  7  /

Table created.

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

SCOTT@orcl_11gR2> select * from test_tab
  2  /

TESTVALUE1 TESTVALUE2 TESTVALUE3 TESTVALUE4 TESTVALUE5
---------- ---------- ---------- ---------- ----------
 123445     test       455321     65742      5719
 54321      test2      98765      87654
 67890      test3      98760

3 rows selected.

SCOTT@orcl_11gR2>

Report message to a moderator

Previous Topic:	query to check data pump
Next Topic:	Exclude badly formatted

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jan 10 22:02:09 CST 2025