UNIX for Beginners Questions & Answers

How to change the format of an excel from exponential to text through UNIX command


We have a pipe delimited file in which one particular A column is a combination of number+text and while converting into
excel using tr command it is generating a exponential data for the A column.

Kindly help to convert a file without generating the exponential value.

Input:

Code:

122911302435E289

Output:

Code:

1.22911302435E+300

UNIX script used for this convertion is shown below:

Code:

tr '|'  ',' < Input.csv > Final.csv

Moderator's Comments:

Please use CODE tags as required by forum rules!

Welcome to unix.com!

In order to avoid misunderstandings and/or misinterpretations, take utmose care in phrasing your requests and supplying representative sample data.
I don't think you can "convert... into excel using tr command". tr can translate one text file into another, e.g. making it easier to read for EXCEL by replacing | chars with commas. That would still be an .csv file, i.e. text with a special structure.
When you write
what is that output - certainly NOT the one by tr?

Did you consider the fact that within .csv- files, string/text fields are double quoted?

Hi.

Dong a Google search for the words in the title How to change the format of an excel from exponential to text through UNIX command? provides about 500K results, among them, the (very) similar thread csv - How do I stop Excel from converting value 0503E000 to 5.03E+02 automatically? - Super User along with some solutions.

Best wishes ... cheers, drl