# Combining CSVs using ruby to match headers

I am trying to write a ruby script that will take all of the CSVs in a given directory and map their headers out so that duplicate columns will match up and columns unique to a file will have their own column in the final data file. The following code produces the output I desire, however something about it seems overly complicated and clumsy. Are there better practices or patterns I should follow to accomplish this? For example it seems to me like the master_hash variable should lose scope when I transfer it to the key_arr, but when I try to make the code reflect that I end up overwriting it each time I open a new file. Am I trying to hard to avoid "slurping" the files? Can this be done using less memory/faster? Forgive me if this question is too general in nature and if I can improve it to be more useful please let me know.

require 'CSV'
key_arr = [] # create Key array to store the headers
master_hash = {} # create master hash to consolidate headers
# get each csv in file
Dir.glob('datasample*.csv').each do |file|
# open each csv
File.open(file) do |csv|
# Create a temporary hash of the headers
# merge temporary hash to master hash
master_hash = master_hash.merge(temp_hash)
end
key_arr = master_hash.keys # Put keys from hash into array
end

CSV.open('dest.csv', 'w') do |dest_csv| # open destination file
dest_csv << key_arr # write header row
end

Dir.glob('datasample*.csv').each do |file| # get all the csv's to be merged
# Open destination file in append mode
CSV.open('dest.csv', 'a', headers: true) do |dest_csv|
# open each source file for reading
source_csv.each do |row| # iterate over each row of the source file
row_arr = Array.new(key_arr.length) # create array to hold Row
row.each do |cell| # get each header/field combination
# iterate over the key arr
# (key arr is the total number of fields on the final csv)
key_arr.length.times do |master_key_index|
# check if the header for the selected cell matches the header
# at the index on the key array to determine position in row array
if cell[0] == key_arr[master_key_index]
# set row array element to cell value if a match
row_arr[master_key_index] = cell [1]
end
end
end
dest_csv << row_arr # Write row to dest_csv
end
end
end
end


While the scale of this individual script is relatively small and performance isn't a huge concern per se, I would rather get into the habit of using the "right" tools for the job than just guessing. Also, sorry about any formatting issues here. I can put this on gist if need be.

Your overall approach seems fine (i.e. get all headers, then combine the files). However, it can be made much simpler.

Before getting into that, though, I'm slightly concerned about how you "manually" parse the headers of each file. The CSV format can have some nasty syntax, so simply splitting on commas may give you the wrong results (for instance, if the header text itself contains a comma). So I'd use the CSV methods for reliable, robust parsing of the header lines.

As for the actual "meat" of the script, it seems to me that you're working way, way too hard, using arrays, hashes, indices, and all sorts of other stuff. No need for that. In fact, you rarely ever need to muck around with low-level array/index stuff in Ruby.

And the neat thing about Ruby's CSV library is that you can reference cells by the header they belong to. So you don't need numeric indices. Instead, if you can do something like:

CSV.foreach(some_file, headers: true) do |row|
puts row["Column A"] # grab the value from a named column in the row
end


CSV.foreach avoids reading everything all at once. Instead it reads the rows, one by one, passing them to the block. And since it's passing them as CSV::Row objects, so you can access the row cells by header name.

So here's my take:

require 'csv'

# Get input files
input_files = Dir.glob("datasample*.csv")

header_line = File.open(file, &:gets)     # grab first line
end

# Write combined file
CSV.open("out.csv", "w") do |out|

# Write rows from each file
input_files.each do |file|