Visit Our UNIX and Linux User Community


How do I extract specific column in multiple csv files?


 
Thread Tools Search this Thread
Top Forums UNIX for Beginners Questions & Answers How do I extract specific column in multiple csv files?
# 1  
Old 06-06-2019
How do I extract specific column in multiple csv files?

file1:
Code:
Name,Threshold,Curr Samples,Curr Error%,Curr ART
GETHome,100,21601,0.00%,47
GETregistry,100,21592,0.00%,13
GEThomeLayout,100,30466,0.00%,17

file2:
Code:
Name,Threshold,Curr Samples,Curr Error%,Curr ART
GETHome,100,21601,0.00%,33
GETregistry,100,21592,0.00%,22
GETPublish,100,21583,0.00%,110

file3:
Code:
Name,Threshold,Curr Samples,Curr Error%,Curr ART
GETHome,100,21601,0.00%,40
GETPublish,100,21583,0.00%,144
GEThomeLayout,100,30466,0.00%,251

similarly i have 21 csv files.i need to extract specific coloumn from all csv files and print in to a csv file.

if i mention Curr ART
example output:
Code:
Name,Curr ART,Curr ART,Curr ART,
GETHome,47,33,40
GETregistry,13,22,NA
GEThomeLayout,17,NA,251
GETPublish,NA,110,144

can anyone help me on this..!
# 2  
Old 06-06-2019
Try
Code:
awk -F, '
NR == 1         {for (i=1; i<=NF; i++) if ($i == SRCH) COL=i
                 HD = $1
                }
FNR == 1        {FCNT++
                 HD = HD OFS SRCH
                 next
                }
! EX[$1]++      {NM[++ROWCNT] = $1
                }
                {T[$1,FCNT] = $COL
                }
END             {print HD
                 for (i=1; i<=ROWCNT; i++)      {TMP = NM[i]
                                                 printf "%s", TMP
                                                 for (j=1; j<=FCNT; j++) printf ",%s", T[TMP,j]?T[TMP,j]:"NA" 
                                                 printf RS
                                                }
                }
' SRCH="Curr ART" OFS=, file[1-3]
Name,Curr ART,Curr ART,Curr ART
GETHome,47,33,40
GETregistry,13,22,NA
GEThomeLayout,17,NA,251
GETPublish,NA,110,144

This User Gave Thanks to RudiC For This Post:
# 3  
Old 06-06-2019
Thanks its working for three filesSmilie ..need to check for my 24 files..i will check it and let u know..!
# 4  
Old 07-01-2019
Hi Rudic ..
i have 24 files and i have tried giving

Code:
' SRCH="Curr ART" OFS=, file[1-24]

it is not working then
it is working for only [1-9]
can u help me for 24 files??

EDITED:
i tried like complete command like file1 file2 file3........file24
the it worked

Code:
' SRCH="Curr ART" OFS=, file1 file2 file3 file4............file24


Last edited by Raghuram717; 07-01-2019 at 05:24 AM..
# 5  
Old 07-01-2019
In shell "pattern matching",
Quote:
[...] Matches any one of the enclosed characters. A pair of characters separated by a hyphen denotes a range expression; any character that falls between those two characters, inclusive, using the current locale's collating sequence and character set, is matched.
(cf man bash). So, [1-24] matches 1, 2, and 4. For your case, you might want to try "brace expansion" (cf man bash):
Code:
echo file{1..24}
file1 file2 file3 file4 file5 file6 file7 file8 file9 file10 ... file23 file24

so, in lieu of explicitly listing each and every file name, have the shell do it for you.
This User Gave Thanks to RudiC For This Post:
# 6  
Old 07-01-2019
And, BTW, you might want to consider combining the solutions in this thread with the ones in your other thread?
This User Gave Thanks to RudiC For This Post:
# 7  
Old 07-04-2019
Just using standard shell features, one could also try:
Code:
' SRCH="Curr ART" OFS=, file[1-9] file1[0-9] file2[0-4]

or, assuming that there aren't any other filexx files where xx are decimal digits:
Code:
' SRCH="Curr ART" OFS=, file[1-9] file[12][0-9]

or, if there aren't any other files with names starting with file:
Code:
' SRCH="Curr ART" OFS=, file? file??

.

