<commit_before>require 'spreadsheet'
require 'csv'
require 'yaml'
require 'json'
require 'set'
require 'pp'

HEADINGS_INSERT = [
  "BANK",
  "IFSC",
  "BRANCH",
  "ADDRESS",
  "CONTACT",
  "CITY",
  "DISTRICT",
  "STATE"
]

def parse_sheets
  data = []

  file_ifsc_mappings = Hash.new

  Dir.glob('sheets/*') do |file|
    log "Parsing #{file}"
    basename = File.basename file
    sheet = Spreadsheet.open(file).worksheet 0
    headings = sheet.row(0)[0,9]

    sheet.each 1 do |row|
      row = row[0,9]
      next if row.compact.empty?
      data_to_insert = [HEADINGS_INSERT, map_data(row, headings)]
      begin
        x = data_to_insert.transpose.to_h
        data.push x
        file_ifsc_mappings[basename] = x['IFSC'][0..3]
      rescue Exception => e
        puts "Faced an Exception"
        puts data_to_insert.to_json
        puts e
        exit
      end
    end
  end
  [data, file_ifsc_mappings]
end

def map_data(row, headings)
  data = []

  # Find the heading in HEADINGS_INSERT
  headings.each_with_index do |header, heading_index|
    header = header.strip
    index = HEADINGS_INSERT.find_index header
    case header
    when 'CONTACT'
      scan = row[heading_index].to_s.scan(/^(\d+)\D?/).last
      data[index] = (scan.nil? or scan==0 or scan=="0" or (scan.is_a? Array and scan==["0"])) ? nil : scan.first
    when 'IFSC CODE'
      index = HEADINGS_INSERT.find_index 'IFSC'
      data[index] = row[heading_index]
      
    else
      data[index] = row[heading_index] if index
    end
  end
  data
end

def export_csv(data)
  CSV.open("data/IFSC.csv", "wb") do |csv|
    csv << data[0].keys
    data.each do |row|
      csv << row.values
    end
  end
end

def export_yml(data)
  File.open("data/IFSC.yml", 'w') { |f| YAML.dump(data, f) }
end

def export_marshal(data)
  File.open("data/IFSC.marshal", 'w') { |f| Marshal.dump(data, f) }
end

def export_json(hash)<commit_msg>Handles Excel errors and converts them to nil #11<commit_after>require 'spreadsheet'
require 'csv'
require 'yaml'
require 'json'
require 'set'
require 'pp'

HEADINGS_INSERT = [
  "BANK",
  "IFSC",
  "BRANCH",
  "ADDRESS",
  "CONTACT",
  "CITY",
  "DISTRICT",
  "STATE"
]

def parse_sheets
  data = []

  file_ifsc_mappings = Hash.new

  Dir.glob('sheets/*') do |file|
    log "Parsing #{file}"
    basename = File.basename file
    sheet = Spreadsheet.open(file).worksheet 0
    headings = sheet.row(0)[0,9]

    sheet.each 1 do |row|
      row = row[0,9]
      next if row.compact.empty?
      data_to_insert = [HEADINGS_INSERT, map_data(row, headings)]
      begin
        x = data_to_insert.transpose.to_h
        x.each do |key, value|
          if value.is_a? Spreadsheet::Excel::Error
            puts "ERROR: #{file} #{x['IFSC']}"
            x[key] = nil
          end
        end
        data.push x
        file_ifsc_mappings[basename] = x['IFSC'][0..3]
      rescue Exception => e
        puts "Faced an Exception"
        puts data_to_insert.to_json
        puts e
        exit
      end
    end
  end
  [data, file_ifsc_mappings]
end

def map_data(row, headings)
  data = []

  # Find the heading in HEADINGS_INSERT
  headings.each_with_index do |header, heading_index|
    header = header.strip
    index = HEADINGS_INSERT.find_index header
    case header
    when 'CONTACT'
      scan = row[heading_index].to_s.scan(/^(\d+)\D?/).last
      data[index] = (scan.nil? or scan==0 or scan=="0" or (scan.is_a? Array and scan==["0"])) ? nil : scan.first
    when 'IFSC CODE'
      index = HEADINGS_INSERT.find_index 'IFSC'
      data[index] = row[heading_index]

    else
      data[index] = row[heading_index] if index
    end
  end
  data
end

def export_csv(data)
  CSV.open("data/IFSC.csv", "wb") do |csv|
    csv << data[0].keys
    data.each do |row|
      csv << row.values
    end
  end
end

def export_yml(data)
  File.open("data/IFSC.yml", 'w') { |f| YAML.dump(data, f) }
end

def export_marshal(data)
  File.open("data/IFSC.marshal", 'w') { |f| Marshal.dump(data, f) }
end

def export_json(hash)
{'fraction_non_alphanumeric': 0.1345771144278607, 'fraction_numerical': 0.006965174129353234, 'mean_word_length': 2.754435107376284, 'pattern_counts': {'":': 0, '<': 11, '<?xml version=': 0, '>': 5, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '3054117', 'n_tokens_mistral': 1535, 'n_tokens_neox': 1496, 'n_words': 354}