Login or Register to Ask a Question and Join Our Community


UNIX for Beginners Questions & Answers

Select and copy .csv files based on row and column number

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums UNIX for Beginners Questions & Answers Select and copy .csv files based on row and column number
# 1  
Old 02-25-2019
Select and copy .csv files based on row and column number
Dear UNIX experts,

I'm a command line novice working on a Macintosh computer (Bash shell) and have neither found advice that is pertinent to my problem on the internet nor in this forum.

I have hundreds of .csv files in a directory. Now I would like to copy the subset of files that contains exactly 6 rows and 4 columns to a new directory. Since the .csv files are indeed comma-separated, I found that
Code:
awk -F',' ' { print NF }' test.csv

yields a column of 6 4's for the wanted files but I do not know how to make use of this output to select and copy numerous files in a loop.

I would be very grateful for any advice and apologize if I missed that a similar question had already been asked on this forum.

Thank you,
Robert
# 2  
Old 02-25-2019
Welcome to the forum.


I'm afraid it's not that easy and straightforward as you assumed. Still it's not a major problem. Try
Code:
awk -F',' '
FNR == 1        {if (NFOK && (OLDFNR == 6)) print "echo cp -v " OLDFN " new_dir/"
                 NFOK = 1
                }
NF != 4         {NFOK = 0
                }
                {OLDFNR = FNR
                 OLDFN  = FILENAME
                }
END             {if (NFOK && (OLDFNR == 6)) print  "echo cp -v " OLDFN " new_dir/"
                }
 ' *.csv | sh

This runs through all the .csv files in your current working dir, checks if each consistently has four fields in each line, and exactly 6 lines, and, for each match, prints a copy command into the pipe for the subsequently running shell.
When happy with what you see, you can remove the echo so the real copy is executed.
Last edited by RudiC; 02-25-2019 at 07:38 AM..
# 3  
Old 02-25-2019
Dear Rudi,

