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 |
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 #506460 is a reply to message #506347] |
Mon, 09 May 2011 06:50 |
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 #506720 is a reply to message #506512] |
Tue, 10 May 2011 15:45 |
|
Littlefoot
Messages: 21823 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?
|
|
|
|
Goto Forum:
Current Time: Sat Jan 25 13:28:07 CST 2025
|