Home » RDBMS Server » Server Utilities » export excel sheet data in database table
| export excel sheet data in database table [message #560839] |
Tue, 17 July 2012 06:54  |
 |
neetesh87
Messages: 280
Registered: 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]
Copyright (c) 2009 Microsoft Corporation. All rights reserved.
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
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL*Loader-500: Unable to open file (c:/users/neetesh/scott_data.ctl)
SQL*Loader-553: file not found
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.
thanx in advance........
|
|
|
|
|
|
|
|
|
|
| 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: 8966
Registered: 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 #560865 is a reply to message #560856] |
Tue, 17 July 2012 08:25 |
|
neetesh87
Messages: 280
Registered: 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]
Copyright (c) 2009 Microsoft Corporation. All rights reserved.
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
07/11/2012 04:53 PM <DIR> passwords
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
05/08/2012 04:14 PM <DIR> toad
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
Copyright (c) 1982, 2005, Oracle. All rights reserved.
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: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
neetesh87 wrote on Tue, 17 July 2012 09:25thanks 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 #560923 is a reply to message #560920] |
Tue, 17 July 2012 15:26 |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
Littlefoot wrote on Tue, 17 July 2012 16:01Huh, 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: 280
Registered: 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
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Commit point reached - logical record count 105
D:\>
but rows are not impported in the table.
ctl file is-
LOAD DATA
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
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Control File: row_rules_data.ctl.txt
Data File: D:\row_rules_data.csv
Bad File: row_rules_data.bad
Discard File: none specified
(Allow all discards)
Number to load: ALL
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:
0 Rows successfully loaded.
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)
Read buffer bytes: 1048576
Total logical records skipped: 0
Total logical records read: 105
Total logical records rejected: 51
Total logical records discarded: 0
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 #561364 is a reply to message #561323] |
Fri, 20 July 2012 13:38 |
 |
Barbara Boehmer
Messages: 9104
Registered: 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 #561579 is a reply to message #560839] |
Tue, 24 July 2012 01:44 |
|
neetesh87
Messages: 280
Registered: 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-
ruleid,rowrule->6489,collaborate^sr_abcd_checkteporary^collaborate_ADDRESS_1^not^^{{collaborate}}^{{collaborate1}}
although it doesnt contain " double quote sign and size also very less in comparison to others.
and control file is
LOAD DATA
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
Copyright (c) 1982, 2005, Oracle. All rights reserved.
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: 9104
Registered: 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}}^
"
6488,^Release^Release_ADDRESS_3^equal^^^{{Release_ADDRESS_3}}
6489,"Releasead^VW_SCRN_MAINTENANCE_RELEASE^Release_ADDRESS_1^not^^{{RuleReleasead1}}^{{Rulead1}}
"
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}}, +
<b>Line+No.:<||b>+{{VW_SCRN_PROJECT:PROJECT_NUMBER}}, +<b>Tract+No.:<||b>+__________________, +
<b>W.+O.+No.:<||b>+{{VW_SCRN_PROJECT:ROW_WORK_ORDER_NUMBER}}, +<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.
|
|
|
|
|
|
Goto Forum:
Current Time: Mon Mar 03 16:13:38 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