Previous Thread | Next Thread
Test Your Knowledge in Computers #336
Difficulty: Easy
sed -e 'r' /etc/passwd will recover lost password on a Unix system.
True or False?

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Row bind multiple csv files having different column headers

All, I guess by this time someone asked this kind of question, but sorry I am unable to find after a deep search. Here is my request I have many files out of which 2 sample files provided below. File-1 (with A,B as column headers) A,B 1,2 File-2 (with C, D as column headers) C,D 4,5 I... (7 Replies)
Discussion started by: ks_reddy
7 Replies

2. Shell Programming and Scripting

Extracting data from specific rows and columns from multiple csv files

I have a series of csv files in the following format eg file1 Experiment Name,XYZ_07/28/15, Specimen Name,Specimen_001, Tube Name, Control, Record Date,7/28/2015 14:50, $OP,XYZYZ, GUID,abc, Population,#Events,%Parent All Events,10500, P1,10071,95.9 Early Apoptosis,1113,11.1 Late... (6 Replies)
Discussion started by: pawannoel
6 Replies

3. Shell Programming and Scripting

Sum values of specific column in multiple files, considering ranges defined in another file

I have a file (let say file B) like this: File B: A1 3 5 A1 7 9 A2 2 5 A3 1 3 The first column defines a filename and the other two define a range in that specific file. In the same directory, I have also three more files (File A1, A2 and A3). Here is 10 sample lines... (3 Replies)
Discussion started by: Bastami
3 Replies

4. Shell Programming and Scripting

How to print multiple specific column after a specific word?

Hello.... Pls help me (and sorry my english) :) So I have a file (test.txt) with 1 long line.... for example: isgc jsfh udgf osff 8462 error iwzr 653 idchisfb isfbisfb sihfjfeb isfhsi gcz eifh How to print after the "error" word the 2nd 4th 5th and 7th word?? output well be: 653 isfbisfb... (2 Replies)
Discussion started by: marvinandco
2 Replies

5. Shell Programming and Scripting

Insterting column in csv from multiple files

Hello, I have a spec file that contains a lot of strings that looks like this: PC DELL OptiPlex 3010MT i3 3220/2GB/500GB/DVD-RW/FREE DOS / 5Y NBD Intel i3 3220 (Dual Core, 3.30GHz, 3MB, w/ HD2500 Graphics), 2GB (1x2GB) DDR3 PC3-1600MHz, 500GB HDD SATA III 7200rpm, DVD+/-RW (16x),... (9 Replies)
Discussion started by: g9100
9 Replies

6. Shell Programming and Scripting

Converting Single Column into Multiple rows, but with strings to specific tab column

Dear fellows, I need your help. I'm trying to write a script to convert a single column into multiple rows. But it need to recognize the beginning of the string and set it to its specific Column number. Each Line (loop) begins with digit (RANGE). At this moment it's kind of working, but it... (6 Replies)
Discussion started by: AK47
6 Replies

7. Shell Programming and Scripting

averaging specific column of multiple files

Hi all, I'm needing help again on scripting. I have weekly files with 3 columns, and I need to do monthly averaging on the values on column 3, the file naming convention is as follows: 20000105.u- 2000:year 01:month 05:day 20000112.u 20000119.u 20000126.u 20000202.u 20020209.u I need to... (15 Replies)
Discussion started by: ida1215
15 Replies

8. Shell Programming and Scripting

Combine Multiple text or csv files column-wise

Hi All I am trying to combine columns from multiple text files into a single file using paste command but the record length being unequal in the different files the data is running over to the closest empty cell on the left. Please see below. What can i do to resolve this ? File 1 File... (15 Replies)
Discussion started by: venky_ibm
15 Replies

9. UNIX for Dummies Questions & Answers

How to extract one column from csv file in perl?

Hi everyone, i am new to perl programming, i have a problem in extracting single column from csv file. the column is the 20th column, please help me.. at present i use this code #!C:/perl/bin use warnings; use strict; my $file1 = $ARGV; open FILE1, "<$file1" or die "Can't... (13 Replies)
Discussion started by: kvth
13 Replies

10. Shell Programming and Scripting

extract csv based on column value

Hi I have a csv file which is below A,5 B,6 C,10 D,7 I want the values who's second column is greater than 7 say C,10 D,7 Help me please... Thanks, Maruth (3 Replies)
Discussion started by: maruthavanan
3 Replies

Featured Tech Videos