1

I need for the 5th column in a delimited text file to be reduced to just the first 5 characters. All other columns must remain unedited.

Input:

file1.txt    column1      column2      column3     column4      column5
123456789    123456789    123456789   123456789    123456789

I would like the output to look like:

output.txt    column1      column2      column3     column4      column5
123456789    123456789    123456789   123456789    12345

Note: I happen to use commas as the delimiter.

  • 1
    This doesn't look like a CSV file as commas/semicolons are missing. Are those tabs between the values?. Can this table have quoted values with newlines like a CSV file? In general you cannot use text-processing tools on CSV, you need a real parser except for not so realistic simple cases. Never more than 5 columns? If not what about alignment? – Anthon Nov 11 '14 at 14:38
  • I can change the delimiter to whatever is needed. I just typically use commas. The example I provided is obviously just a visual representation of what I am looking for, not an actual CSV. If it would make the question easier, I could easily update the table, but I guess I assumed someone would have a quick command off the top of their head. – cmart2112 Nov 11 '14 at 14:41
  • For real CSV files, you need to use Perl or Python with an appropriate library, otherwise you'll break the file. If this is a simple subset of CSV (no quoted values, no newlines in values, etc.) you should clearly indicate that, then simpler solutions with awk/sed etc can be given. – Anthon Nov 11 '14 at 14:46
2

If file.csv looks like this:

123456789,123456789,123456789,123456789,123456789
123456789,123456789,123456789,123456789,223456789
123456789,123456789,123456789,123456789,323456789
123456789,123456789,123456789,123456789,423456789

Then, you can do:

$ awk -F, '{print $1","$2","$3","$4","substr($5,1,5) }' file.csv
123456789,123456789,123456789,123456789,12345
123456789,123456789,123456789,123456789,22345
123456789,123456789,123456789,123456789,32345
123456789,123456789,123456789,123456789,42345
  • 1
    You can do it more portable awk -F, -v OFS="," '{$5=substr($5,1,5)}1' – Costas Nov 11 '14 at 15:11
  • 1
    To pass 1 line with headers just modify awk -F, -v OFS="," 'NR>1{$5=substr($5,1,5)}1' – Costas Nov 11 '14 at 16:42
1

If all the values are simple (no quotes and newlines in a value) and if they are comma separated, like:

123456789,123456789,123456789,123456789,123456789

and if there are always 5 such values on a line, you can use sed:

sed '2,$s/\(.*\),\(.*\),\(.*\),\(.*\),\(.....\)\(.*\)/\1,\2,\3,\4,\5/' input

The 2,$ assumes you have a header that is comma separated as well (and that its fifth column should not be truncated), if that is not the case leave it out.

  • If all first columns have fields more then 5 symbol length you can make script more portable sed 's/\([^,]\{5\}\)[^,]*/\1/5' < input – Costas Nov 11 '14 at 14:59
  • To edit above variant to work with any length fields sed '2,$ s/\([^,]\{,5\}\)[^,]*/\1/5' < input – Costas Nov 11 '14 at 15:07
1

Or awk:

For white-space delimited:

awk 'NR==1 { print; next} {print $1, $2, $3, $4, substr($5,1,5)}'

For comma delimited:

awk 'BEGIN {FS=","} NR==1 { print; next} {print $1, $2, $3, $4, substr($5,1,5)}'

For slightly better CSV splitting and assuming GNU awk:

awk -vFPAT='[^,]*|"[^"]*"'  'NR==1 { print; next} {print $1, $2, $3, $4, substr($5,1,5)}'

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.