Reputation: 29

Export CouchDB database to CSV

I'm quite RDB use and have no experience about REST or programming..

I'm usually working with MSSQL and excel...

There is some database on couchdb that I want to export to Excel.

How can I export database to csv?

Here's example of first document's source.

{
   "_id": "0188d98de825528ce9c980f65d0024cc",
   "_rev": "1-046e405f344709e96ee1ef51464871d1",
   "contributors": null,
   "truncated": false,
   "text": "Plume test1",
   "in_reply_to_status_id": null,
   "id": 311079203028209660,
   "favorite_count": 0,
   "source": "<a href=\"http://www.myplume.com/\" rel=\"nofollow\">Plume for Android</a>",
   "retweeted": false,
   "coordinates": {
       "type": "Point",
       "coordinates": [
           126.897899,
           37.535298
       ]
   },
   "entities": {
       "symbols": [
       ],
       "user_mentions": [
       ],
       "hashtags": [
       ],
       "urls": [
       ]
   },
   "in_reply_to_screen_name": null,
   "id_str": "311079203028209664",
   "retweet_count": 0,
   "in_reply_to_user_id": null,
   "favorited": false,
   "user": {
       "id": 20825023,
       "id_str": "20825023"
   },
   "geo": {
       "type": "Point",
       "coordinates": [
           37.535298,
           126.897899
       ]
   },
   "in_reply_to_user_id_str": null,
   "lang": "et",
   "created_at": "Mon Mar 11 11:40:36 +0000 2013",
   "in_reply_to_status_id_str": null,
   "place": null
}

I've try https://gist.github.com/xalakox/3026004 but doesn't work.

Desire exported csv is something like

created at, text, source, coordinates1, coordinates2
Mon Mar 11 11:40:36 +0000 2013,Plume test1,Plume for Android,126.897899,37.535298
Mon Mar 11 11:40:36 +0000 2013,Plume test1,Plume for Android,126.897899,37.535298
Mon Mar 11 11:40:36 +0000 2013,Plume test1,Plume for Android,126.897899,37.535298
Mon Mar 11 11:40:36 +0000 2013,Plume test1,Plume for Android,126.897899,37.535298

Upvotes: 2

Answers (1)

Paul Sweatte

Reputation: 24617

Use a browser tool to get the CSV from the JSON result. Here is the source:

<!doctype html>
<head>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />

<title>JSON to CSV</title>
<meta name="og:title" content="JSON to CSV" />

<meta name="description" content="A simple, in-browser JSON viewer, and CSV converter." />
<meta name="og:description" content="A simple, in-browser JSON viewer and CSV converter." />

<meta name="author" content="Eric Mill" />
<meta name="twitter:creator" content="@konklone" />
<meta name="twitter:url" content="https://konklone.io/json/" />

<link rel="shortcut icon" href="/json/favicon.png" />
<link rel="canonical" href="https://konklone.io/json/" />

<!--
redirect users to the https version of the website.
but: only check when on the production domain.
-->
<script type="text/javascript">
  var enforce = "konklone.io";
  if ((enforce == window.location.host) && (window.location.protocol != "https:"))
    window.location.protocol = "https";
</script>

<!-- jquery, jquery-csv,bootstrap -->
<script type='text/javascript' src='assets/jquery-2.1.1.min.js'></script>
<script src="assets/jquery.csv.js"></script>
<link href="assets/bootstrap.min.css" type="text/css" rel="stylesheet" />


<!-- site styles and JS -->
<link href="assets/site.css" type="text/css" rel="stylesheet" />
<link href="assets/github.css" type="text/css" rel="stylesheet" />
<script src="assets/site.js"></script>
<script src="assets/highlight.pack.js"></script>

