0

The following is the sample csv file:

12354506.0,4
13129229.0,4
815612,5
7624107.0,5
6056548.0,5

The trailing zero in the first column is formatted to hole number on CSV VIEWER.

Is there any option to have trailing zero?

I was able to have trailing zero by prefix single quotes to the first column. But it looks odd with single quotes.

'12354506.0,4
'13129229.0,4
'815612,5
'7624107.0,5
'6056548.0,5 
  • This is likely a feature of your CSV viewer software (you don't mention what the software is). It may have the option to show a configurable number of decimal places for numeric data. – Kusalananda Jul 27 '18 at 6:38
  • @Kusalananda Yes, we can configure the decimal places, but it create a conflict in 3rd row of my input file since it doesn't have decimal value. – user302504 Jul 27 '18 at 6:51
  • Why is this tagged sendmail? – Rob Jul 27 '18 at 11:25
0

Since the CSV viewer software seems to be able to detect floating point numbers by the inclusion of a fractional part, the numbers must be converted to have at least one decimal.

One way of doing this with awk:

awk -F, -vOFS="," '$1 == int($1) { $1 = sprintf("%.1f", $1) } 1' file >newfile

This would test the first comma-separated field to see if it was a whole number. In that case, it would rewrite the field as n.0 (where n is the original number). The trailing 1 in the code would cause all lines to be outputted.

The result in written to the new file newfile.

A similar solution using sed:

sed 's/^\([0-9]*\),/\1.0,/' file >newfile

Here, we match any number of digits followed immediately by a comma, and replace that with the same digits, .0, and the comma. This would ignore the numbers that already have fractional parts, but would add .0 to numbers that don't have that.

0

It's purely behavior of CVS viewer which you are using. I'm able to replicate the same issue with MS Excel.

awk -F "," '{print "=\""$1"\","$2}' test.csv > test3.csv
  • = will force the data to be text.

To edit the file.

echo "$(awk -F "," '{print "=\""$1"\","$2}' test.csv)" > test.csv
  • Is it possible to edit the file, in spite of creating the new file... – user302504 Jul 27 '18 at 7:28
  • @ababkren try the second code. – msp9011 Jul 27 '18 at 7:34
0

with reference to the answer.

 awk -F ","  'BEGIN {OFS = FS}{$1="=\""$1"\""; print}' test.csv

we may not know how many fields in a csv. Its quite difficult to declare each field in to print. So better we can edit the field which is required.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy