Number conversion

[Gopi Kishore m]

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).

[Matti_Kurkela]

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

[Gopi Kishore m]

My Database is Oracle10g

[James R. Ferguson · Accepted solution]

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...

[James R. Ferguson]

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...

[Gopi Kishore m]

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

[James R. Ferguson]

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...

[James R. Ferguson]

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...

[Dennis Handly]

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