0

I have four csv files, and I want to update a set of three, based on the values in the fourth file.

  1. file_1 contains names.
  2. file_2 their numerical ids.
  3. file_3 contains ids from file_1 and file_2, and values corresponding to each pair of id combinations.
  4. file_4 contains values from some new name combinations.

What I need to do is append file_4's new names to file_1 and file_2, and auto-incrementally create ids for them. Then insert the values in file_3 according to the new id combinations. The problem is quite simple as explained by the example below, what complicates it a little bit is the presence of comma delimited sub-fields like "1,2,3" in some of the csvs.

I need to use a script to achieve this, though I realize it may be easier to use sql.

file_1

nid,vname
1,name1
2,name2
3,name3

file_2

did,dname
1,"s1,s2,s3"
2,s4
3,"s5,s6"

file_3

nid,did,value
1,1,aa
1,2,gg
1,3,tt
2,1,aa
2,2,ag
2,3,at
3,1,aa
3,2,tt

file_4

new_name,new_dataset,value
name1,"s7,s8",aa
name2,"s9,s10",gg
name8,"s1,s2,s3",aa

So the three updated files should look like:

file_1_updated

nid,vname
1,name1
2,name2
3,name3
4,name8

file_2_updated

did,dname
1,"s1,s2,s3"
2,s4
3,"s5,s6"
4,"s7,s8"
5,"s9,s10"

file_3_updated

nid,did,value
1,1,aa
1,2,gg
1,3,tt
1,4,aa
2,1,aa
2,2,ag
2,3,at
2,5,gg
3,1,aa
3,2,tt
4,1,aa
6
  • 1
    And what exactly is the problem? How far did you come with the script? I have never found anything that became more easy by using SQL, so I cannot recommend going that route. – Anthon Oct 26 '14 at 21:14
  • The only route that I could think of is join file3 with file1 and file2 to get it in a similar structure as file4. But the challenge for me is : if join the files based on a comma (,) delimiter then it wouldnt recognize "s1,s2,s3" as a single column but three columns and it gives me a wrong combined file. – Hia Sen Oct 26 '14 at 22:52
  • @HiaSen It would be very simple task for awk if , would be "clean" field separator, but it is sometimes inside double quotes as in cvs files, what significantly complicates the job. Thus I recommend to use heavy machinery: python or perl. – jimmij Oct 26 '14 at 23:44
  • I have self taught myself some very basic shell scripts but I have little knowledge of perl or python, would you please help with some headstart on this? The file3 in the example is about 6gb in size. – Hia Sen Oct 27 '14 at 0:24
  • This definitely seems like a job for a SQL engine, but if you really need to do it in a shell script, you may want to check join from GNU Coreutils: gnu.org/software/coreutils/manual/…. – Vlad GURDIGA Oct 27 '14 at 6:32
3

Here's what you could do in plain bash, assuming the data is exactly as you posted. (Warning: modifies the files in-place. Be careful to take backups before testing.)

A couple of functions to manage the first two files:

next_id() {
  file="$1"
  # assumes file is sorted by id
  echo $(( $(tail -n 1 $file|cut -d, -f1) + 1 ))
}

Assuming file1 and file2 are sorted on the id column, this takes the first part of the last line and increments it by one, generating the next id.

find_or_create_id() {
  file="$1"
  item="$2"
  # check if we already have that item
  id=$(grep -m 1 ",$item$" "$file" 2> /dev/null)
  if [[ $? -ne 0 ]] ; then
    # generate the next id, append
    id=$(next_id "$file")
    echo "$id,$item" >> "$file"
  else
    # got it already
    id=${id/,*}
  fi
  echo "$id"
}

This looks for an item (vname or dname) in one of the first two files. If it is found, return the existing id. If not, generate the next id and store it back into the file.

The main part is pretty simple once you've got the substrings right:

while read line ; do
  col1=${line/,*}  # everything up to first ,
  col3=${line//*,} # everything after last ,
  col2=${line%,*}  # everything after first ,
  col2=${col2#*,}  # everything before last ,
  id1=$(find_or_create_id file1 "$col1")
  id2=$(find_or_create_id file2 "$col2")
  # don't insert duplicates
  if ! grep -m 1 -q "^$id1,$id2," file3 ; then
    echo "$id1,$id2,$col3" >> file3
  fi
done < <(tail -n +2 file4)

This will not insert into the last file in order, you'll get the new rows appended at the end.


That being said, if any of these files is non-trivial in size, a database would be appropriate. Look into SQLite if you don't want a database server.

Assuming you don't care about sequential ids (just that they're distinct), and you've put integer primary key autoincrement ids for table 1 and table 2 (plus unique keys on vname and dname), the update would look like (there are most likely more subtle ways than the insert or ignore approach):

insert or ignore into tab1(vname) select distinct vname from tab4;
insert or ignore into tab2(dname) select distinct dname from tab4;

insert or ignore into tab3(id1,id2,value)
  select tab1.id, tab2.id, tab4.value
  from tab4
  left join tab1 on tab1.vname = tab4.vname
  left join tab2 on tab2.dname = tab4.dname;

SQLite can deal with the " in your file just fine.

.separator ,
.import fileX tabX

does the Right Thing™, at least with the samples you have there.

Simple schema:

create table tab1 (id integer primary key autoincrement, vname text);
create unique index tab1_vname on tab1(vname);

create table tab2 (id integer primary key autoincrement, dname text);
create unique index tab2_dname on tab2(dname);

create table tab3 (id1 int, id2 int, value text,
                   constraint tab3_pk primary key(id1, id2));

create table tab4 (vname text, dname text, value text);
5
  • I can't believe OP didn't come back and say thanks for this. Nice work @Mat! – NotJustClarkKent Dec 2 '14 at 4:31
  • The bash code returns unwanted data for all three files, e.g. the output file1 contains 4,new_name, which was not the OPs spec. – agc Jun 28 '16 at 3:25
  • Please clarify @agc, item 4 to be added to the first file is in the spec – Mat Jun 28 '16 at 3:57
  • @Mat, the spec shows 4,name8, but the shell code above outputs 4,new_name, followed by 5,name8. – agc Jun 28 '16 at 4:02
  • Ok, fixed then I think – Mat Jun 28 '16 at 4:22
0

Here's 2/3 of the answer, using *nix software tools. file_1_updated:

head -n 1 file_1 ; \
{ tail -n +2 file_1 | cut -d ',' -f 2 ; \
  tail -n +2 file_4 | cut -d ',' -f 1 ; } | \
sort -n | uniq | nl -s ',' | tr -d ' '

Output:

nid,vname
1,name1
2,name2
3,name3
4,name8

file_2_updated:

head -n 1 file_2 ; \
{ tail -n +2 file_2 | cut -d ',' -f 2- ; \
  tail -n +2 file_4 | cut -d ',' -f 2- | \
  rev | cut -d ',' -f 2- | rev ; } | \
sort -n | uniq | nl -s ',' | tr -d ' '

Output:

did,dname
1,"s1,s2,s3"
2,s4
3,"s5,s6"
4,"s7,s8"
5,"s9,s10"

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.