I was provided with a CSV file, that in a single column, uses spaces to denote a thousands separator (eg. 11 000 instead of 11,000 or 11000). The other columns have useful spaces within them, so I need to only fix this one column.
My data:
Date,Source,Amount
1/1/2013,Ben's Chili Bowl,11 000.90
I need to get:
Date,Source,Amount
1/1/2013,Ben's Chili Bowl,11000.90
I have been trying awk, sed, and cut, but I can't get it to work.
dirty and quick:
awk -F, -v OFS="," '{gsub(/ /,"",$NF)}1'
example:
kent$ echo "Date,Source,Amount
1/1/2013,Ben's Chili Bowl,11 000.90"|awk -F, -v OFS="," '{gsub(/ /,"",$NF)}1'
Date,Source,Amount
1/1/2013,Ben's Chili Bowl,11000.90
$NF can be replaced with $n to make the change in any nth column specific
awk to use comma as the field separator for both input (-F,) and output (-v OFS=,). gsub(/ /,"",somestring) replaces spaces (that's a space between the /.../) with nothing at all ("") in the given string. NF is the number of (comma-delimited, thanks to -F,) fields, and $n is the nth field , so $NF means the last field on the line. The 1 at the end tells awk to do its default thing afterward, which is to print the modified fields back out, separated by OFS. So: delete all spaces in the last comma-separated field of each line.-v OFS=,.One possibility might be:
sed 's/\([0-9]\) \([0-9]\)/\1\2/'
This looks for two digits either side of a blank and keeps just the two digits. For the data shown, it would work fine. You can add a trailing g if you might have to deal with 11 234 567.89.
If you might have other columns with spaces between numbers, or not the first such column, you can use a similar technique/regex in awk with gsub() on the relevant field.
just with bash
$ echo "Date,Source,Amount
1/1/2013,Ben's Chili Bowl,11 000.90" |
while IFS=, read -r date source amount; do
echo "$date,$source,${amount// /}"
done
Date,Source,Amount
1/1/2013,Ben's Chili Bowl,11000.90
