125 lines
4.5 KiB
Ruby
125 lines
4.5 KiB
Ruby
# frozen_string_literal: true
|
|
|
|
# keila_csv_lib.rb — Shared library for keila2csv and csv2keila
|
|
#
|
|
# Keila stores custom contact data as a JSON object in a "data" column.
|
|
# This library converts between:
|
|
# - Keila's format: standard columns + a "data" column with a JSON blob
|
|
# - A flat format: standard columns + one column per custom data key
|
|
|
|
require "csv"
|
|
require "json"
|
|
|
|
module KeilaCsv
|
|
# Keila's built-in contact fields in their canonical capitalisation for import.
|
|
# Used both for identifying standard fields (downcased) and for writing output headers.
|
|
KEILA_FIELDS_CANONICAL = %w[Email First_name Last_name External_id Tags Data].freeze
|
|
KEILA_FIELDS = KEILA_FIELDS_CANONICAL.map(&:downcase).freeze
|
|
|
|
# Lookup from lowercase field name → canonical capitalisation
|
|
KEILA_FIELDS_MAP = KEILA_FIELDS.zip(KEILA_FIELDS_CANONICAL).to_h.freeze
|
|
|
|
# keila2csv: Expand Keila's "data" JSON column into individual columns.
|
|
#
|
|
# Each top-level key in the JSON object becomes its own column.
|
|
# Nested objects/arrays are kept as JSON strings in their column.
|
|
# Contacts without a "data" value simply get empty cells.
|
|
# The output column order is: standard fields (minus "data"), then all
|
|
# discovered custom keys (union across all rows, alphabetically sorted).
|
|
def self.keila_to_csv(input_path, output_path)
|
|
rows = CSV.read(input_path, headers: true, header_converters: :downcase)
|
|
|
|
# First pass: collect all custom data keys across all rows
|
|
data_keys = []
|
|
rows.each do |row|
|
|
raw = row["data"]
|
|
next if raw.nil? || raw.strip.empty?
|
|
|
|
parsed = JSON.parse(raw)
|
|
data_keys |= parsed.keys.sort if parsed.is_a?(Hash)
|
|
rescue JSON::ParserError => e
|
|
warn "Warning: could not parse JSON for #{row['email']}: #{e.message}"
|
|
end
|
|
|
|
standard_headers = rows.headers.reject { |h| h == "data" }
|
|
output_headers = standard_headers + data_keys
|
|
|
|
# Second pass: write output
|
|
CSV.open(output_path, "w", headers: output_headers, write_headers: true) do |csv|
|
|
rows.each do |row|
|
|
out = {}
|
|
standard_headers.each { |h| out[h] = row[h] }
|
|
|
|
raw = row["data"]
|
|
if raw && !raw.strip.empty?
|
|
begin
|
|
parsed = JSON.parse(raw)
|
|
if parsed.is_a?(Hash)
|
|
parsed.each do |key, value|
|
|
out[key] = value.is_a?(String) ? value : value.to_json
|
|
end
|
|
else
|
|
warn "Warning: 'data' for #{row['email']} is not a JSON object — skipping."
|
|
end
|
|
rescue JSON::ParserError => e
|
|
warn "Warning: could not parse JSON for #{row['email']}: #{e.message}"
|
|
end
|
|
end
|
|
|
|
csv << output_headers.map { |h| out[h] }
|
|
end
|
|
end
|
|
|
|
{
|
|
contacts: rows.length,
|
|
custom_fields: data_keys
|
|
}
|
|
end
|
|
|
|
# csv2keila: Collect non-standard columns back into a Keila "data" JSON object.
|
|
#
|
|
# Any column not in KEILA_FIELDS is treated as custom data and packed into
|
|
# the "data" JSON column. Empty cells are omitted from the JSON entirely
|
|
# (so they don't overwrite existing values on re-import).
|
|
# Values that look like JSON objects or arrays are re-parsed as structured
|
|
# data; plain strings remain strings.
|
|
def self.csv_to_keila(input_path, output_path)
|
|
rows = CSV.read(input_path, headers: true, header_converters: :downcase)
|
|
|
|
custom_headers = rows.headers - KEILA_FIELDS
|
|
present_standard = rows.headers & (KEILA_FIELDS - ["data"])
|
|
# Write headers in Keila's canonical capitalisation (e.g. "Email", "First_name")
|
|
output_headers = present_standard.map { |h| KEILA_FIELDS_MAP[h] } + ["Data"]
|
|
|
|
CSV.open(output_path, "w", headers: output_headers, write_headers: true) do |csv|
|
|
rows.each do |row|
|
|
out = {}
|
|
# Row data is still keyed by lowercase; map to canonical for output
|
|
present_standard.each { |h| out[KEILA_FIELDS_MAP[h]] = row[h] }
|
|
|
|
data_hash = {}
|
|
custom_headers.each do |key|
|
|
val = row[key]
|
|
next if val.nil? || val.strip.empty?
|
|
|
|
# Re-parse JSON structures (objects/arrays); keep everything else as string
|
|
data_hash[key] = begin
|
|
parsed = JSON.parse(val)
|
|
parsed.is_a?(Hash) || parsed.is_a?(Array) ? parsed : val
|
|
rescue JSON::ParserError
|
|
val
|
|
end
|
|
end
|
|
|
|
out["Data"] = data_hash.empty? ? nil : data_hash.to_json
|
|
csv << output_headers.map { |h| out[h] }
|
|
end
|
|
end
|
|
|
|
{
|
|
contacts: rows.length,
|
|
custom_fields: custom_headers
|
|
}
|
|
end
|
|
end
|