If you are at the IMP or IMBA then just log in with your username/password, no registration needed. GMI and MFPL members please use firstname.lastname as your username, and your professional e-mail address when registering.

Welcome to the BioComp Knowledge Hub! You can ask questions here related to bioinformatics, statistics, computational biology and similar subjects. You can also answer questions and rate other users' contributions.

Frequently Asked Questions

trailing commas at end of csv file

0 votes
1 view

Hi!

I get trailing commas at the end of a .csv files when saving excels files with either libre office or excel2010.

I know the problem.

excel has something like a used range.

so if i have

column1  column2 column3

a1            xxx          yyy

a2            zzz

the saved csv is

a1,xxx,yyy

a2,zzz,,

 however i don't want the second comma in a2.

I googled at lot, but found no easy solution to this problem except writting a makro.

also this awk command is not working awk -F, '{sub(/ *$/,"");$0=$0 ","}1' OFS=,

 

Thanks and best regards Georg.

asked Nov 25, 2014 in Bioinformatics by mlynekg9 (120 points)
edited Nov 26, 2014 by mlynekg9

2 Answers

+1 vote
One-liner example to get rid of the trailing comma in UNIX:

echo "a2,zzz,," | sed -E 's/,$//'

will print a2,zzz,

Explanation: the s/ command of sed looks for a comma at the end of the line and replaces it with nothing.

If you don't want commata at the end at all, you may try:

echo "a2,zzz,," | sed -E 's/,+$//'

this will print a2,zzz

You can cat and pipe a CSV file to sed or give the filename as an argument. Cheers, AA
answered Dec 5, 2014 by aszodi (590 points)
0 votes
Hi,

a small bash script will do the trick. (not sure if this is what you call a "makro").

 

#!/bin/bash

for line in $(cat "$1"); do
    lastchar=${line: -1}
    while [ "$lastchar" == "," ]; do
        len=$(( ${#line}-1 ))    
        line=${line:0:$len}
        lastchar=${line: -1}
    done
    echo "$line" >> "$2"
done
answered Dec 2, 2014 by cri (420 points)
...