Login or Register to Ask a Question and Join Our Community


UNIX for Beginners Questions & Answers

How to add following decimal point to a CSV value?

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums UNIX for Beginners Questions & Answers How to add following decimal point to a CSV value?
# 1  
Old 08-17-2018
How to add following decimal point to a CSV value?
hi there I being trying to figure out way to add " .0" to an integer value in a csv using sed or awk with out success. just as a work around for 2147483647 32 bit limitation that influxdb is currently having



the data base will accept values and work fine if it has the XXX.0 attached but if only and integer format it caps the database at 2147483647


I already have a shell script that reads the csv and converts the epoch to timestamp
ie

Code:
epoch,tx,rx
1534522909,6666,55555
1534522910,6667,55556
timestamp,tx,rx
17 -8- 2018 16:21:49,6666,55555
17 -8- 2018 16:21:49,6667,55556

I just want to add addition scripting to add a following decimal point and 0



Code:
timestamp,tx,rx
17 -8- 2018 16:21:49,6666.0,55555.0
17 -8- 2018 16:21:49,6667.0,55556.0


thank you for your time




Moderator's Comments:
Mod Comment Please use CODE tags as required by forum rules!
Last edited by RudiC; 08-17-2018 at 01:58 PM.. Reason: Added CODE tags.
# 2  
Old 08-17-2018
Welcome to the forum.


Please show your script so we can discuss enhancements / improvements to satisfy your request.
# 3  
Old 08-17-2018
Code:
  

#!/bin/bash
 usage()
{

  echo "usage: sysinfo_page [[-f file ] | [-h]]"
}
  while [ "$1" != "" ]; do
    case $1 in 
       -f | --file )           shift
                                filename1=$1
                                ;;
        -h | --help )           usage
                                exit
                                ;;
        * )                     usage
                                exit 1
    esac
    shift
done

cd /tmp/runfiles/tmp

awk 'BEGIN {FS=OFS=","}{$1=strftime("%Y-%m-%d %T",$1)}{print}' $filename1 > $filename1.tmp

sed 's/1970-01-01 00:00:00/timestamp/' $filename1.tmp  > /tmp/runfiles/CSV/$filename1
rm $filename1.tmp

currently to change my time I used this to change my timestamp it might not be perfect but it works well enough..


I suspect i can add in

Code:
sed "s/.*/&.0/" $filename1.tmp  to add in the .0 at the end

sed 's/rx.0/rx/' $filename1.tmp and to clean up the rx header

I am just not sure how to do the the TX value
Last edited by sash99; 08-17-2018 at 02:38 PM..
# 4  
Old 08-17-2018
Not sure I understand what the script is doing. All your efforts seem to be doable in one single awk script. Please post an input sample.
# 5  
Old 08-17-2018
the whole script is a secondary processor for a another script that sends CSV to influx. this particular one just converts collectd CVS to the proper format to work with csv-to-influx ( found on github) collectd writes in epoch and csv-to-influx wants timestamp and human readable the primary script gathers the MAC of a openwrt router based on BPI-R1 read a config file for CSV to be processed, passing the name to this script which converts the CSV to the proper format and then once it is converted the primary script builds the format to send the information to an influx database . and then it repeats the process over and over again every 30 seconds or so



input sample CSV: (Collectd)



Code:
epoch,tx,rx 
1534522909,6666,55555 
1534522910,6667,55556

output sample CSV; ( for csv-to-influx)

Code:
timestamp,tx,rx 
2018-8-17 16:21:49,6666.0,55555.0
2018-8-17 16:21:49,6667.0,55556.0

I think I figured out the awk command


Code:
awk -F, '{ $2=$2".0";$3=$3".0";}1' OFS=, test.file

not sure how you would combine all into one awk command

would not one need to fix the csv header after the processing


