Home » RDBMS Server » Server Utilities » export excel sheet data in database table
export excel sheet data in database table Tue, 17 July 2012 06:54
 neetesh87 Messages: 280Registered: September 2011 Location: bhopal Senior Member
hello experts,
i want to export excel sheet in database table, so i have converted excel file in .csv file(comma delimated)and made control file, then
i started sqlldr by double clicking on it. path is-D:\oracle\product\10.2.0\client_1\BIN

i run this command from cmd-

Microsoft Windows [Version 6.1.7600]

C:\Users\Neetesh>sqlldr scott/tiger@localdb control=c:/users/neetesh/scott_data.
ctl

SQL*Loader: Release 10.2.0.1.0 - Production on Tue Jul 17 17:20:33 2012

SQL*Loader-500: Unable to open file (c:/users/neetesh/scott_data.ctl)
SQL*Loader-509: System error: The system cannot find the file specified.

C:\Users\Neetesh>

and i attached the .ctl file. and .csv file is stored on same directory as .ctl file, please tell me what is the problem .why oracle couldnot find the .ctl file.

Re: export excel sheet data in database table [message #560840 is a reply to message #560839] Tue, 17 July 2012 06:57
 Littlefoot Messages: 21756Registered: June 2005 Location: Croatia, Europe Senior MemberAccount Moderator
I'd suggest you to open command prompt and set current directory to the one that contains your CSV and CTL files. For example:
M:\>c:

C:\>cd c:\users\neetesh\

Note the backslash (not forward slash! as you used it). Then run the SQLLDR executable.
Re: export excel sheet data in database table [message #560845 is a reply to message #560840] Tue, 17 July 2012 07:15
 neetesh87 Messages: 280Registered: September 2011 Location: bhopal Senior Member
but same result-

Microsoft Windows [Version 6.1.7600]

C:\Users\Neetesh>sqlldr scott/tiger@localdb control=c:\users\neetesh\scott_data.ctl

SQL*Loader: Release 10.2.0.1.0 - Production on Tue Jul 17 17:42:29 2012

SQL*Loader-500: Unable to open file (c:\users\neetesh\scott_data.ctl)
SQL*Loader-509: System error: The system cannot find the file specified.

C:\Users\Neetesh>

sir,is there any modification needed in cmd here?

thanks again....
Re: export excel sheet data in database table [message #560847 is a reply to message #560845] Tue, 17 July 2012 07:25
 Littlefoot Messages: 21756Registered: June 2005 Location: Croatia, Europe Senior MemberAccount Moderator
While in C:\Users\Neetesh:
dir scott_data.ctl

What is the result?

Also, you don't need to specify the whole path now. This might be OK:
sqlldr scott/tiger@localdb control=scott_data.ctl log=scott_data.log
Re: export excel sheet data in database table [message #560849 is a reply to message #560845] Tue, 17 July 2012 07:29
 John Watson Messages: 8793Registered: January 2010 Location: Global Village Senior Member
Quote:
and i attached the .ctl file. and .csv file is stored on same directory as .ctl file,
Thhe file you have attached is called scott_data.ctl.txt, and the data file to which it refers is called c:\scott_data
Are you sure about your file names and the directory in which they reside?
Re: export excel sheet data in database table [message #560853 is a reply to message #560849] Tue, 17 July 2012 07:48
 neetesh87 Messages: 280Registered: September 2011 Location: bhopal Senior Member
thanx sir,

Littlefoot wrote on Tue, 17 July 2012 07:25
While in C:\Users\Neetesh:
dir scott_data.ctl

What is the result?

Microsoft Windows [Version 6.1.7600]

sir this the result..

C:\Users\Neetesh>dir scott_data.ctl
Volume in drive C has no label.
Volume Serial Number is A681-24CE

Directory of C:\Users\Neetesh

C:\Users\Neetesh>

John Watson wrote on Tue, 17 July 2012 07:29
Quote:
and i attached the .ctl file. and .csv file is stored on same directory as .ctl file,
Thhe file you have attached is called scott_data.ctl.txt, and the data file to which it refers is called c:\scott_data
Are you sure about your file names and the directory in which they reside?

yes sir, it is in-C:\Users\Neetesh and a log text file is created at same directory(C:\Users\Neetesh) which containts the errors , shown in command prompt.

is there need to move these files to other location?
and i also run this command on- start menu->run
although same error occurs.i think this is another problem not a problem of path , because log text file is created at same place where scott_data.ctl.txt and data file exist.

thanx again sir...
Re: export excel sheet data in database table [message #560856 is a reply to message #560853] Tue, 17 July 2012 07:53
 Littlefoot Messages: 21756Registered: June 2005 Location: Croatia, Europe Senior MemberAccount Moderator
DIR SCOTT_DATA.CTL said "File not found". You can't expect it to work if you a) misspelled its name, b) the file doesn't exist.

Make SURE that it is called SCOTT_DATA.CTL (not SCOTT_DATA.CTL.TXT or whatever else). Then run the SQLLDR statement once again (as I showed you in my previous message).
Re: export excel sheet data in database table [message #560865 is a reply to message #560856] Tue, 17 July 2012 08:25
 neetesh87 Messages: 280Registered: September 2011 Location: bhopal Senior Member
thanks sir,
it is done.
there is a problem in .ctl file that file name was scott_data.ctl but it was in actual scott_data.ctl.txt.

Microsoft Windows [Version 6.1.7600]

C:\Windows\system32>d:

D:\>dir /p
Volume in drive D is New Volume
Volume Serial Number is 744F-88A0

Directory of D:\

thanx again....

05/23/2012 05:08 PM <DIR> app
05/23/2012 03:44 PM <DIR> DataBase_Diff
07/06/2012 08:21 PM <DIR> groove backup
07/11/2012 12:48 PM <DIR> jdk
12/01/2006 11:37 PM 904,704 msdia80.dll
07/13/2012 01:43 PM <DIR> my local oracle notes
05/23/2012 03:37 PM <DIR> neetesh
05/23/2012 05:26 PM <DIR> oracle
05/28/2012 01:51 PM <DIR> Program Files
07/17/2012 05:32 PM 75 scott_data.csv.csv
07/17/2012 05:32 PM 164 scott_data.ctl.txt
07/16/2012 04:12 PM 7,540 scott_data.xlsx
07/16/2012 04:59 PM <DIR> set ups
06/03/2010 05:55 PM 22,971,688 SkypeSetupFull.exe
05/23/2012 05:49 PM <DIR> software
05/21/2012 05:19 PM <DIR> softwares
05/23/2012 03:45 PM <DIR> TEMP
Press any key to continue . . .
07/16/2012 02:11 PM <DIR> work
5 File(s) 23,884,171 bytes
15 Dir(s) 175,608,676,352 bytes free

D:\>dir scott_data.csv.csv
Volume in drive D is New Volume
Volume Serial Number is 744F-88A0

Directory of D:\

07/17/2012 05:32 PM 75 scott_data.csv.csv
1 File(s) 75 bytes
0 Dir(s) 175,608,676,352 bytes free

D:\>
d:>sqlldr scott/tiger@localdb control=d:\scott_data.ctl.txt

SQL*Loader: Release 10.2.0.1.0 - Production on Tue Jul 17 18:42:47 2012

Commit point reached - logical record count 5

d:>
Re: export excel sheet data in database table [message #560888 is a reply to message #560865] Tue, 17 July 2012 12:49
 joy_division Messages: 4963Registered: February 2005 Location: East Coast USA Senior Member
neetesh87 wrote on Tue, 17 July 2012 09:25
thanks sir,
it is done.
there is a problem in .ctl file that file name was scott_data.ctl but it was in actual scott_data.ctl.txt.

Not to sound harsh, I really do not intend that to be the case, but...the first thing you should have checked was whether the file existed since Oracle did indeed tell you "Unable to open file (c:/users/neetesh/scott_data.ctl)." Wouldn't the first logical thing to do would be to see if scott_data.ctl did exist, no matter if you *think* it did or not?
Re: export excel sheet data in database table [message #560920 is a reply to message #560888] Tue, 17 July 2012 15:01
 Littlefoot Messages: 21756Registered: June 2005 Location: Croatia, Europe Senior MemberAccount Moderator
Huh, right - but Windows can be misleading here if you aren't careful enough. You can set it NOT to display extensions of known file types, so it'll - in Windows Explorer - hide .TXT (or .EXE or .BAT or .DOC or ...) and leave the "unknown" extension - .CTL. Therefore, if he opened Windows Explorer, SCOTT_DATA.CTL was really there. Too bad that .TXT was "hidden".

It would be different in DOS - DIR SCOTT_DATA.* would actually show the real extension.
Re: export excel sheet data in database table [message #560923 is a reply to message #560920] Tue, 17 July 2012 15:26
 joy_division Messages: 4963Registered: February 2005 Location: East Coast USA Senior Member
Littlefoot wrote on Tue, 17 July 2012 16:01
Huh, right - but Windows can be misleading here if you aren't careful enough. You can set it NOT to display extensions of known file types, so it'll - in Windows Explorer - hide .TXT (or .EXE or .BAT or .DOC or ...) and leave the "unknown" extension - .CTL. Therefore, if he opened Windows Explorer, SCOTT_DATA.CTL was really there. Too bad that .TXT was "hidden".

It would be different in DOS - DIR SCOTT_DATA.* would actually show the real extension.

Excellent point!

[Updated on: Tue, 17 July 2012 15:26]

Report message to a moderator

Re: export excel sheet data in database table [message #561307 is a reply to message #560923] Fri, 20 July 2012 06:54
 neetesh87 Messages: 280Registered: September 2011 Location: bhopal Senior Member
thanks alot to all,
i want to know that is there may be any issue of size of data for a column like a clob column.
while sqlldr statement is successfully run and shows result like-

cmd:-

D:\>sqlldr aepuser/test@chkdb control=row_rules_data.ctl.txt

SQL*Loader: Release 10.2.0.1.0 - Production on Fri Jul 20 16:49:30 2012

Commit point reached - logical record count 105

D:\>

but rows are not impported in the table.

ctl file is-

INFILE 'D:\row_rules_data.csv'
INSERT
INTO TABLE ROW_RULES
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(RULEID,
ROWRULE )

and oracle creates log file with same name of ctl file, which has error details something related to
size of data. some info is-

SQL*Loader: Release 10.2.0.1.0 - Production on Fri Jul 20 16:49:30 2012

Control File: row_rules_data.ctl.txt
Data File: D:\row_rules_data.csv

Number to skip: 0
Errors allowed: 50
Bind array: 64 rows, maximum of 256000 bytes
Continuation: none specified
Path used: Conventional

Table ROW_RULES, loaded from every logical record.
Insert option in effect for this table: REPLACE
TRAILING NULLCOLS option in effect

Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
RULEID FIRST * , O(") CHARACTER
ROWRULE NEXT * , O(") CHARACTER

Record 1: Rejected - Error on table ROW_RULES, column ROWRULE.
second enclosure string not present
Record 28: Rejected - Error on table ROW_RULES, column RULEID.
second enclosure string not present
Record 3: Rejected - Error on table ROW_RULES, column RULEID.
ORA-01722: invalid number

Record 4: Rejected - Error on table ROW_RULES, column RULEID.
ORA-01722: invalid number
MAXIMUM ERROR COUNT EXCEEDED - Above statistics reflect partial run.

Table ROW_RULES:
51 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.

Space allocated for bind array: 33024 bytes(64 rows)

Total logical records skipped: 0
Total logical records rejected: 51

Run began on Fri Jul 20 16:49:30 2012
Run ended on Fri Jul 20 16:49:52 2012

Elapsed time was: 00:00:21.58
CPU time was: 00:00:00.05

and oracle makes bad file also it contains those records which was not inserted(i think),
so no records were inserted because of script rollback.

please tell me why the data import could not be done.
i am using oracle 11.2.0.1.0 .
datatype of columns are-

RULEID number
ROWRULE clob

thanx again.......

Re: export excel sheet data in database table [message #561308 is a reply to message #561307] Fri, 20 July 2012 07:04
 Littlefoot Messages: 21756Registered: June 2005 Location: Croatia, Europe Senior MemberAccount Moderator
Log file says what happened; did you not read it? What's the purpose in posting it here - YOU should have read it first.
Re: export excel sheet data in database table [message #561309 is a reply to message #561308] Fri, 20 July 2012 07:10
 neetesh87 Messages: 280Registered: September 2011 Location: bhopal Senior Member
Littlefoot wrote on Fri, 20 July 2012 07:04
Log file says what happened;

thanks sir.....
Re: export excel sheet data in database table [message #561323 is a reply to message #561309] Fri, 20 July 2012 08:44
 neetesh87 Messages: 280Registered: September 2011 Location: bhopal Senior Member
is there any thing different for inserting data in clob column when control file is-

INFILE 'D:\row_rules_data.csv'
INSERT
INTO TABLE ROW_RULES
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(RULEID,
ROWRULE )

--where ruleid is number and rowrule is clob type.

thanks....
Re: export excel sheet data in database table [message #561364 is a reply to message #561323] Fri, 20 July 2012 13:38
 Barbara Boehmer Messages: 8933Registered: November 2002 Location: California, USA Senior Member
If you do not specify a data length, then the default is 255. So, if your clob data is longer than 255, it will not load and you will normally get an error that says that you have exceeded the maximum length. You can fix this by specifying the data length, for example:

ROWRULE CHAR(5000)

However, the error message in your SQL*Loader log file indicates that some values of your ruleid are not valid numbers and that there are so many such values that they have exceeded the maximum allowed number of errors. Although you could have a problem with your ruleid values, you may also have a problem with it reading data that belongs in the clob and those not being numbers. This will happen with the control file that you posted, if your data wraps around on multiple lines. If that is the case, then you need to specify starting and ending delimiters for the clob data, for example:

ROWRULE CHAR(5000) ENCLOSED BY '"' AND '"'

If this does not solve your problem, then please post some realistic sample data to be loaded. You can attach a text file to your post if needed

[EDITED by LF: removed superfluous empty lines]

[Updated on: Fri, 20 July 2012 15:01] by Moderator

Report message to a moderator

Re: export excel sheet data in database table [message #561455 is a reply to message #560839] Mon, 23 July 2012 02:24
 neetesh87 Messages: 280Registered: September 2011 Location: bhopal Senior Member
thanks barbara,
i have tried in several ways to load data in a table, but couldn't only just because of clob data.

i want to insert data in my table temp_row_rules

SQL> desc temp_row_rules
Name Null? Type
----------------------------------------- -------- ----------------------------

RULEID NOT NULL NUMBER(10)
ROWRULE CLOB

SQL>

it is empty at this time and i want to insert data which is in excel sheet and it is converted in .csv file.
content of control file is -

INFILE 'D:\text_data.csv'
insert
INTO TABLE TEMP_ROW_RULES
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(ruleid,rowrule )

and command on cmd is-

D:\>sqlldr test/test@chkdb control=test.txt

please tell me the way in which i can load data consisting the clob data.

thanks again....

• Attachment: text_data.csv
Re: export excel sheet data in database table [message #561524 is a reply to message #561455] Mon, 23 July 2012 08:59
 joy_division Messages: 4963Registered: February 2005 Location: East Coast USA Senior Member
And how is this control file different from the last one? You ignored everything Barbara told you.

[EDITED by LF: removed unnecessary quote of the whole previous message]

[Updated on: Mon, 23 July 2012 13:42] by Moderator

Report message to a moderator

Re: export excel sheet data in database table [message #561558 is a reply to message #561455] Mon, 23 July 2012 20:12
 Barbara Boehmer Messages: 8933Registered: November 2002 Location: California, USA Senior Member
Although your data appears to be one row per record, when I try to load it, some characters are recognized as line feeds, so the data is split and it thinks the next line of the data should start with the ruleid, so about every second or third row raises an invalid number error. To solve this, I used CONTINUEIF LAST != '"' in addition to the CHAR(5000) in the control file below.

INFILE 'D:\text_data.csv'
INSERT
CONTINUEIF LAST != '"'
INTO TABLE temp_row_rules
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(ruleid,
rowrule CHAR(5000))
Re: export excel sheet data in database table [message #561579 is a reply to message #560839] Tue, 24 July 2012 01:44
 neetesh87 Messages: 280Registered: September 2011 Location: bhopal Senior Member
thanks barbara and all those who helped me alot,
>>joy davison
sir i wanted to show her that what i have done in control file , so i
showed her old control file.
thanks sir.

>>Barbara Boehmer
all records are inserted successfully except 1 record that is-

although it doesnt contain " double quote sign and size also very less in comparison to others.

and control file is

INFILE 'D:\text_data.csv'
insert
CONTINUEIF LAST != '"'
INTO TABLE TEMP_ROW_RULES
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(ruleid ,rowrule char(5000))

please tell me why this only one record is not inserted.

and one more question about statement execution on cmd-

D:\>sqlldr test/test@chkdb control=test.txt

SQL*Loader: Release 10.2.0.1.0 - Production on Tue Jul 24 12:00:41 2012

Commit point reached - logical record count 48
Commit point reached - logical record count 96
Commit point reached - logical record count 144
Commit point reached - logical record count 192
Commit point reached - logical record count 240
Commit point reached - logical record count 288
Commit point reached - logical record count 336
Commit point reached - logical record count 384
Commit point reached - logical record count 432
Commit point reached - logical record count 480
Commit point reached - logical record count 528
Commit point reached - logical record count 541

D:\>

>> why these "Commit point reached - logical record count 48" executed again and again with difference of 48 records.
i am using windows 7 64-bit os

your help will be highly appreciated.
thanks again.....

[Updated on: Tue, 24 July 2012 02:04]

Report message to a moderator

Re: export excel sheet data in database table [message #561663 is a reply to message #561579] Tue, 24 July 2012 11:46
 Barbara Boehmer Messages: 8933Registered: November 2002 Location: California, USA Senior Member
In your data (see excerpt below) the record with ruleid 6488 does not have an ending double quote. So, the command CONTINUEIF LAST != '"' tells it to continue to the next line and consider the next line part of the same record. It was necessary to use that to solve the problem of multiple lines of text per record. However, it means that any record without a closing double quote gets combined with the next record. So, the row with 6489, "Released ... gets added to the clob data for the previous record with ruleid 6488. There is only so much that you can do to fix things. You can't fix everything. You can have the rejected rows go to a badfile, then fix those rows manually and reload them.

6487,"Releasead1^VW_SCRN_MAINTENANCE_RELEASE^Release_ADDRESS_1^not^^{{VW_SCRN_MAINTENANCE_RELEASE:RELEASE_ADDRESS_1}},{{RuleReleasead2}}^
"
"
6490,"Footer2^VW_SCRN_PROP_OWNER_LEGAL_DESC^EASEMENT_STATE^equal^Oklahoma^<p><font+face=""Times+New+Roman""+style=""font-size:+16pt""><b>Project+Name:<||b>+{{VW_SCRN_PROJECT:PROJECT_NAME}},&nbsp;&nbsp;&nbsp;+
<b>Line+No.:<||b>+{{VW_SCRN_PROJECT:PROJECT_NUMBER}},&nbsp;&nbsp;&nbsp;+<b>Tract+No.:<||b>+__________________,&nbsp;&nbsp;&nbsp;+
<b>W.+O.+No.:<||b>+{{VW_SCRN_PROJECT:ROW_WORK_ORDER_NUMBER}},&nbsp;&nbsp;&nbsp;+<b>Check+No.:<||b>+__________________<||font><||p>+++^{{RuleFooter3}}
"


SQL*Loader has a ROWS (rows per commit) parameter that can be issued in the SQL*Loader command line or using OPTIONS in the control file. This parameter determines after how many rows to commit. If you do not specify a value, then the default is 64.

Re: export excel sheet data in database table [message #561939 is a reply to message #560839] Thu, 26 July 2012 06:01
 neetesh87 Messages: 280Registered: September 2011 Location: bhopal Senior Member
thank you very much barbara,