 SQL Loader Date&Time Upload Column Problem [message #187926] |
Wed, 16 August 2006 06:52  |
ahirde
Messages: 5
Registered: August 2006
|
Junior Member |
|
|
Hi,
I am using SQL Loader for the first time & trying to load data into a table. I am facing problem while uploading a data having DATE column. Although I have done all the permutation combination by specifying the datatype but it is not getting uploaded, whereas all other columns are getting populate correctly.
For your reference the control file that I created
load data
infile 'c:\data\test.txt'
APPEND INTO TABLE empload
fields terminated BY "|" optionally enclosed BY '"'
TRAILING NULLCOLS
( name,
empno,
address,
company,
compaddr,
t_date)
My text file contains date column in this manner Eg. 16/08/2006 12:31:10 PM
Can you pls. let me know how can I upload this into a table.
Thanks & Regards,
Ashish
|
|
|
|
|
|
|
|
| Re: SQL Loader Date&Time Upload Column Problem [message #189375 is a reply to message #187926] |
Thu, 24 August 2006 05:52  |
ahirde
Messages: 5
Registered: August 2006
|
Junior Member |
|
|
Mahesh,
I am able to upload the file into database table successfully. Right now I am facing problem while uploading a text column data. Actually the data that needs to be uploaded in the text column is having carriage return & SQL Loader is treating it as a new line. In control file I have specified the datatype against the column.
I am trying to find out solution in this forum but the solution that is there is not helping me.
The column in the table is having datatype as VARCHAR2(2000). The text file is '|' delimited.
Could you please guide me in this regard.
Sample of the text file below
1|54|0|15|1|3|0|0|205|A|Factory built of corrugated steel sheets on 2 Meters concrete wall and roofed with steel sheets and office buildings built of Concrete blocks and roofed with precast slabs situate Industrial Estate (Liability included)||15/11/2000 3:13:00PM|01/03/2000|28/02/2001|0||1|0|1200|0|0|0|2770308|0|0|
If I put the above sample file in a single line in notepad it gets uploaded without any errors.
Thanks & Regards,
Ashish
|
|
|
|
|
|
| Re: SQL Loader Date&Time Upload Column Problem [message #189887 is a reply to message #189386] |
Mon, 28 August 2006 06:01 |
ahirde
Messages: 5
Registered: August 2006
|
Junior Member |
|
|
Mahesh,
Tried to explore the link & the content associated with it. I did necessary changes in the control file but still I am not able to upload the file into the table.
Do I need to change the delimiter from pipe (|) to some other delimiter or do I need to put all char columns in double quotes (" ") which is optional.
Could you pls. guide me.
Thanks & Regards,
Ashish
|
|
|
|
|
|
| Re: SQL Loader Date&Time Upload Column Problem [message #190064 is a reply to message #189902] |
Tue, 29 August 2006 03:33 |
ahirde
Messages: 5
Registered: August 2006
|
Junior Member |
|
|
Mahesh,
I was successfully able to upload the file. Actually, there was a problem in the text file. The problem was, delimiter pipe '|' was missing at the end of the record, secondly, as such the records were enclosed in double quotes ' " " ', in some of the records, description which was creating a problem already had double quote in it. So, I manually removed double quote (5-10 existance of double quotes) & put pipe at the end of the record & then tried to upload, it got uploaded without any errors.
This double quote in the description field can be controlled at my end, but the problem is regarding the pipe at the end of each record. Right now I am using Toad to create a pipe delimited text file, but at the end of the record its not putting pipe. I am trying to explore on this. If in case you know any why through with I can overcome this problem it will be very helpful.
Thanks for all your guidance from start. I helped me a lot.
Regards,
Ashish
|
|
|
|
| Re: SQL Loader Date&Time Upload Column Problem [message #190105 is a reply to message #190064] |
Tue, 29 August 2006 05:12 |
 |
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
i do not use TOAD.
But these sort of formatting issues can be easily dealt with some simple scripting ( which is use a lot).
-- original file
oracle@mutation#cat dept.data
10,ACCOUNTING,NEW YORK
20,~,DALLAS
30,SALES,CHICAGO
40,~,BOSTON
--
-- use little awk
-- i am just padding the last record with a pipepline and
-- redirecting the output to a new file.
oracle@mutation#awk -F',' '{print $0"|"}' dept.data > newdept.data
--
-- New file
--
oracle@mutation#cat newdept.data
10,ACCOUNTING,NEW YORK|
20,~,DALLAS|
30,SALES,CHICAGO|
40,~,BOSTON|
|
|
|
|
| Re: SQL Loader Date&Time Upload Column Problem [message #190675 is a reply to message #187926] |
Thu, 31 August 2006 12:45 |
goudelly
Messages: 52
Registered: August 2006
Location: India
|
Member |
|
|
Hi,
You can use like this :
load data
infile 'c:\data\test.txt'
APPEND INTO TABLE empload
fields terminated BY "|" optionally enclosed BY '"'
TRAILING NULLCOLS
( name,
empno,
address,
company,
compaddr,
to_char(t_date,'dd/mm/yyyy hh:mi:ss'))
you have to provide the date format as you like text file is having.
Thanks,
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