Many thanks for your suggestion. I'm sure I'm missing something stupid but this prompts the following error message:
Quote:
sh: line 4: syntax error near unexpected token `('
I've created the directory new_dir/ within the directory containing the .csv's. Was there anything else I had to do to adjust your code?

I should add: After removing the
Code:
echo

command, the script successfully copies a small number of correctly chosen .csv's before stopping and prompting the above error message.

Thanks again,
Robert
Last edited by rcsapo; 02-25-2019 at 11:35 AM..
# 4  
Old 02-25-2019
More info needed. Drop the | sh and post the printed result. You didn't need to stick to the "new_dir" - feel free to adapt to your situation; change to an entire different path if need be.
# 5  
Old 02-25-2019
OK, here it comes...
Code:
awk -F',' '
FNR == 1        {if (NFOK && (OLDFNR == 6)) print "echo cp -v " OLDFN " new_dir/"
                 NFOK = 1
                }
NF != 4         {NFOK = 0
                }
                {OLDFNR = FNR
                 OLDFN  = FILENAME
                }
END             {if (NFOK && (OLDFNR == 6)) print  "echo cp -v " OLDFN " new_dir/"
                }
 ' *.csv | sh

yields
Code:
cp -v file1.csv new_dir/
cp -v 497_BIA_riablo_2016-Jan-28.csv new_dir/
cp -v 502_BIA_riablo_2016-Mar-08.csv new_dir/
sh: line 4: syntax error near unexpected token `('
sh: line 4: `echo cp -v 503_BIA_riablo_2016-Jan-13_(1).csv new_dir/'

I suppose this might be related to the parentheses in the file name?

Code:
awk -F',' '
FNR == 1        {if (NFOK && (OLDFNR == 6)) print "echo cp -v " OLDFN " new_dir/"
                 NFOK = 1
                }
NF != 4         {NFOK = 0
                }
                {OLDFNR = FNR
                 OLDFN  = FILENAME
                }
END             {if (NFOK && (OLDFNR == 6)) print  "echo cp -v " OLDFN " new_dir/"
                }
 ' *.csv

runs through smoothly and prompts lines like the following for approximately 90 files, which I assume are all I have that meet the above conditions. Note that also the file
Code:
echo cp -v 503_BIA_riablo_2016-Jan-13_(1).csv new_dir/

ran through.

Code:
awk -F',' '
FNR == 1        {if (NFOK && (OLDFNR == 6)) print " cp -v " OLDFN " new_dir/"
                 NFOK = 1
                }
NF != 4         {NFOK = 0
                }
                {OLDFNR = FNR
                 OLDFN  = FILENAME
                }
END             {if (NFOK && (OLDFNR == 6)) print  " cp -v " OLDFN " new_dir/"
                }
 ' *.csv

runs through prompting something like
Code:
 cp -v 503_BIA_riablo_2016-Jan-13_(1).csv new_dir/

for the above ~90 files but does not copy anything.

By contrast,
Code:
awk -F',' '
FNR == 1        {if (NFOK && (OLDFNR == 6)) print " cp -v " OLDFN " new_dir/"
                 NFOK = 1
                }
NF != 4         {NFOK = 0
                }
                {OLDFNR = FNR
                 OLDFN  = FILENAME
                }
END             {if (NFOK && (OLDFNR == 6)) print  " cp -v " OLDFN " new_dir/"
                }
 ' *.csv | sh

does copy 3 files before stopping yielding the
Code:
sh: line 4: syntax error near unexpected token `('
sh: line 4: ` cp -v 503_BIA_riablo_2016-Jan-13_(1).csv new_dir/'

error message.
Last edited by RudiC; 02-25-2019 at 12:27 PM..
# 6  
Old 02-25-2019
Quote:
Originally Posted by rcsapo
... I suppose this might be related to the parentheses in the file name?
...
Yes, absolutely.


Try to quote the file names:

Code:
awk -F',' '
FNR == 1        {if (NFOK && (OLDFNR == 6)) print "echo cp -v \"" OLDFN "\" new_dir/"
                 NFOK = 1
                }
NF != 4         {NFOK = 0
                }
                {OLDFNR = FNR
                 OLDFN  = FILENAME
                }
END             {if (NFOK && (OLDFNR == 6)) print  "echo cp -v \"" OLDFN "\" new_dir/"
                }
' *.csv

I hope I didn't mess anything else up in my hurry...
These 2 Users Gave Thanks to RudiC For This Post:
rcsapo wisecracker
# 7  
Old 02-25-2019
Quote:
Try to quote the file names:
That did the trick! Thanks a huge lot, you've just made my life quite a bit easier!
Best, Robert
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Get maximum per column from CSV file, based on date column

Hello everyone, I am using ksh on Solaris 10 and I'm gathering data in a CSV file that looks like this: 20170628-23:25:01,1,0,0,1,1,1,1,55,55,1 20170628-23:30:01,1,0,0,1,1,1,1,56,56,1 20170628-23:35:00,1,0,0,1,1,2,1,57,57,2 20170628-23:40:00,1,0,0,1,1,1,1,58,58,2... (6 Replies)
Discussion started by: ejianu
6 Replies

2. 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

3. Shell Programming and Scripting

List files with number to select based on number

Hi experts, I am using KSH and I am need to display file with number in front of file names and user can select it by entering the number. I am trying to use following command to display list with numbers. but I do not know how to capture number and identify what file it is to be used for... (5 Replies)
Discussion started by: mysocks
5 Replies

4. Shell Programming and Scripting

Comparing Select Columns from two CSV files in UNIX and create a third file based on comparision

Hi , I want to compare first 3 columns of File A and File B and create a new file File C which will have all rows from File B and will include rows that are present in File A and not in File B based on First 3 column comparison. Thanks in advance for your help. File A A,B,C,45,46... (2 Replies)
Discussion started by: ady_koolz
2 Replies

5. Shell Programming and Scripting

Read in 2-column CSV, output many files based on field

Is there a way to read in a two-columned CSV file, and based on the fields in 1st column, output many different files? The input/output looks something like: input.csv: call Call Mom. call Call T-Mobile. go Go home. go Go to school. go Go to gas station. play Play music. play Play... (4 Replies)
Discussion started by: pxalpine
4 Replies

6. Shell Programming and Scripting

awk: Transpose csv row to column.

Hello, am I new to awk, and I am tryint to: INPUT FILE: "73423555","73423556","73423557","73423558","73423559" OUTPUT FILE: 73423555 73423556 73423557 73423558 73423559 My useless code so far: #!/bin/awk -F ',' BEGIN { i=0; } (8 Replies)
Discussion started by: drbiloukos
8 Replies

7. Shell Programming and Scripting

delete a row in csv file based on the date

Hi, I have a csv file with old data..i need to have only last 30 days from the current dateof data in the file.The fourth field in the file is a date field.i need to write a script to delete the old data by comparing the the fourth field with the (current date -30).I need to delete the rows in... (2 Replies)
Discussion started by: pals70423
2 Replies

8. Shell Programming and Scripting

How to print column based on row number

Hi, I want to print column value based on row number say multiple of 8. Input file: line 1 67 34 line 2 45 57 . . . . . . line 8 12 46 . . . . . . line 16 24 90 . . . . . . line 24 49 67 Output 46 90 67 (2 Replies)
Discussion started by: Surabhi_so_mh
2 Replies

9. Shell Programming and Scripting

Split single file into multiple files based on the number in the column

Dear All, I would like to split a file of the following format into multiple files based on the number in the 6th column (numbers 1, 2, 3...): ATOM 1 N GLY A 1 -3.198 27.537 -5.958 1.00 0.00 N ATOM 2 CA GLY A 1 -2.199 28.399 -6.617 1.00 0.00 ... (3 Replies)
Discussion started by: tomasl
3 Replies

10. Shell Programming and Scripting

Select Record based on First Column

Hi, I have a file with multiple records...and I have to select records based on first column....here is the sample file... I01,abc,125,1a2,LBVI02 I01,abc,126,2b5,LBVI02 I02,20070530,254,abc,LLBI01 I02,20070820,111,bvd,NGBI01 I need all records with I01 in first field in one file and... (8 Replies)
Discussion started by: mgirinath
8 Replies
Login or Register to Ask a Question