<script>
  var excerptRows = 7;
  var input;
  var url;
  var lastSaved;

  // function log(msg) {
  //   return $(".console").removeClass("error").html(msg);
  // }

  // function error(msg) {
  //   return log(msg).addClass("error");
  // }

  function doJSON() {
    // just in case
    $(".drop").hide();

    // get input JSON, try to parse it
    var newInput = $(".json textarea").val();
    if (newInput == input) return;

    input = newInput;
    if (!input) {
      // wipe the rendered version too
      $(".json code").html("");
      return;
    }

    var json = jsonFrom(input);

    // if succeeded, prettify and highlight it
    // highlight shows when textarea loses focus
    if (json) {
      // Reset any error message from previous failed parses.
      $("div.error").hide();
      $("div.warning").show();

      var pretty = JSON.stringify(json, undefined, 2);
      $(".json code").html(pretty);
      if (pretty.length < (50 * 1024))
        hljs.highlightBlock($(".json code").get(0));

      // convert to CSV, make available
      doCSV(json);
    } else {
      // Show error.
      $("div.warning").hide();
      $("div.error").show();
      $(".json code").html("");
    }

    // Either way, update the error-reporting link to include the latest.
    setErrorReporting(null, input);

    return true;
  }

  // show rendered JSON
  function showJSON(rendered) {
    console.log("ordered to show JSON: " + rendered);
    if (rendered) {
      if ($(".json code").html()) {
        console.log("there's code to show, showing...");
        $(".json .rendered").show();
        $(".json .editing").hide();
      }
    } else {
      $(".json .rendered").hide();
      $(".json .editing").show().focus();
    }
  }

  function showCSV(rendered) {
    if (rendered) {
      if ($(".csv table").html()) {
        $(".csv .rendered").show();
        $(".csv .editing").hide();
      }
    } else {
      $(".csv .rendered").hide();
      $(".csv .editing").show().focus();
    }
  }

  // takes an array of flat JSON objects, converts them to arrays
  // renders them into a small table as an example
  function renderCSV(objects) {
    var rows = $.csv.fromObjects(objects, {justArrays: true});
    if (rows.length < 1) return;

    // find CSV table
    var table = $(".csv table")[0];
    $(table).html("");

    // render header row
    var thead = document.createElement("thead");
    var tr = document.createElement("tr");
    var header = rows[0];
    for (field in header) {
      var th = document.createElement("th");
      $(th).html(header[field])
      tr.appendChild(th);
    }
    thead.appendChild(tr);

    // render body of table
    var tbody = document.createElement("tbody");
    for (var i=1; i<rows.length; i++) {
      tr = document.createElement("tr");
      for (field in rows[i]) {
        var td = document.createElement("td");
        $(td)
          .html(rows[i][field])
          .attr("title", rows[i][field]);
        tr.appendChild(td);
      }
      tbody.appendChild(tr);
    }

    table.appendChild(thead);
    table.appendChild(tbody);
  }

  function doCSV(json) {
    // 1) find the primary array to iterate over
    // 2) for each item in that array, recursively flatten it into a tabular object
    // 3) turn that tabular object into a CSV row using jquery-csv
    var inArray = arrayFrom(json);

    var outArray = [];
    for (var row in inArray)
        outArray[outArray.length] = parse_object(inArray[row]);

    $("span.rows.count").text("" + outArray.length);

    var csv = $.csv.fromObjects(outArray);
    // excerpt and render first 10 rows
    renderCSV(outArray.slice(0, excerptRows));
    showCSV(true);

    // show raw data if people really want it
    $(".csv textarea").val(csv);

    // download link to entire CSV as data
    // thanks to https://jsfiddle.net/terryyounghk/KPEGU/
    // and https://stackoverflow.com/questions/14964035/how-to-export-javascript-array-info-to-csv-on-client-side
    var uri = "data:text/csv;charset=utf-8," + encodeURIComponent(csv);
    $(".csv a.download").attr("href", uri);
  }

  // loads original pasted JSON from textarea, saves to anonymous gist
  // rate-limiting means this could easily fail with a 403.
  function saveJSON() {
    if (!input) return false;
    if (input == lastSaved) return false;

    // save a permalink to an anonymous gist
    var gist = {
      description: "test",
      public: false,
      files: {
        "source.json": {
            "content": input
        }
      }
    };

    // TODO: show spinner/msg while this happens

    console.log("Saving to an anonymous gist...");
    $.post(
      'https://api.github.com/gists',
      JSON.stringify(gist)
    ).done(function(data, status, xhr) {

      // take new Gist id, make permalink
      setPermalink(data.id);

      // send analytics event
      Events.permalink();

      // mark what we last saved
      lastSaved = input;

      // update error-reporting link, including permalink
      setErrorReporting(data.id, input);

      console.log("Remaining this hour: " + xhr.getResponseHeader("X-RateLimit-Remaining"));

    }).fail(function(xhr, status, errorThrown) {
      console.log(xhr);

      // send analytics event
      Events.permalink_error(status);

      // TODO: gracefully handle rate limit errors
      // if (status == 403)

      // TODO: show when saving will be available
      // e.g. "try again in 5 minutes"
      // var reset = xhr.getResponseHeader("X-RateLimit-Reset");
      // var date = new Date();
      // date.setTime(parseInt(reset) * 1000);
      // use http://momentjs.com/ to say "in _ minutes"

    });

    return false;
  }

  // Updates the error-reporting link to include current details.
  //
  // If the passed-in `id` is not null, a permalink is included.
  // If the passed-in `id` is null, then no permalink is included.
  // (Needed explicitly because the current URL doesn't always refer
  // to a permalink related to the current value of the textarea.)
  //
  // The current body of the textarea will be encoded into the URI,
  // to pre-populate the GitHub issue template, but only if the body
  // is < 7KB (7,168). GitHub's nginx server rejects query strings
  // longer than ~8KB.
  //
  // If no `id` is given, and content is too long, the URL will
  // encode only a title, and no body.
  function setErrorReporting(id, content) {
    var base = "https://github.com/konklone/json/issues/new";

    var title = "Error parsing some specific JSON";

    var body = "I'm having an issue converting this JSON:\n\n";
    if (id) body += (
      window.location.protocol + "//" +
      window.location.host + window.location.pathname +
      "?id=" + id + "\n\n"
    );

    if (content.length <= (7 * 1024))
      body += ("```json\n" + content + "\n```");

    var finalUrl = base + "?title=" + encodeURIComponent(title) +
      "&body=" + encodeURIComponent(body);

    $(".error a.report").attr("href", finalUrl);

    // console.log("Updated error reporting link to:" + finalUrl);
    return true;
  }

  // given a valid gist ID, set the permalink to use it
  function setPermalink(id) {
    if (history && history.pushState)
      history.pushState({id: id}, null, "?id=" + id);

    // log("Permalink created! (Copy from the location bar.)")
  }

  // check query string for gist ID
  function loadPermalink() {
    var id = getParam("id");
    if (!id) return;

    $.get('https://api.github.com/gists/' + id,
      function(data, status, xhr) {
        console.log("Remaining this hour: " + xhr.getResponseHeader("X-RateLimit-Remaining"));

        var input = data.files["source.json"].content;
        $(".json textarea").val(input);
        doJSON();
        showJSON(true);
      }
    ).fail(function(xhr, status, errorThrown) {
      console.log("Error fetching anonymous gist!");
      console.log(xhr);
      console.log(status);
      console.log(errorThrown);
    });
  }

  $(function() {

    $(".json textarea").blur(function() {showJSON(true);});
    $(".json pre").click(function() {showJSON(false)});
    $(".csv textarea").blur(function() {showCSV(true);})
    $(".csv .raw").click(function() {
      showCSV(false);
      $(".csv textarea").focus().select();
      return false;
    })

    // if there's no CSV to download, don't download anything.
    // also, log an analytics event.
    $(".csv a.download").click(function() {
      var data = $(".csv textarea").val();
      if (data) {
        Events.download(data.length);
        return true;
      } else
        return false;
    });

    // Both elements are present on page-load, so use normal click handler.
    $(".save a, .error a.save").click(saveJSON);

    // transform the JSON whenever it's pasted/edited
    $(".json textarea")
      .on('paste', function() {
        // delay the showing so the paste is pasted by then
        setTimeout(function() {
          doJSON();
          $(".json textarea").blur();
        }, 1);
      })
      .keyup(doJSON); // harmless to repeat doJSON

    // go away
    $("body").click(function() {
      $(".drop").hide();
    });

    $(document)
      .on("dragenter", function(e) {
        e.preventDefault();
        e.stopPropagation();
        $(".drop").show();
      })
      .on("dragover", function(e) {
        e.preventDefault();
        e.stopPropagation();
      })
      .on("dragend", function(e) {
        e.preventDefault();
        e.stopPropagation();
        $(".drop").hide();
      })
      .on("drop", function(e) {
        $(".drop").hide();

        if (e.originalEvent.dataTransfer) {
          if (e.originalEvent.dataTransfer.files.length) {
            e.preventDefault();
            e.stopPropagation();

            var reader = new FileReader();

            reader.onload = function(ev) {
              console.log(ev.target.result);
              $(".json textarea").val(ev.target.result);

              setTimeout(function() {
                doJSON();
                $(".json textarea").blur();
              }, 1);
            }

            reader.readAsText(e.originalEvent.dataTransfer.files[0]);
          }
        }
      });

    // highlight CSV on click
    $(".csv textarea").click(function() {$(this).focus().select();});

    loadPermalink();
  });
