Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

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

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Read in 2-column CSV, output many files based on field
# 1  
Old 12-05-2012
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:

Code:
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 Beatles.

Code:
outputs 3 files:
call.xml
<value><tokens><token>Call</token><token>Mom</token></tokens></value>
<value><tokens><token>Call</token><token>T-Mobile</token></tokens></value>

go.xml
<value><tokens><token>Go</token><token>home</token></tokens></value>
<value><tokens><token>Go</token><token>to</token><token>school</token></tokens></value>
<value><tokens><token>Go</token><token>to</token><token>gas</token><token>station</token></tokens></value>

play.xml
<value><tokens><token>Play</token><token>music</token></tokens></value>
<value><tokens><token>Play</token><token>Beatles</token></tokens></value>

I'm stuck at the part of checking which items in 1st column are the same, then saving all those identical items along with their rows into a new list?? Is this possible to do with shell scripts, or would I need to use Python?
# 2  
Old 12-05-2012
try:
Code:
while read f l
do
  printf "<value>" >> $f.xml
  for w in ${l%[.]}
  do
     printf "<token>$w</token>" >> $f.xml
  done
  printf "</value>\n" >> $f.xml
done < input

This User Gave Thanks to rdrtx1 For This Post:
pxalpine
# 3  
Old 12-05-2012
Code:
sort input.csv | awk ' { print $1 } ' | uniq | while read file
do
 awk -v FL=$file '$1==FL {
  for (i=2;i<=NF;i++) {
   if(i==NF) printf "%s", "<tokens>"$i"</tokens></value>\n";
   if(i==2)  printf "%s", "<value><tokens>"$i"</tokens>"
   if(i!=NF && i!=2) printf "%s", "<tokens>"$i"</tokens>";
  }
 }' input.csv > $file.xml
done

# 4  
Old 12-06-2012
What if you wanted to add a line at the beginning and end of file?

Code:
call.xml
<head><body>
<value><tokens><token>Call</token><token>Mom</token></tokens></value>
<value><tokens><token>Call</token><token>T-Mobile</token></tokens></value>
</head></body>

# 5  
Old 12-06-2012
Code:
sort input.csv | awk ' { print $1 } ' | uniq | while read file
do
 echo "<head><body>" > $file.xml
 awk -v FL=$file '$1==FL {
  for (i=2;i<=NF;i++) {
   if(i==NF) printf "%s", "<tokens>"$i"</tokens></value>\n";
   if(i==2)  printf "%s", "<value><tokens>"$i"</tokens>"
   if(i!=NF && i!=2) printf "%s", "<tokens>"$i"</tokens>";
  }
 }' input.csv >> $file.xml
 echo "</body></head>" >> $file.xml
done

Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

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... (8 Replies)
Discussion started by: rcsapo
8 Replies

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

3. Shell Programming and Scripting

Match columns from two csv files and update field in one of the csv file

Hi, I have a file of csv data, which looks like this: file1: 1AA,LGV_PONCEY_LES_ATHEE,1,\N,1,00020460E1,0,\N,\N,\N,\N,2,00.22335321,0.00466628 2BB,LES_POUGES_ASF,\N,200,200,00006298G1,0,\N,\N,\N,\N,1,00.30887539,0.00050312... (10 Replies)
Discussion started by: djoseph
10 Replies

4. Shell Programming and Scripting

Read text between regexps and write into files based on a field in the text

Hi, I have a huge file that has data something like shown below: huge_file.txt start regexp Name=Name1 Title=Analyst Address=Address1 Department=Finance end regexp some text some text start regexp Name=Name2 Title=Controller Address=Address2 Department=Finance end regexp (7 Replies)
Discussion started by: r3d3
7 Replies

5. Shell Programming and Scripting

Pick the column value based on another column from .csv file

My scenario is that I need to pick value from third column based on fourth column value, if fourth column value is 1 then first value of third column.Third column (2|3|4|6|1) values are cancatenated. Main imp point, in my .csv file, third column is having price value with comma (1,20,300), it has... (2 Replies)
Discussion started by: Ganesh L
2 Replies

6. Shell Programming and Scripting

awk read column csv and search in other csv

hi, someone to know how can i read a specific column of csv file and search the value in other csv columns if exist the value in the second csv copy entire row with all field in a new csv file. i suppose that its possible using awk but i m not expertise thanks in advance (8 Replies)
Discussion started by: giankan
8 Replies

7. Shell Programming and Scripting

Matching and Merging csv data fields based on a common field

Dear List, I have a file of csv data which has a different line per compliance check per host. I do not want any omissions from this csv data file which looks like this: date,hostname,status,color,check 02-03-2012,COMP1,FAIL,Yellow,auth_pass_change... (3 Replies)
Discussion started by: landossa
3 Replies

8. Shell Programming and Scripting

Merging CSV fields based on a common field

Hi List, I have two files. File1 contains all of the data I require to be processed, and I need to add another field to this data by matching a common field in File2 and appending a corresponding field to the data in File1 based on the match... So: File 1:... (1 Reply)
Discussion started by: landossa
1 Replies

9. Shell Programming and Scripting

extract data in a csv file based on a certain field.

I have a csv file that I need to extract some data from depending on another field after reading info from another text file. The text file would say have 592560 in it. The csv file may have some data like so Field 1 Field2 Field3 Field4 Field5 Field6 20009756 1 ... (9 Replies)
Discussion started by: GroveTuckey
9 Replies

10. Shell Programming and Scripting

Read CSV column value based on column name

Hi All, I am newbie to Unix I ve got assignment to work in unix can you please help me in this regard There is a sample CSV file "Username", "Password" "John1", "Scot1" "John2", "Scot2" "John3", "Scot3" "John4", "Scot4" If i give the column name as Password and row number as 4 the... (3 Replies)
Discussion started by: JohnGG
3 Replies
Login or Register to Ask a Question