Deep Nested Enum to CSV or XLSX

Is there a way to take a deep nested enum (examples below) like a map or a list and convert it to a plain CSV or direct to XLSX to export data?
Almost all our data resides in JSONB Postgres. We can export it to JSON but almost all our customers desire a spreadsheet format.

Map example:

%{"a" => [[1504224000000, 50],[1506816000000, 310]], "b" => [[1504224000000, 20],[1506816000000, 81]]}

List example:

[["a", [[1501545600000, 28],[1504224000000, 261]]], ["b", [[1501545600000, 28],[1504224000000, 261]]]]

Ideally output to CSV/XLSX:

a,1501545600000,28
a,1504224000000,261
b,1501545600000,28
b,1504224000000,261

PS: Nested enum can be way more complicated. Possibly output file with thousands lines.

Maybe you can use https://github.com/plataformatec/nimble_csv, but first reshape data a bit so that it consists of a list of rows.

data = [["a", [[1501545600000, 28],[1504224000000, 261]]], ["b", [[1501545600000, 28],[1504224000000, 261]]]]

rows = Enum.flat_map(data, fn [group, rows] ->
  Enum.map(rows, fn row -> [group | row] end)
end)
# returns
[
  ["a", 1501545600000, 28],
  ["a", 1504224000000, 261],
  ["b", 1501545600000, 28],
  ["b", 1504224000000, 261]
]

which can then be probably used with NimbleCSV.dump_to_iodata/1

1 Like

I wrote a general purpose library that can do this kind of stuff pretty easily. It’s probably not the fastest solution, but I can be very simple. For example: This is a simple-minded tool to convert any nested elixir data structure to JSON.

https://hexdocs.pm/phst_transform/examples.html

The key thing about my library is that it passes in a list of the data structures above it as part of the tranformation function. So you can build case statements based on the depth of the structure.

Now this may or may not be simpler than just flattening the data into rows. Frankly I love playing with this library to solve interesting puzzles like yours, but that may just be a twist in my personality.

Give me a bit and I’ll post code to solve your example problems.

1 Like

Here’s the code that solves your sample problem.

defmodule ToCSV do

  import PhStTransform

  # Map example:
  #
  # %{"a" => [[1504224000000, 50],[1506816000000, 310]], "b" => [[1504224000000, 20],[1506816000000, 81]]}
  #
  # List example:
  #
  # [["a", [[1501545600000, 28],[1504224000000, 261]]], ["b", [[1501545600000, 28],[1504224000000, 261]]]]
  #
  # Ideally output to CSV/XLSX:
  #
  # a,1501545600000,28
  # a,1504224000000,261
  # b,1501545600000,28
  # b,1504224000000,261



  def to_csv(data) do
    json_potion = %{Atom => &from_atom/1,
                    List => &from_list/2,
                    Map => &from_map/1,
                    Any => &from_any/1 }

    "#{transform(data, json_potion)}"
  end

  defp from_atom(atom), do: "\"#{inspect(atom)}\""

  #
  defp from_list(list, []) do
    Enum.join(list,"\n")
  end

  defp from_list(list, depth) do
    case depth do
      [List, List, List] -> "#{Enum.join(list,",")}"
      [List, List] ->  list
      [List, Map]  ->  "#{Enum.join(list,",")}"
      [Map]              ->  list
      [List]             ->  csv_list(list)
    end
  end

  def csv_list([head | [tail]]) do
    tail |> Enum.map(fn(item) -> "#{head},#{item}" end) |> Enum.join("\n")
  end

  defp from_map(map) do
    inner = for {key, value} <- map, into: [], do: csv_list([key|[value]])
    "#{Enum.join(inner,"\n")}"
  end


  defp from_any(any) do
    inspect(any)
  end
end
1 Like