CODEWITHSUNDEEP
csvclojure
Chris Edwards
Chris Edwards

Reputation: 1558

Clojure write 2d array to csv

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 :/

Upvotes: 0

Views: 388

Answers (1)

Mars
Mars

Reputation: 8854

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).

Upvotes: 1

Related Questions