is there a way to ignore the 3rd colum if it does not exist as some CSV only use 2 columns
Last edited by sash99; 08-17-2018 at 06:59 PM..
# 6  
Old 08-17-2018
How about
Code:
awk -F, '
NR == 1         {$1 = "timestamp"
                 print
                 next
                }
                {cmd = "date +\"%F %T\" -d@" $1
                 cmd | getline $1
                 close (cmd)
                 $2 = $2 ".0"
                 if ($3) $3 = $3 ".0"
                }
1
' OFS=, file

# 7  
Old 08-17-2018
Hi RudiC


thank you kindly for the code it works nicely in normal linux arch but not in ARM arch


Code:

#!/bin/bash
 usage()
{

  echo "usage: sysinfo_page [[-f file ] | [-h]]"
}
  while [ "$1" != "" ]; do
    case $1 in 
       -f | --file )           shift
                                filename1=$1
                                ;;
        -h | --help )           usage
                                exit
                                ;;
        * )                     usage
                                exit 1
    esac
    shift
done
#cd /tmp/runfiles/tmp


awk -F, '
NR == 1         {$1 = "timestamp"
                 print
                 next
                }
                {cmd = "date +\"%F %T\" -d@" $1
                 cmd | getline $1
                 close (cmd)
                 $2 = $2 ".0"
                 if ($3) $3 = $3 ".0"
                }
1
' OFS=, $filename1

against and actual collectd CSV


Code:
epoch,rx,tx
1534477715.852,29429474,724680066
1534477720.854,29433801,724682823
1534477725.852,29437903,724685952
1534477730.852,29439120,724688043
1534477735.852,29446691,724691078
1534477740.852,29452624,724692719
1534477745.852,29457362,724696536
1534477750.852,29461215,724699053
1534477755.852,29465872,724701793
1534477760.853,29466906,724703284
1534477765.853,29471367,724705629
1534477770.852,29475009,724707836
1534477775.852,29480881,724711779
1534477780.852,29484515,724714005
1534477785.852,29488975,724716416
1534477790.854,29490008,724717907
1534477795.852,29495013,724720064
1534477800.852,29498611,724722541
1534477805.852,29502482,724724656
1534477810.852,29506624,724727067
1534477815.852,29510496,724729182
1534477820.852,29512315,724731298
1534477825.852,29516186,724733413
1534477830.852,29520328,724735824
1534477835.852,29525286,724739184
1534477840.852,29529670,724741993
1534477845.853,29533541,724744108
1534477850.852,29535210,724745937
1534477855.852,29539081,724748052
1534477860.852,29543353,724750463
1534477865.852,29549652,724752686
1534477870.852,29553840,724755247
1534477875.852,29557620,724756738
1534477880.852,29559465,724759244
1534477885.852,29566161,724762681
1534477890.852,29570349,724765242

under ARM I get invalid date whereas in X86_64 it works fine


how would I insert my above awk time shift into your code,, As I remember it was a bit of a bugger to get awk time to work correctly under ARM


awk 'BEGIN {FS=OFS=","}{$1=strftime("%Y-%m-%d %T",$1)

------ Post updated at 09:33 PM ------

hi RudiiC
I modified your code slightly



Code:
 awk -F, 

' NR == 1         {$1 = "timestamp" 

                  print    

              next  
 
               } 

                {$1=strftime("%Y-%m-%d %T",$1)  

                $2 = $2 ".0"  

                if ($3) $3 = $3 ".0" 

                } 1 ' OFS=, file

now it works in both ARM and X86_64


and again thank you kindly for your help
Last edited by sash99; 08-17-2018 at 09:18 PM..
These 2 Users Gave Thanks to sash99 For This Post:
jim mcnamara RudiC
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Advanced & Expert Users

Convert a numeric to 2 decimal point value

