topic Re: Number conversion in Operating System - HP-UX

Number conversion

Gopi Kishore m — Tue, 14 Dec 2010 12:39:56 GMT

I need to load input flatfile contents into some table.

My flat file contains data like:
---------------------------------------
0 90 0 0 0 512000 7
0 0 30 0 0 6.29146e+06 30

in the above data all the columns are numbers only. But From the source file we are receiving data in the sixth column in exponential format (which is out of control from us) now I need to convert this into number and load into table (Because my Table column data type is number).

Re: Number conversion

Matti_Kurkela — Tue, 14 Dec 2010 13:16:26 GMT

What is this "table" supposed to be?

If this is a table in a database, what's the name and version of the database engine? (Oracle 10g? MySQL 5.1? Something else?)

In general, I'd search the database documentation for functions like TO_NUMBER(), CONVERT() or CAST(). Without knowing the name of the database engine, it is hard to be more specific.

MK

Re: Number conversion

Gopi Kishore m — Tue, 14 Dec 2010 13:18:45 GMT

My Database is Oracle10g

Re: Number conversion

James R. Ferguson — Tue, 14 Dec 2010 14:09:42 GMT

Hi :

One way:

# perl -ne 's{(\d+\.?\d+[eE][+-]\d+)}{sprintf "%.0f",$1}ge;print' file

Using your input this produces:

0 90 0 0 0 512000 7
0 0 30 0 0 6291460 30

Regards!

...JRF...

Re: Number conversion

James R. Ferguson — Tue, 14 Dec 2010 14:13:55 GMT

Hi (again) Gopi:

You also have unevaluated (answers without points assigned) an your earlier threads:

http://forums.itrc.hp.com/service/forums/questionanswer.do?threadId=1458759

When you are satisfied with the help that you have received, please read this link about points assignments:

http://forums.itrc.hp.com/service/forums/helptips.do?#28

Regards!

...JRF...

Re: Number conversion

Gopi Kishore m — Tue, 14 Dec 2010 14:19:33 GMT

ThankQ James (JRF), It works...
..

Sure from nowonwards I will try to assign the points aswell.

As I am new to this forum, I dont know the benifits for assign points.

Anyways thanQ... :-)
Gopi

Re: Number conversion

James R. Ferguson — Tue, 14 Dec 2010 14:31:35 GMT

Hi Gopi:

> Sure from nowonwards I will try to assign the points aswell.

Thanks. You can go back (using your profile) and find those threads where you didn't assign points and evaluate those too.

The value to assigning points is two-fold: (1) to say "thanks"; and (2) to flag the discussion and its contributions for future viewers to find what solved *your* problem and therefore what might be helpful to them.

Regards!

...JRF...

Re: Number conversion

James R. Ferguson — Tue, 14 Dec 2010 15:34:01 GMT

Hi (again):

Oops, the original post I made failed to cover cases like "3e+3" (without a fractional part). Use:

# perl -ne 's{(\d+(?:\.\d+)?[eE][+-]\d+)}{sprintf "%.0f",$1}ge;print' file

...JRF...

Re: Number conversion

Dennis Handly — Wed, 15 Dec 2010 08:32:38 GMT

Assuming you don't have any fractions, you can also use this awk:
awk '
BEGIN { OFMT="%.0f" }
{
for (i=1; i < NF; ++i)
printf "%.0f ", $i
print $NF
} ' file