Home » RDBMS Server » Server Utilities » SQL Loader - Excel file different column order format (Oracle 11, Linux)
SQL Loader - Excel file different column order format [message #506347] Sun, 08 May 2011 18:54 Go to next message
nichollsvi
Messages: 8
Registered: May 2009
Location: Va.
Junior Member
Hi,

I have an Excel spreadsheet that just had a format change. The fourth column is new. Order of columns in Excel is:

oldcol1
oldcol2
oldcol3
oldcol4
newcol
oldcol5

Sqlldr script is in order of the .csv file just listed. Oracle table is in order

oldcol1
oldcol2
oldcol3
oldcol4
oldcol5
newcol

When I run the sqlldr script, the information is loaded:

oldcol1
oldcol2
oldcol3
oldcol4
newcol in oldcol5

so that all the information is loaded incorrectly. Out side of having to change the table, is there anything I can do to make it load correctly? Thanks,

Vic
Re: SQL Loader - Excel file different column order format [message #506348 is a reply to message #506347] Sun, 08 May 2011 19:06 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>so that all the information is loaded incorrectly. Out side of having to change the table, is there anything I can do to make it load correctly?

How can we reproduce and correct what you report?

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
Re: SQL Loader - Excel file different column order format [message #506349 is a reply to message #506348] Sun, 08 May 2011 19:11 Go to previous messageGo to next message
nichollsvi
Messages: 8
Registered: May 2009
Location: Va.
Junior Member
My apologies, I will post code tomorrow.

Vic
Re: SQL Loader - Excel file different column order format [message #506352 is a reply to message #506349] Sun, 08 May 2011 20:27 Go to previous messageGo to next message
ramoradba
Messages: 2456
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Is that a temp table or a permanent table you have inserted ?
Identify the rows that you inserted wrongly.
Just delete he rows and re insert in a correct order.

Sriram
Re: SQL Loader - Excel file different column order format [message #506460 is a reply to message #506347] Mon, 09 May 2011 06:50 Go to previous messageGo to next message
nichollsvi
Messages: 8
Registered: May 2009
Location: Va.
Junior Member
Here is the table code:

CREATE TABLE ENROLLMENT_TRACKING
(
ACADEMIC_PERIOD VARCHAR2(120 CHAR),
ACADEMIC_PERIOD_DESC VARCHAR2(120 CHAR),
LOAD_WEEK VARCHAR2(120 CHAR),
PERSON_UID VARCHAR2(120 CHAR),
SUBJECT VARCHAR2(120 CHAR),
COURSE_NUMBER VARCHAR2(120 CHAR),
COURSE_CREDITS VARCHAR2(120 CHAR),
COURSE_LEVEL VARCHAR2(120 CHAR),
COURSE_LEVEL_DESC VARCHAR2(120 CHAR),
COLLEGE VARCHAR2(120 CHAR),
COLLEGE_DESC VARCHAR2(120 CHAR),
CURRENT_DEPT VARCHAR2(120 CHAR),
CURRENT_DEPT_DESC VARCHAR2(120 CHAR),
COURSE_SITE VARCHAR2(120 CHAR),
COURSE_SITE_DESC VARCHAR2(120 CHAR),
CAMPUS_REGION VARCHAR2(120 CHAR),
COURSE_DIVISION VARCHAR2(120 CHAR),
DELIVERY_MODE VARCHAR2(120 CHAR),
DELIVERY_MODE_DESC VARCHAR2(120 CHAR),
SITE_AGGREGATE1 VARCHAR2(120 CHAR),
SITE_AGGREGATE2 VARCHAR2(120 CHAR),
STUDENT_SITE VARCHAR2(120 CHAR),
STUDENT_SITE_DESC VARCHAR2(120 CHAR),
STUDENT_LEVEL_FTE VARCHAR2(120 CHAR),
TUITION_STATUS VARCHAR2(120 CHAR),
FREEZE_DATE DATE,
SUB_ACADEMIC_PERIOD VARCHAR2(120 CHAR)
)

Here is the control file:

options (skip=1)
load data
append into table odu_enrollment_tracking fields terminated by "," trailing nullcols
(ACADEMIC_PERIOD,
ACADEMIC_PERIOD_DESC,
LOAD_WEEK,
SUB_ACADEMIC_PERIOD,
PERSON_UID,
SUBJECT,
COURSE_NUMBER,
COURSE_CREDITS,
COURSE_LEVEL,
COURSE_LEVEL_DESC,
COLLEGE,
COLLEGE_DESC,
CURRENT_DEPT,
CURRENT_DEPT_DESC,
COURSE_SITE,
COURSE_SITE_DESC,
CAMPUS_REGION,
COURSE_DIVISION,
DELIVERY_MODE,
DLIVERY_MODE_DESC,
SITE_AGGREGATE1,
SITE_AGGREGATE2,
STUDENT_SITE,
STUDENT_SITE_DESC,
STUDENT_LEVEL_FTE,
TUITION_STATUS)

The control file has the order of the fields correctly with the Excel spreadsheet.

Thanks,

Vic
Re: SQL Loader - Excel file different column order format [message #506511 is a reply to message #506460] Mon, 09 May 2011 09:35 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
You'll have to give sample contents for csv file. We can't fix issues we can't recreate.
Re: SQL Loader - Excel file different column order format [message #506512 is a reply to message #506511] Mon, 09 May 2011 09:37 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
How can we reproduce and correct what you report?

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
Re: SQL Loader - Excel file different column order format [message #506720 is a reply to message #506512] Tue, 10 May 2011 15:45 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
This is a test table:
SQL> desc test
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 FIRST_NAME                                         VARCHAR2(20)
 ADDRESS                                            VARCHAR2(20)
At the beginning, everything is OK. This is how the control looks like:
load data
infile *
into table test
replace
fields terminated by ','
(id,
 first_name,
 address
)

begindata
1,Little,Croatia
2,Michel,France
3,Barbara,US of A
Loading session (snip) and the results:
SQL> $sqlldr scott/tiger control=test.ctl log=test.log

SQL> select * from test;

        ID FIRST_NAME           ADDRESS
---------- -------------------- --------------------
         1 Little               Croatia
         2 Michel               France
         3 Barbara              US of A
As expected - no problem at all.

OK, let's alter a table because input changed (people got their last name):
SQL> alter table test add last_name varchar2(20);

Table altered.

SQL> desc test
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 FIRST_NAME                                         VARCHAR2(20)
 ADDRESS                                            VARCHAR2(20)
 LAST_NAME                                          VARCHAR2(20)

As you said, new column's value (in the input, CSV file) doesn't come at the end.

Check the BEGINDATA section - last names (Foot, Cadot, Boehmer) are NOT the last values. Also check the column list - newly added column (LAST_NAME) is located in a position that reflects CSV file's contents (i.e. not at the end of the column list (which is the position in a table)):
load data
infile *
into table test
replace
fields terminated by ','
(id,
 first_name,
 last_name,
 address
)

begindata
1,Little,Foot,Croatia
2,Michel,Cadot,France
3,Barbara,Boehmer,US of A

Let's test it:
SQL> $sqlldr scott/tiger control=test_newcol.ctl log=test_newcol.log

SQL> select * from test;

        ID FIRST_NAME           ADDRESS              LAST_NAME
---------- -------------------- -------------------- --------------------
         1 Little               Croatia              Foot
         2 Michel               France               Cadot
         3 Barbara              US of A              Boehmer

I don't see any problem here, everything looks as it should.

So, what should you do?
- ALTER TABLE (to add a column)
- include the column into the control file. The position should reflect its position within the CSV file.

What did you do so that it failed?
Re: SQL Loader - Excel file different column order format [message #506915 is a reply to message #506720] Wed, 11 May 2011 16:15 Go to previous message
nichollsvi
Messages: 8
Registered: May 2009
Location: Va.
Junior Member
I had an extra column in there that I missed removing. Once that was done, it worked like a charm. Once you start seeing so much of this, over and over, you can't see the forest for the trees.

Thanks!

Vic
Previous Topic: Logical backups
Next Topic: ORA-02298: - parent keys not found
Goto Forum:
  


Current Time: Fri Mar 29 01:52:30 CDT 2024