Reduce length of specific column in delimited text file

up vote
1
down vote

favorite

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.

edited Nov 11 '14 at 18:19

DisplayName

4,29584273

asked Nov 11 '14 at 14:35

cmart2112

607

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

add a commentÂ |Â

up vote
1
down vote

favorite

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.

edited Nov 11 '14 at 18:19

DisplayName

4,29584273

asked Nov 11 '14 at 14:35

cmart2112

607

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

add a commentÂ |Â

up vote
1
down vote

favorite

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.

edited Nov 11 '14 at 18:19

DisplayName

4,29584273

asked Nov 11 '14 at 14:35

cmart2112

607

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.

text-processing scripting columns

edited Nov 11 '14 at 18:19

DisplayName

4,29584273

asked Nov 11 '14 at 14:35

cmart2112

607

edited Nov 11 '14 at 18:19

DisplayName

4,29584273

asked Nov 11 '14 at 14:35

cmart2112

607

edited Nov 11 '14 at 18:19

DisplayName

4,29584273

edited Nov 11 '14 at 18:19

DisplayName

4,29584273

edited Nov 11 '14 at 18:19

DisplayName

4,29584273

asked Nov 11 '14 at 14:35

cmart2112

607

asked Nov 11 '14 at 14:35

cmart2112

607

asked Nov 11 '14 at 14:35

cmart2112

607

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

add a commentÂ |Â

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

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

add a commentÂ |Â

4 Answers
4

active

oldest

votes

up vote
2
down vote

accepted

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

awk -F, 'print $1","$2","$3","$4","substr($5,1,5) ' file.csv

will output this:
123456789,123456789,123456789,123456789,12345 123456789,123456789,123456789,123456789,22345 123456789,123456789,123456789,123456789,32345 123456789,123456789,123456789,123456789,42345

answered Nov 11 '14 at 14:56

a21

31413

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

add a commentÂ |Â

up vote
1
down vote

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.

answered Nov 11 '14 at 14:51

Anthon

59.1k1796160

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

add a commentÂ |Â

up vote
1
down vote

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)'

answered Nov 11 '14 at 15:19

garethTheRed

23.3k35978

add a commentÂ |Â

up vote
0
down vote

if there 100 columns i believe listing column like this would be hectic.

is there any other way ,like i have 100 column in file and reduce size of 70th column in unix file which delimeted one(~|)

answered 6 mins ago

Loknath Shaw

New contributor

add a commentÂ |Â

Your Answer

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "106"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
convertImagesToLinks: false,
noModals: false,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);

);

draft saved

draft discarded

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2funix.stackexchange.com%2fquestions%2f167340%2freduce-length-of-specific-column-in-delimited-text-file%23new-answer', 'question_page');

);

Post as a guest

Name

4 Answers
4

active

oldest

votes

4 Answers
4

active

oldest

votes

up vote
2
down vote

accepted

answered Nov 11 '14 at 14:56

a21

31413

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

add a commentÂ |Â

up vote
2
down vote

accepted

answered Nov 11 '14 at 14:56

a21

31413

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

add a commentÂ |Â

up vote
2
down vote

accepted

answered Nov 11 '14 at 14:56

a21

31413

answered Nov 11 '14 at 14:56

a21

31413

answered Nov 11 '14 at 14:56

a21

31413

answered Nov 11 '14 at 14:56

a21

31413

answered Nov 11 '14 at 14:56

a21

31413

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

add a commentÂ |Â

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

You can do it more portable awk -F, -v OFS="," '$5=substr($5,1,5)1'
â€“Â Costas
Nov 11 '14 at 15:11

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

add a commentÂ |Â

up vote
1
down vote

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.

answered Nov 11 '14 at 14:51

Anthon

59.1k1796160

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

add a commentÂ |Â

up vote
1
down vote

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.

answered Nov 11 '14 at 14:51

Anthon

59.1k1796160

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

add a commentÂ |Â

up vote
1
down vote

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.

answered Nov 11 '14 at 14:51

Anthon

59.1k1796160

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.

answered Nov 11 '14 at 14:51

Anthon

59.1k1796160

answered Nov 11 '14 at 14:51

Anthon

59.1k1796160

answered Nov 11 '14 at 14:51

Anthon

59.1k1796160

answered Nov 11 '14 at 14:51

Anthon

59.1k1796160

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

add a commentÂ |Â

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

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

add a commentÂ |Â

up vote
1
down vote

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)'

answered Nov 11 '14 at 15:19

garethTheRed

23.3k35978

add a commentÂ |Â

up vote
1
down vote

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)'

answered Nov 11 '14 at 15:19

garethTheRed

23.3k35978

add a commentÂ |Â

up vote
1
down vote

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)'

answered Nov 11 '14 at 15:19

garethTheRed

23.3k35978

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)'

answered Nov 11 '14 at 15:19

garethTheRed

23.3k35978

answered Nov 11 '14 at 15:19

garethTheRed

23.3k35978

answered Nov 11 '14 at 15:19

garethTheRed

23.3k35978

answered Nov 11 '14 at 15:19

garethTheRed

23.3k35978

add a commentÂ |Â

up vote
0
down vote

if there 100 columns i believe listing column like this would be hectic.

is there any other way ,like i have 100 column in file and reduce size of 70th column in unix file which delimeted one(~|)

answered 6 mins ago

Loknath Shaw

New contributor

add a commentÂ |Â

up vote
0
down vote

if there 100 columns i believe listing column like this would be hectic.

is there any other way ,like i have 100 column in file and reduce size of 70th column in unix file which delimeted one(~|)

answered 6 mins ago

Loknath Shaw

New contributor

add a commentÂ |Â

up vote
0
down vote

if there 100 columns i believe listing column like this would be hectic.

is there any other way ,like i have 100 column in file and reduce size of 70th column in unix file which delimeted one(~|)

answered 6 mins ago

Loknath Shaw

New contributor

if there 100 columns i believe listing column like this would be hectic.

is there any other way ,like i have 100 column in file and reduce size of 70th column in unix file which delimeted one(~|)

answered 6 mins ago

Loknath Shaw

New contributor

answered 6 mins ago

Loknath Shaw

New contributor

answered 6 mins ago

Loknath Shaw

answered 6 mins ago

Loknath Shaw

New contributor

Loknath Shaw is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

add a commentÂ |Â

draft saved

draft discarded

draft saved

draft discarded

Post as a guest

Name

搜尋此網誌

mjhjmtu