Hello
It would be much simpler and faster to process csv text files than spreadsheet tables.
You may export your master table to master.csv and names table to names.csv and try the following ruby script to consolidate master.csv and names.csv and yield out.csv in desktop. Currently, output csv's field separator is comma and record separator is linefeed.
E.g.,
master.csv:
N,TAXON,PARENT,RANK
1,Animalia,(NULL),5
2,Chordata,Animalia,15
3,Vertebrata,Chordata,17
10,Mammalia,Vertebrata,25
names.csv:
N,TAXON,NAME_COMMON
1,Animalia,animal
2,Chordata,chordate
4,Mammalia,mammal
out.csv:
N,TAXON,PARENT,RANK,NAME_COMMON
1,Animalia,(NULL),5,animal
2,Chordata,Animalia,15,chordate
3,Vertebrata,Chordata,17,
10,Mammalia,Vertebrata,25,mammal
#!/bin/bash
cd ~/desktop || exit
/usr/bin/ruby -w <<'EOF' - master.csv names.csv > out.csv
#
# ARGV[0] = master csv
# ARGV[1] = names csv
#
# * input and output csv fields e.g.
# master csv fields = N, TAXON, ...
# names csv fields = N, TAXON, NAME_COMMON
# output csv fields = N, TAXON, ..., NAME_COMMON
# * field TAXON is the consolidation key.
# * Output csv records are sorted by field N in numerically ascending order.
# * internal hash table is created such that
# { pp[i][k] => [ pp[i] << qq[j][n] : qq[j][k] == pp[i][k] or '' ] : for i }
# where
# pp = 2d-array of master csv
# qq = 2d-array of names csv
# k = consolidation key column index (0-based) of master and names csv (k = 1 : TAXON)
# n = name column index (0-based) of names csv (n = 2 : NAME_COMMON)
# i = body row index (0-based) of master csv
# j = body row index (0-based) of names csv
# a[x][y] denotes data at coorditates [x][y] of array a
#
def csv2array(t, opts = {})
# string t : csv text
# hash opts : {:fs => fs, :rs => rs}
# string fs : field separator
# string rs : record separator
# * fs and rs may be string or compiled pattern
# return array : 2d-array representing data in csv
# quasi constants
_QQ = %["] # U+0022 QUOTATION MARK
_FS = %[\x1c] # U+001C INFORMATION SEPARATOR FOUR (FS)
_RS = %[\x1e] # U+001E INFORMATION SEPARATOR TWO (RS)
_US = %[\x1f] # U+001F INFORMATION SEPARATOR ONE (US)
# create unique escape sequences
_FS_, _RS_, _US_ = _FS, _RS, _US
_FS_ += _FS while t.include?(_FS_)
_RS_ += _RS while t.include?(_RS_)
_US_ += _US while t.include?(_US_)
# accept fs, rs
fs, rs = {:fs => %[,], :rs => %[\n]}.merge(opts).values_at(:fs, :rs)
# split text by _QQ and get arrays for odd & even elements - aa & bb (bb is quoted elements)
p = 0
aa, bb = t.split(_QQ).partition {|x| ( p += 1) % 2 == 1}
# change null string in aa except at beginning and end to _QQ, which corresponds to changing "" to " in quoted text
(1 .. aa.length - 2).each {|i| aa[i] = _QQ if aa[i] == ''}
# escape fs by _FS_ and rs by _RS_ in aa
aa = aa.join(_US_).gsub(fs, _FS_).gsub(rs, _RS_).split(_US_)
# rebuild escaped text and convert it to 2d array by using _FS_ and _RS_
s = aa.zip(bb).join('')
return s.split(_RS_).map {|x| x.split(_FS_)}
end
def array2csv(aa, opts = {})
# array aa : 2d array
# hash opts : {:fs => fs, :rs => rs}
# string fs : field separator
# string rs : record separator
# return string : csv text representing data in 2d array
# * field is quoted if it contains fs, rs or quote character
fs, rs = {:fs => %[,], :rs => %[\n]}.merge(opts).values_at(:fs, :rs)
q = %["]
aa.map do |a|
a.map do |s|
s =~ /#{fs}|#{rs}|#{q}/o ? (q + s.gsub(/#{q}/o, q + q) + q) : s
end.join(fs)
end.join(rs) + rs
end
key_index = 1 # consolidation key column index (0-based) of master and names csv files
name_index = 2 # name column index (0-based) of names csv file
sort_index = 0 # sort column index (0-based) of output csv file
csv_fs = %[,] # field separator in input and output csv's
csv_rs = %[\n] # record separator in output csv
hh = {} # internal hash table
# create 2d-arrays from input csv's
aa = []
ARGV[0,2].each do |f|
File.open(f) { |f| aa << csv2array(f.read, :fs => csv_fs, :rs => /\015\012|\015|\012/)}
end
pp, qq = aa
# process header
pp1 = pp.shift
qq1 = qq.shift
rr1 = pp1 << qq1[name_index]
# process body
pp.each { |e| hh[e[key_index]] = e << '' }
qq.each { |e| hh[e[key_index]][-1] = e[name_index] }
rr = []
hh.keys.sort_by { |k| hh[k][sort_index].to_i }.each { |k| rr << hh[k] }
# build and print output csv
print array2csv(rr.unshift(rr1), :fs => csv_fs, :rs => csv_rs)
EOF
If you're not familiar with shell script in Terminal.app, you may run it in Run Shell Script action in Automator workflow as follows.
1) Run AppleScript action
- Code = as follows
set in1 to (choose file of type {"csv", "txt"} with prompt "Choose master csv file")'s POSIX path
set in2 to (choose file of type {"csv", "txt"} with prompt "Choose names csv file")'s POSIX path
set out to (choose file name default name "out.csv" with prompt "Specify output csv file name and location")'s POSIX path
return {in1, in2, out}
2) Run Shell Script action
- Shell = /bin/bash
- Pass Input = as arguments
- Code = as follows
#!/bin/bash
/usr/bin/ruby -w <<'EOF' - "$1" "$2" > "$3"
#
# ARGV[0] = master csv
# ARGV[1] = names csv
#
# * input and output csv fields e.g.
# master csv fields = N, TAXON, ...
# names csv fields = N, TAXON, NAME_COMMON
# output csv fields = N, TAXON, ..., NAME_COMMON
# * field TAXON is the consolidation key.
# * Output csv records are sorted by field N in numerically ascending order.
# * internal hash table is created such that
# { pp[i][k] => [ pp[i] << qq[j][n] : qq[j][k] == pp[i][k] or '' ] : for i }
# where
# pp = 2d-array of master csv
# qq = 2d-array of names csv
# k = consolidation key column index (0-based) of master and names csv (k = 1 : TAXON)
# n = name column index (0-based) of names csv (n = 2 : NAME_COMMON)
# i = body row index (0-based) of master csv
# j = body row index (0-based) of names csv
# a[x][y] denotes data at coorditates [x][y] of array a
#
def csv2array(t, opts = {})
# string t : csv text
# hash opts : {:fs => fs, :rs => rs}
# string fs : field separator
# string rs : record separator
# * fs and rs may be string or compiled pattern
# return array : 2d-array representing data in csv
# quasi constants
_QQ = %["] # U+0022 QUOTATION MARK
_FS = %[\x1c] # U+001C INFORMATION SEPARATOR FOUR (FS)
_RS = %[\x1e] # U+001E INFORMATION SEPARATOR TWO (RS)
_US = %[\x1f] # U+001F INFORMATION SEPARATOR ONE (US)
# create unique escape sequences
_FS_, _RS_, _US_ = _FS, _RS, _US
_FS_ += _FS while t.include?(_FS_)
_RS_ += _RS while t.include?(_RS_)
_US_ += _US while t.include?(_US_)
# accept fs, rs
fs, rs = {:fs => %[,], :rs => %[\n]}.merge(opts).values_at(:fs, :rs)
# split text by _QQ and get arrays for odd & even elements - aa & bb (bb is quoted elements)
p = 0
aa, bb = t.split(_QQ).partition {|x| ( p += 1) % 2 == 1}
# change null string in aa except at beginning and end to _QQ, which corresponds to changing "" to " in quoted text
(1 .. aa.length - 2).each {|i| aa[i] = _QQ if aa[i] == ''}
# escape fs by _FS_ and rs by _RS_ in aa
aa = aa.join(_US_).gsub(fs, _FS_).gsub(rs, _RS_).split(_US_)
# rebuild escaped text and convert it to 2d array by using _FS_ and _RS_
s = aa.zip(bb).join('')
return s.split(_RS_).map {|x| x.split(_FS_)}
end
def array2csv(aa, opts = {})
# array aa : 2d array
# hash opts : {:fs => fs, :rs => rs}
# string fs : field separator
# string rs : record separator
# return string : csv text representing data in 2d array
# * field is quoted if it contains fs, rs or quote character
fs, rs = {:fs => %[,], :rs => %[\n]}.merge(opts).values_at(:fs, :rs)
q = %["]
aa.map do |a|
a.map do |s|
s =~ /#{fs}|#{rs}|#{q}/o ? (q + s.gsub(/#{q}/o, q + q) + q) : s
end.join(fs)
end.join(rs) + rs
end
key_index = 1 # consolidation key column index (0-based) of master and names csv files
name_index = 2 # name column index (0-based) of names csv file
sort_index = 0 # sort column index (0-based) of output csv file
csv_fs = %[,] # field separator in input and output csv's
csv_rs = %[\n] # record separator in output csv
hh = {} # internal hash table
# create 2d-arrays from input csv's
aa = []
ARGV[0,2].each do |f|
File.open(f) { |f| aa << csv2array(f.read, :fs => csv_fs, :rs => /\015\012|\015|\012/)}
end
pp, qq = aa
# process header
pp1 = pp.shift
qq1 = qq.shift
rr1 = pp1 << qq1[name_index]
# process body
pp.each { |e| hh[e[key_index]] = e << '' }
qq.each { |e| hh[e[key_index]][-1] = e[name_index] }
rr = []
hh.keys.sort_by { |k| hh[k][sort_index].to_i }.each { |k| rr << hh[k] }
# build and print output csv
print array2csv(rr.unshift(rr1), :fs => csv_fs, :rs => csv_rs)
EOF
Resulting workflow will look something like this:
When run, it will let you choose master.csv, names.csv and output csv's name and location and then yield the output as specified.
Briefly tested under OS X 10.6.8.
Good luck,
H
