Clojure write 2d array to csv

Question

I understand how to write to CSV using [clojure.data.csv] But I am at a loss as to write the CSV in this specific format.

The data I want to write to CSV is the result of a DB query using [clojure.java.jdbc] with the as-arrays? true modifier which returns a 2D array where [0][1] is the column names which need to become the headers in the CSV and then [x][y] will become the data to write to these headers so [1][0] will write the first returned row and column 0 to the CSV under the first heading.

(with-open [out-file (io/writer "out-file.csv")]
  (csv/write-csv out-file
       [["abc" "def"]
        ["ghi" "jkl"]]))

The above is an example of writing to CSV file, but I am unsure how to use the result of my query and write the values to CSV.

The data will look like this:

[[header1, header2, header3]
 [val1, val2, val3]
 [val1, val2, val3]]

The query looks like this:

(j/query db ["$SOME_QUERY"] as-arrays? true))

Can somebody help with this?

Edit: update this is what i have so far:

(defn write-query-to-csv [query db output-filename]
  (log/info (str "Executing " query " on " db))
  (let [results (j/query db ["$QUERY"]
                         :as-arrays? true)
        header (->> results
                    first)
        data (->> results)]
    (with-open [out-file (io/writer output-filename)]
      (csv/write-csv out-file
                     (reduce conj (conj [] header) data)))
    (io/file output-filename)))

The header data is correct but I'm unsure how to populate the data variabale :/

@cfrick That was a bad copy paste from me, it shouldn't be in there! — Chris Edwards
– Chris Edwards, Commented Aug 27, 2015 at 16:58
It looks to me like data is a sequence of sequences, and in the let you pull the header sequence out, but don't strip it off of the data. So now header contains a sequence of header labels, and data contains the header sequence plus the data sequences (one sequence for each row). The reduce line adds the header sequence back on to the sequence of sequences--which now contains two header sequences. It looks as if the let only needs to bind result, and then you can pass result with no modification as the second argument to write-csv. That's it. Am I misunderstanding something? — Mars
– Mars, Commented Aug 28, 2015 at 4:05
When you say "the data will look like this", do you mean the data returned by query, or the data you want to write to the file? That's why I can't post an answer--I'm not sure what j/query is returning. If you mean, by that comment, the data that you want to write to the file, then what is query returning? Take a look at it, if you're not sure. (Add (println result), for example. If that's too much data, construct a different query, or use take to take only the first n rows.) My guess is that result is in exactly the format that you want. — Mars
– Mars, Commented Aug 28, 2015 at 4:10
@Mars the query returns the data in the correct format, I was needless over processing it. If you want to mark passing the result to write-csv then that may help others — Chris Edwards
– Chris Edwards, Commented Aug 28, 2015 at 7:51

Mars · Accepted Answer · 2015-08-28 21:40:20Z

It looks to me like results is a sequence of sequences, and in the let you pull the header sequence out, but don't strip it off of the data. Then header contains a sequence of header labels, and data contains the header sequence plus the data sequences (one sequence for each row). The reduce line adds the header sequence back on to the sequence of sequences--which now contains two header sequences. Most of that isn't necessary. Since results is in the correct format for passing to write-csv, the let only needs to bind results, and then you can pass results with no modification as the second argument to write-csv, like this:

(defn write-query-to-csv [query db output-filename]
  (log/info (str "Executing " query " on " db))
  (let [results (j/query db ["$QUERY"]
                         :as-arrays? true)]
    (with-open [out-file (io/writer output-filename)]
      (csv/write-csv out-file result)
    (io/file output-filename)))

So you don't need the reduce line here, but for future reference, it would probably clearer to replace (conj [] header) with (vector header). Also, another way to write the entire reduce expression would be (cons header data). That will return a different kind of sequence than your reduce line, but write-csv won't care, and I think performance should be similar. You could also use (into (vector header) data).

Collectives™ on Stack Overflow

Clojure write 2d array to csv

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related