Home » RDBMS Server » Server Utilities » SQL Loader Date&Time Upload Column Problem
icon1.gif  SQL Loader Date&Time Upload Column Problem [message #187926] Wed, 16 August 2006 06:52 Go to next message
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 #187942 is a reply to message #187926] Wed, 16 August 2006 07:23 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
you can use a to_date function within the sqlldr control file.
Please search the forum before posting ( Most probably it would have have been answered already).
http://www.orafaq.com/forum/m/73802/42800/?srch=to_date+sqlldr#msg_73802

Regards


icon1.gif  Re: SQL Loader Date&Time Upload Column Problem [message #187949 is a reply to message #187942] Wed, 16 August 2006 07:51 Go to previous messageGo to next message
ahirde
Messages: 5
Registered: August 2006
Junior Member
Mahesh,
Thanks for your reply & the link that you have sent. I will try to explore & upload the file into table.
If in case I get any issues, will post the same.

Regards,
Ashish
Re: SQL Loader Date&Time Upload Column Problem [message #189375 is a reply to message #187926] Thu, 24 August 2006 05:52 Go to previous messageGo to next message
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 #189386 is a reply to message #189375] Thu, 24 August 2006 06:51 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Please search the forum. There must be several examples/cases like this. If nothing works, please post again.

http://www.orafaq.net/msgboard/serverutil/messages/5276.htm
http://www.orafaq.com/forum/t/26259/0/

Regards
Re: SQL Loader Date&Time Upload Column Problem [message #189887 is a reply to message #189386] Mon, 28 August 2006 06:01 Go to previous messageGo to next message
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 #189902 is a reply to message #189887] Mon, 28 August 2006 07:25 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Did you use the stream format?
Re: SQL Loader Date&Time Upload Column Problem [message #190064 is a reply to message #189902] Tue, 29 August 2006 03:33 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
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 Go to previous message
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,

Previous Topic: about 9.2.0 emp/imp error
Next Topic: exporting big table
Goto Forum:
  


Current Time: Tue Dec 06 15:57:22 CST 2016

Total time taken to generate the page: 0.09438 seconds