| How to covert the scientific format value into number or text in oracle [message #341708] |
Wed, 20 August 2008 01:30  |
krishna.kanigelpula
Messages: 12
Registered: August 2008
Location: Sydney
|
Junior Member |
|
|
Hi Forum,
I used SQL loader to import an csv file into an Oracle database. One of the columns in the csv file is SNO, which is a text format in csv and VCHAR2(50) in Oracle DB.
The source (CSV file), is holding the correct data. But, adter importing into the oracle DB, the some of the serial numbers are converting into scientific format.
eg:
SERIAL_NUMBER
--------------------------------------------------
5.57E+11
But, the actual value of that SNO in the source CSV file is : 557000100816
Is there any way to change the format to normal text / number in excel or in the database itself.
Cheers,
KK
Krishna.
|
|
|
|
|
|
| Re: How to covert the scientific format value into number or text in oracle [message #341725 is a reply to message #341713] |
Wed, 20 August 2008 02:14  |
krishna.kanigelpula
Messages: 12
Registered: August 2008
Location: Sydney
|
Junior Member |
|
|
Thanks Barbara for your response.
I tried the way you suggested by changing the set numwidth 40. But, no luck
SQL> set numwidth 20
SQL> select serial_number from anzo_base where instance_id = '142';
SERIAL_NUMBER
--------------------------------------------------
5.57E+11
SQL> set numwidth 40
SQL> select serial_number from anzo_base where instance_id = '142';
SERIAL_NUMBER
--------------------------------------------------
5.57E+11
SQL>
Cheers,
Krishna.
|
|
|
|
|
|
| Re: How to covert the scientific format value into number or text in oracle [message #341737 is a reply to message #341734] |
Wed, 20 August 2008 02:27 |
krishna.kanigelpula
Messages: 12
Registered: August 2008
Location: Sydney
|
Junior Member |
|
|
Yes it is Varchar2 Michel. The reason being is I am having some more serial number, which has the combination of alphanumeric
SQL> desc anzo_base;
Name Null? Type
----------------------------------------- -------- ----------------------------
INSTANCE_ID NOT NULL NUMBER(15)
INSTANCE_NUMBER NOT NULL NUMBER(15)
SERIAL_NUMBER NOT NULL VARCHAR2(50)
INSTANCE_STATUS_ID NOT NULL VARCHAR2(15)
ACTIVE_START_DATE NOT NULL VARCHAR2(15)
ACTIVE_END_DATE NOT NULL VARCHAR2(15)
Thanks
Krishna.
|
|
|
|
|
|
|
|
|
|
| Re: How to covert the scientific format value into number or text in oracle [message #341801 is a reply to message #341752] |
Wed, 20 August 2008 06:16 |
krishna.kanigelpula
Messages: 12
Registered: August 2008
Location: Sydney
|
Junior Member |
|
|
Michel,
I've pasted the control file, which I am using to populate the Database based on the CSV file.
CONTROL FILE:
load data
infile 'D:\Documents and Settings\Administrator\Desktop\ANZO Install Base\ANZO_Install_Base\part1.csv'
into table load_anzo_base
fields terminated by "," optionally enclosed by '"'
( Instance_Id, Instance_Number, Serial_Number, Instance_Status_Id, Active_Start_Date, Active_End_Date, Owner_Party_Id,
Owner_Party_Account_Id, Owner_Customer_Name, Owner_Party_Number, Global_Customer_Name, Global_Party_Number, Install_Address1
)
Could, you please advice me, where about the changes are required.
Cheers,
Krishna.
|
|
|
|
|
|
|
|
|
|
| Re: How to covert the scientific format value into number or text in oracle [message #342022 is a reply to message #341915] |
Thu, 21 August 2008 01:51 |
krishna.kanigelpula
Messages: 12
Registered: August 2008
Location: Sydney
|
Junior Member |
|
|
Michel, Barbara,
I fixed the issue. What I did is, I've changed the serial_number column format to TEXT in the csv file and imported it into the oracle DB.
Coming to your question.
I am loading my CSV file into a temp_table called (Load_anzo_base) and from there I am using
insert into anzo_base (select * from load_anzo_base); to load the actual table (anzo_base).
The reason being is I am having multiple CSV files, which I need to load it into one table. So, I used the load_anzo_base as a buffer.
Thanks all for you valuable assistance.
Cheers,
Krishna Kanigelpula.
|
|
|
|
|
|
| Re: How to covert the scientific format value into number or text in oracle [message #342276 is a reply to message #342152] |
Thu, 21 August 2008 19:28 |
krishna.kanigelpula
Messages: 12
Registered: August 2008
Location: Sydney
|
Junior Member |
|
|
Barbara,
Yes, you are right. There is no need for an extra step. Thanks for advsing me. It saves a bit of time to me.
Is there any way, where I can use export multiple CSV's into a table at a time using the SQL loader.
Coming to the changing the column into text, I have surrounded the actual serial_number with the ("4235098324508934"), to make it as a text and saving as a CSV file and importing it into the DB. This worked well.
Cheers,
Krishna.
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
