Home » RDBMS Server » Server Utilities » Problems Loading Data Into Table - SQL Loader (11.2.0.2.0)
Problems Loading Data Into Table - SQL Loader [message #521616] Wed, 31 August 2011 10:17 Go to next message
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
Re: Problems Loading Data Into Table - SQL Loader [message #521619 is a reply to message #521616] Wed, 31 August 2011 10:26 Go to previous messageGo to next message
BlackSwan
Messages: 21978
Registered: January 2009
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.
Re: Problems Loading Data Into Table - SQL Loader [message #521638 is a reply to message #521619] Wed, 31 August 2011 11:57 Go to previous message
Barbara Boehmer
Messages: 7861
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> 

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


Current Time: Thu Apr 24 14:38:33 CDT 2014

Total time taken to generate the page: 0.11680 seconds