OraFAQ Forum: Server Utilities » Have to replace ? with blank space when loading data from csv to oracle

Home » RDBMS Server » Server Utilities » Have to replace ? with blank space when loading data from csv to oracle

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Have to replace ? with blank space when loading data from csv to oracle [message #290956]

Wed, 02 January 2008 05:15

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.

Report message to a moderator

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

Michel Cadot
Messages: 68665
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

Report message to a moderator

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

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.

Report message to a moderator

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

Michel Cadot
Messages: 68665
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

Report message to a moderator

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

joy_division
Messages: 4963
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.

Report message to a moderator

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

vanathi
Messages: 74
Registered: December 2007
Location: India

Member

blank space only.

Report message to a moderator

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

Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Read the link I posted.

Regards
Michel

Report message to a moderator

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

Littlefoot
Messages: 21811
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?

Report message to a moderator

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

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.

Report message to a moderator

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

BlackSwan
Messages: 26766
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.

Report message to a moderator