</script>

</head>
<body>

<h1>Convert JSON to CSV</h1>

<section class="json">

  <p>
    <span class="instruction editing">
      Paste your JSON below.
    </span>

    <span class="instruction rendered">
      Click your JSON below to edit.
    </span>

    <span class="save">
      <a href="#">Create a permalink</a> any time.
    </span>

    <span>
      Please <a target="_blank" href="https://github.com/konklone/json/issues">report bugs and send feedback</a> on GitHub.
    </span>

    <span>
      Made by <a href="https://twitter.com/konklone">@konklone.</a>
    </span>
  </p>

  <div class="areas">
    <textarea class="editing"></textarea>
    <pre class="rendered"><code></code></pre>
    <div class="drop">DROP JSON HERE</div>
  </div>

  <div class="warning">
    Extremely large files may cause trouble &mdash; the conversion is done inside your browser.
  </div>

  <div class="error">
    There was an error parsing this JSON. If you're sure this JSON is valid, please
    <a class="report" target="_blank"
      href="https://github.com/konklone/json/issues/new">
      file an issue</a>.
    You can
    <a class="save" href="#">create a permalink to the error</a>
    any time.
  </div>
</section>

<section class="csv">

  <p>
    <span class="rendered">
      Below are the first few rows (<span class="rows count"></span> total).

      <a download="result.csv" href="#" class="download">
        Download the entire CSV</a>,

      or <a href="#" class="raw">show the raw data</a>.
    </span>

    <span class="editing">
      Your JSON will appear below as a table.
    </span>
  </p>

  <div class="areas">
    <textarea class="editing" readonly></textarea>
    <div class="table rendered">
      <table></table>
    </div>
  </div>
</section>

<footer>
<p>
  Thanks to <a href="https://twitter.com/benbalter">@benbalter</a> for help, and to <a href="https://twitter.com/onyxfish">@onyxfish</a> for the amazing <a href="https://csvkit.readthedocs.org/en/latest/scripts/in2csv.html">csvkit</a>.
</p>
</footer>

<script>
  (function(i,s,o,g,r,a,m){i['GoogleAnalyticsObject']=r;i[r]=i[r]||function(){
  (i[r].q=i[r].q||[]).push(arguments)},i[r].l=1*new Date();a=s.createElement(o),
  m=s.getElementsByTagName(o)[0];a.async=1;a.src=g;m.parentNode.insertBefore(a,m)
  })(window,document,'script','https://www.google-analytics.com/analytics.js','ga');

  ga('create', 'UA-252618-16', 'konklone.io');
  ga('set', 'forceSSL', true);
  ga('set', 'anonymizeIp', true);
  ga('send', 'pageview');
</script>

</body>
</html>

References

Upvotes: 1

Export CouchDB database to CSV

Answers (1)

Related Questions