0

I have a CSV file looks like this:

id,country
12343,China
23455,Japan
47,Singapore
374,Australia
6572e,United Kingdom
  • Firstly I want to count the total number of lines whose column id length is greater than 3
  • Next I want to remove the lines whose id length is not equal to 5, and whose id contains an alphabetic character
  • Finally I want to create a new file to store the result

I have tried awk method but I get this result:

mylaptop@MacBook-Pro ~ % awk 'length($1)!=19 {c++} END {print c+0}' myfile
quote>

If I type the command like this:

mylaptop@MacBook-Pro ~ % awk 'length($1)!=19 {c++} END {print c+0}' <myfile>

It will show:

zsh: parse error near `\n'

The expected output should be like in the Mac terminal:

mylaptop@MacBook-Pro ~ % awk 'length($1)!=19 {c++} END {print c+0}' myfile
4

and save all the changes in a file.

I am new to linux, so can you show the full commands for 3 questions?

Improve this question
13
  • 2
    Edit your question and add what you tried and where it went wrong. Also, add clarity regarding what you want to do. You state that you want to count the values under column id that have more than three characters but then you want to remove the lines where the number if characters is not equal to five. Which one do you want? Add the expected output to your question to clarify this. Commented Sep 25, 2021 at 15:36
  • Also, "not equal to 5 and containing character". Do you mean containing a non-numeric character, like "123q5" ? I note your input data contains a value which is length 5 and contains e -- what does that do ? Do you want the two conditions to be and or or ? Commented Sep 25, 2021 at 16:43
  • By the way, it is not CSV, it appears to be tab-separated. Commented Sep 25, 2021 at 16:44
  • @NasirRiley I have use a awk command like the answer below, but it doesn't show the result. It shows 'quote>'. I don't know what happened. Does it have something to do with my laptop? I am using a Mac. Commented Sep 25, 2021 at 18:34
  • 1
    Why does your question mention 3 and 5, but your code says 19? Why should that work? Commented Sep 25, 2021 at 21:22

2 Answers 2

Reset to default
2

This seems like homework!

Count lines where length of column 1 is greater than 3

awk 'length($1)>3 {c++} END {print c+0}'

Discard lines where length of column 1 is not 5

awk 'length($1)==5'

Discard lines where length of column 1 is not 5 and it contains a non digit

awk 'length($1)==5 && $1 !~ /[^0-9]/'

Use the shell to direct the output to the new file.

Edit: The file originally appeared to be a tab separated file, now it has been edited to be a CSV. This means the solutions need to be

awk -F, 'length($1)>3 {c++} END {print c+0}'
awk -F, 'length($1)==5'
awk -F, 'length($1)==5 && $1 !~ /[^0-9]/'
Improve this answer
5
  • thank you so much! I have a qustion about using the awk commands. After I type in the command, it doesn't give the output, it will display a 'quote>' instead of the result. Why will it happen? Commented Sep 25, 2021 at 18:32
  • I have updated my question. Why won't I get the result? Commented Sep 25, 2021 at 18:50
  • 1
    @YKILYCHAN > in a shell prompt is normally PS2 (secondary prompt). I don't know MacBook or zsh, but if it prompts with quote> I would guess it is asking for a continuation line because you have an unpaired quote in a previous line. Commented Sep 25, 2021 at 21:28
  • @YKILYCHAN I would guess you aren't providing the input file name on the command line and are running awk 'script' instead of awk 'script' file so awk is waiting for you to type some lines of input. Commented Sep 27, 2021 at 16:55
  • 1
    zsh promts you with quote> if you are inside a single quoted string. Make sure that you use the same character to start and finish your awk program, usually the single quote in the lower right of the keyboard rather than say the backquote. Commented Sep 27, 2021 at 22:38
0

To count the number of lines whose first comma-delimited field is longer than three characters, delete the header and any line whose first field is not four characters long or longer, then count the remaining lines.

sed -e '1d' -e '/^[^,]\{4,\},/!d' file | wc -l

The d command deletes the line addressed by the preceding address (either a line number or a regular expression). If the preceding condition is not fulfilled, !d triggers the deletion.

The expression ^[^,]\{4,\}, matches four or more non-commas at the start of the line, followed by a comma.

Remove all lines whose first field is not precisely five digits long while retaining the header and saving the result to a new file.

sed -e '1b' -e '/^[[:digit:]]\{5\},/!d' file >newfile

We use the b command to branch to the end of the script for the first input line, skipping forward to the next cycle. Doing this ensures that the header line is printed rather than deleted.

The expression ^[[:digit:]]\{5\}, matches exactly five digits at the start of the line, followed by a comma.

Improve this answer

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.