Home » RDBMS Server » Server Utilities » Have to replace ? with blank space when loading data from csv to oracle
Have to replace ? with blank space when loading data from csv to oracle [message #290956] Wed, 02 January 2008 05:15 Go to next message
vanathi
Messages: 74
Registered: December 2007
Location: India
Member
Hi,

I am loading data from csv to oracle tables.
I may get ? sometimes instead of data.
For that data I have to leave a blank space.
Is it possible?...

Please advice.

Thanks in advance.
Re: Have to replace ? with blank space when loading data from csv to oracle [message #290961 is a reply to message #290956] Wed, 02 January 2008 06:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You get ? in the file or in the database after loading when it is not in the file?

Regards
Michel
Re: Have to replace ? with blank space when loading data from csv to oracle [message #290964 is a reply to message #290961] Wed, 02 January 2008 06:31 Go to previous messageGo to next message
vanathi
Messages: 74
Registered: December 2007
Location: India
Member
I have a csv like
Sample data
76,?,rt,ls

In this case, I have to load the table with the blank space for the second column.






Re: Have to replace ? with blank space when loading data from csv to oracle [message #290969 is a reply to message #290964] Wed, 02 January 2008 06:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
field CHAR "REPLACE(:field,'?',' ')"

See Applying SQL Operators to Fields section in documentation.

Regards
Michel
Re: Have to replace ? with blank space when loading data from csv to oracle [message #290979 is a reply to message #290956] Wed, 02 January 2008 08:01 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
vanathi wrote on Wed, 02 January 2008 06:15

Hi,

I am loading data from csv to oracle tables.
I may get ? sometimes instead of data.
For that data I have to leave a blank space.
.


Are you sure you want a blank space and not a NULL? Now you will not know upon viewing your table whether there is a space no value in your column.
Re: Have to replace ? with blank space when loading data from csv to oracle [message #290980 is a reply to message #290979] Wed, 02 January 2008 08:03 Go to previous messageGo to next message
vanathi
Messages: 74
Registered: December 2007
Location: India
Member
blank space only.
Re: Have to replace ? with blank space when loading data from csv to oracle [message #290991 is a reply to message #290980] Wed, 02 January 2008 08:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Read the link I posted.

Regards
Michel
Re: Have to replace ? with blank space when loading data from csv to oracle [message #291020 is a reply to message #290980] Wed, 02 January 2008 14:11 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
vanathi wrote on Wed, 02 January 2008 15:03

blank space only.

What will you do when '?' comes in a place that represents a table column whose datatype is NUMBER?

See an example: this is a control file:
load data
infile *

replace
into table test
( col_n integer external (10) "replace(:col_n, '?', ' ')" )

begindata
42
?
1889
Run the test:
SQL> create table test (col_n number(10));

Table created.

SQL> $sqlldr scott/tiger control=test.ctl log=test.log

SQL*Loader: Release 10.2.0.1.0 - Production on Sri Sij 2 21:07:04 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Commit point reached - logical record count 2
Commit point reached - logical record count 3

SQL> select * from test;

     COL_N
----------
        42
      1889

SQL>
An excerpt of the log file:
Quote:


SQL string for column : "replace(:col_n, '?', ' ')"

Record 2: Rejected - Error on table TEST, column COL_N.
ORA-01722: invalid number

Table TEST:
2 Rows successfully loaded.
1 Row 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.


So, are you sure you don't want a NULL instead?
Re: Have to replace ? with blank space when loading data from csv to oracle [message #291056 is a reply to message #291020] Wed, 02 January 2008 21:35 Go to previous messageGo to next message
vanathi
Messages: 74
Registered: December 2007
Location: India
Member
My req.

Some missing data elements will contain the standard ‘?’ value. This should be associated with a blank value.


Please advice.
Re: Have to replace ? with blank space when loading data from csv to oracle [message #291060 is a reply to message #290956] Wed, 02 January 2008 22:25 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
You have the choice to read and FOLLOW the posting guidelines as stated above.
If you choose to ignore the posting guidelines, then we can choose to ignore your posts.
Re: Have to replace ? with blank space when loading data from csv to oracle [message #291079 is a reply to message #291056] Wed, 02 January 2008 23:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Wed, 02 January 2008 13:42

field CHAR "REPLACE(:field,'?',' ')"

See Applying SQL Operators to Fields section in documentation.

In addition there is LittleFoot's example.
Are you s... or blind?

Regards
Michel

[Updated on: Wed, 02 January 2008 23:27]

Report message to a moderator

Re: Have to replace ? with blank space when loading data from csv to oracle [message #291107 is a reply to message #291079] Thu, 03 January 2008 00:52 Go to previous messageGo to next message
vanathi
Messages: 74
Registered: December 2007
Location: India
Member
Thank you friends...
I will check and tell...
Re: Have to replace ? with blank space when loading data from csv to oracle [message #294951 is a reply to message #291107] Mon, 21 January 2008 00:25 Go to previous message
vanathi
Messages: 74
Registered: December 2007
Location: India
Member
I used null and it is working fine now.
Thank you friends.
Previous Topic: SQL Loader Doubt
Next Topic: How to use two functions for a column in control file
Goto Forum:
  


Current Time: Fri Dec 09 19:09:20 CST 2016

Total time taken to generate the page: 0.13771 seconds