[SOLVED] Linux Command to get fields from CSV files

Issue

In csv files on Linux server, I have thousands of rows in below csv format

0,20221208195546466,9,200,Above as:2|RAN34f2fb:HAER:0|RAND8365b2bca763:FON:0|RANDa7a5f964900b:ION:0|

I need to get output from all the files on below format (2nd field ie 20221208195546466 and 5th field but value after Above as: and before first | ie 2 in above example )

output :

20221208195546466 , 2

Can anyone help me with linux command ?

Edit :

my attempts

I tried but it give field 5th value. How to add field 2 as well ?

cat *.csv | cut -d, -f5|cut -d'|' -f1|cut -d':' -f2|

EDIT : sorted result

Now I am using this command (based on Dave Pritlove answer ) awk -F'[,|:]' '{print $2", "$6}' file.csv. However, I have one more query, If I have to sort the output based on $6 ( value 2 in your example ) then how can i do it ? I want result should be displayed in sorted order based on 2nd output field. for ex :

20221208195546366, 20

20221208195546436, 16

20221208195546466, 5

2022120819536466, 2

Solution

Gnu awk allows multiple field separators to be set, allowing you to delimit each record at ,, |, and : at the same time. Thus, the following will fish out the required fields from file.csv:

awk -F'[,|:]' '{print $2", "$6}' file.csv

Tested on the single record example:

echo "0,20221208195546466,9,200,Above as:2|RAN34f2fb:HAER:0|RAND8365b2bca763:FON:0|RANDa7a5f964900b:ION:0|" | awk -F'[,|:]' '{print $2", "$6}'

output:

20221208195546466, 2

Answered By - Dave Pritlove

Answer Checked By - Pedro (WPSolving Volunteer)