Hi , I have a file which contains text like A|Mau|Code|12|Detail B|Mau|Code|20|Header I want to write a command using awk which will output A|Mau|Code|12.00|Detail B|Mau|Code|20.00|Header I used a command like awk -F"|" {printf "%s|%s|%s|%.2f|%s",$1,$2,$3,$4,$5}' which does the... (4 Replies)
Discussion started by: LoneRanger
4 Replies

2. Shell Programming and Scripting

Add Delimiter after 2 decimal point for a particular column

Hi All, Please help for the following scenario where I want to add a delimiter after 2 decimal point integer. Original Line 1007937820§L§2016-12-19§000000002§2018-02-01§2050-12-01§00395§M§146713.57§00005.05000§762.59§00395§M§301223.0528§1165§2017-03-31 20:34:25 Expected Line... (12 Replies)
Discussion started by: ckwan123
12 Replies

3. Shell Programming and Scripting

Moving decimal point in a series of numbers

Hi, i need to move the decimal point from a file listing some numbers like this : 49899.50 49914.55 49894.48 49939.65 49879.44 49919.57 49934.62 49944.67 49954.72 (1 Reply)
Discussion started by: Board27
1 Replies

4. Shell Programming and Scripting

Check for decimal point and add it at the end if its not there using awk/perl

I have test.dat file with values given below: 20150202,abc,,,,3625.300000,,,,,-5,,,,,,,,,,,,,,,,,,,,,, 20150202,def,,,,32.585,,,,,0,,,,,,,,,,,,,,,,,,,,,, 20150202,xyz,,,,12,,,,,0.004167,,,,,,,,,,,,,,,,,,,,,, My expected output is shown below: ... (1 Reply)
Discussion started by: nvk_vinoth
1 Replies

5. Shell Programming and Scripting

awk decimal point numbers matching

Hi All, Can some one help me in identifying the significance of character "$" ,Which is playing critical role in matching decimal point numbers as below. $ echo "01#.01"|awk '{if ($0 ~ /^+(\.*)?$/) print}' $ echo "01#.01"|awk '{if ($0 ~ /^+(\.*)?/) print}' 01#.01 $ Regards, Rmkganesh. (3 Replies)
Discussion started by: rmkganesh
3 Replies

6. Shell Programming and Scripting

remove directories with two digits after decimal point

Hi everyone, I am new here and generally not experienced with linux. My question must be easy, but as for now I have no idea how to do it. I have lots of directories with numerical names, e.g. 50 50.1 50.12 etc. What I want is to leave directories with no or single digit after the decimal... (2 Replies)
Discussion started by: cabaciucia
2 Replies

7. Shell Programming and Scripting

Insert decimal point for numbers

Hi In Unix, I have a file with some numbers like : 45600 12345 I want to insert a decimal point for these numbers based on user input. If the input is 2, the numbers should be changed to 456.00 123.45 If the input is 3, the numbers should be changed to 45.600 12.345 Can... (2 Replies)
Discussion started by: yoursdivu
2 Replies

8. Shell Programming and Scripting

Comparing two numbers with decimal point

How to compare two numbers with decimal points ? Is there a way in bash to do this? (33 Replies)
Discussion started by: kinny
33 Replies

9. Shell Programming and Scripting

Insert a decimal point

Hi all. Using /bin/sh on an HPUX system. I want to place a decimal in the field 2 charactors from the right (yes, converting to currency). The field lengths are variable. Here's what I'm doing: exec < filename while read FIELD1 FIELD2 do FIELD1="echo $FIELD1 | sed 'syntax that will... (4 Replies)
Discussion started by: lyoncc
4 Replies

10. Shell Programming and Scripting

how to get rid of decimal point?

Hi, I have input with decimal point ( 9.99 ) for hours variable hrs. I need to change it to seconds. Here is my code: secs=`/usr/ucb/echo $hrs*3600 |bc` But I don't want to see the decimal point. I can use awk to trim it if there is one. I am just wondering if there is better standard... (2 Replies)
Discussion started by: cin2000
2 Replies
Login or Register to Ask a Question