Export Dataview tables to CSV with DataviewJS

[epabarker]

Continuing the discussion from Dataview - Exporting Table as CSV and based on my answer to another post here, I’ve come up with an imperfect way to save dataview tables as csv fies, if that’s something you’d find useful.

This code does the following:

1. Grab the data you want
2. Select the columns you want in the table
3. Export the table as a CSV file

Example

Note: dataviewjs must be appended for code block

// ----- Gather data and specify table columns -----
// Grab all relevant notes/pages
let projects = dv.pages("#projects");
// Grab the columns you want using .map function
const projects_tbl = projects.map(p => [p.file.link,p.status,p.domain]);
// Map function traverses each row and joins elements with ",", then we join the array of strings with "\n"
let csvContent = "data:text/csv;charset=utf-8," + projects_tbl.map(e => e.join(",")).join("\n");

// ------ Create CSV file ------
// Create a hidden <a> DOM node and set its download attribute as follows, where "my_data.csv" will be the name of your csv file
var encodedUri = encodeURI(csvContent);
var link = document.createElement("a");
link.setAttribute("href", encodedUri);
link.setAttribute("download","my_data.csv");
document.body.appendChild(link);
link.click();

I would use this at your discretion, as it uses some functions I am unaware of, but suffice to say I’ve tested this and I got a table just as I wanted, as a csv file. Once this code has run, it will automatically bring up a window to save the file, and bear in mind that this will keep running if you have set dataview queries to re-evaluate in Live-Preview. So, I would run it as a script perhaps?

Would love some extra suggestions to improve upon this to make it:

• More succinct
• More flexible
• Edit: header info would be nice too

Generalisable version here:

// ----- Gather data and specify table columns -----
// Grab all relevant notes/pages
let <name_of_pages> = dv.pages("<source>");
// Grab the columns you want using .map function
const <table_name> = <name_of_pages>.map(p => [p.<field1>,p.<field2>,p.<field3>]);
// Map function traverses each row and joins elements with ",", then we join the array of strings with "\n"
let csvContent = "data:text/csv;charset=utf-8," + <table_name>.map(e => e.join(",")).join("\n");

// ------ Create CSV file ------
// Create a hidden <a> DOM node and set its download attribute as follows, where "my_data.csv" will be the name of your csv file
var encodedUri = encodeURI(csvContent);
var link = document.createElement("a");
link.setAttribute("href", encodedUri);
link.setAttribute("download","<csv_file_name>.csv");
document.body.appendChild(link);
link.click();

[Archie]

Is this going to get any table on the page? How I can set it up? Or just put it in a Templater and run it?

[epabarker]

Hi @Archie.

The JavaScript in my example is not actually grabbing any table on the page in this instance
This part of the code is the source of the data:

@epabarker

let <name_of_pages> = dv.pages("<source>");

This part of the code determines the fields/attributes from said pages that will populate the table

@epabarker

const <table_name> = <name_of_pages>.map(p => [p.<field1>,p.<field2>,p.<field3>]);

You could run this code alongside rendering it, if you simply added code to render the same table like so:

const headers = ["Field 1", "Field 2", "Field 3"]
const elements = dv.pages("<source>").map(p => [p.<field1>,p.<field2>,p.<field3>]);
dv.table(headers, elements);

I’ve tried to present the most bare-bones way to achieve this goal, but I suspect a good way to do it might be an actual button that runs the code using the buttons plugin.

Again, this could do with more work, but I imagine having:

• Templater plugin
• Dataview plugin
• Buttons plugin

And then:

• Having a button;in the file that you want a csv for, perhaps on a regular basis;that links to a template
• A templater template that simply runs the code I have written above

[Archie]

Thank you , I agree that Buttons and Templater is the way to go to run it regularly. But I don’t still get it. How it is going to identify tables if (i don’t know any identifiers or name for them in dv code. I just use normal basic dataview code blocks. Should I put the table columns by their name (after AS ) or the variable linked to it.

[epabarker]

Ah okay, I’ll explain the two concepts in parallel, with the same references in both cases:

Normal Dataview query

TABLE
    field2 AS "Field 2",
    field3  AS "Field 3"
FROM <source>

DataviewJS to export equivalent table as CSV

let source = dv.pages("<source>");
const table = source.map(p => [p.<field1>,p.<field2>,p.<field3>]);
let csvContent = "data:text/csv;charset=utf-8," + table.map(e => e.join(",")).join("\n");
var encodedUri = encodeURI(csvContent);
var link = document.createElement("a");
link.setAttribute("href", encodedUri);
link.setAttribute("download","my_data.csv");
document.body.appendChild(link);
link.click();

Both can have optional WHERE conditions.

Finally, to make it clear, the Dataview code block as you are familiar with starts with ```dataview, and the DataviewJS code block starts with ```dataviewjs

[Archie]

Oh I see, so you making a table and exporting it at the same time, which makes sense. Thanks for clarifying it for me, I have very little experience with dataviewjs so far

[zmyrenee]

Thank you @epabarker for sharing the code.
I am super new to dataview/js, but I really want to get to a button to click before it download the dataview table into a csv file. I did some research and here’s what I come up with based on your approach and some snippet other shared (link below):

// ----- Gather data and specify table columns -----
// Grab all relevant notes/pages
let <name_of_pages> = dv.pages("#日总结");
// Grab the columns you want using .map function
const headers = ["field1", "field2", "field3"]
const elements = dv.pages("<source>").map(p => [p.<field1>,p.<field2>,p.<field3>])
// Map function traverses each row and joins elements with ",", then we join the array of strings with "\n"
let csvContent = "data:text/csv;charset=utf-8," + headers + "\n" + elements.map(e => e.join(",")).join("\n");

// create button to click to download the csv file
const buttonMaker = (action) => {
    const btn = this.container.createEl('button', {"text": "click to download!"});
    btn.addEventListener('click', async (evt) => {
        action.click()
    });
    return btn;
};

// ------ Create CSV file ------
// Create a hidden <a> DOM node and set its download attribute as follows, where "my_data.csv" will be the name of your csv file
var encodedUri = encodeURI(csvContent);
var link = document.createElement("a");
link.setAttribute("href", encodedUri);
link.setAttribute("download","my_data.csv");
document.body.appendChild(link);
buttonMaker(link)

Other reference link: (Thank you @asauceda94 )

DataviewJS Snippet Showcase

Howdy again @dryice ! Sorry for the delay - holidays and all. Let’s see if I can help! I’ll paste the first code block below, but first, a disclaimer - you should never, EVER, blindly paste code into dataviewjs if you don’t understand it. Bad actors could utilize it to wreak havoc on your system. This isn’t that but good practice moving forward Now, the full code block: const {update} = this.app.plugins.plugins["metaedit"].api; const buttonMaker = (pn, pv, fpath) => { const…

[epabarker]

Hey @zmyrenee, I actually came across this new plugin that exports tables to CSV files with a button here by @metawops. I haven’t had time to look through the code but I wonder if it takes a similar approach! My understanding is it’s limited to the first table in a file, but also works with dataview tables. Many thanks @metawops !

[metawops]

Thanks so much, @epabarker, for the nice mention!
Being able to export not just the first table in a note is one of the top prio enhancements that’s on the list way up. Just haven’t found the time, yet.
Any preferences on that? A button beneath each table? Or any other ideas?

[epabarker]

On a personal level I’d love to automate data export, so from that point of view it would be great to reference tables by an ID to export with a command. Or even collaborate with the advanced tables plugin! But yeah, I’d say much in the same way one has a copy code button for code blocks, an export CSV button for tables would be amazing.

[metawops]

I see. Good point, the automation idea. I’ll keep that in mind as I love automation, too.
But … How would you “automate” Obsidian commands? Is there another plugin for that? Are you talking about “scripting” Obsidian (commands)? How so? (Oh, we’re getting off-topic here, I suppose! )

[epabarker]

To be honest I don’t know if I am the best person to ask these questions to as I don’t know JS or how obsidian functions more generally. I’m an academic so I’m more interested in the data science side. Off topic, I think what I’m really after is a way to query the entirety of my notes as a database. As in daily notes are the time stamps, and then all of the annotations are variables for said timestamps.

[metawops]

Yeah, I’m doing that, too, already. And I put all this metadata into MS Power BI and visualise my vault and my writing behaviour that way. It’s fun! Being able to do this was the reason for writing my plugin, btw.

Don’t know if you’re on LinkedIn but I wrote a post there and @Edmund posted a few screenshots from his Power BI visualisations of his vault. Here’s the link to that LinkedIn post.

[ccchan]

i can now simply select the whole table, copy into gpt4 code interpreter and ask it to make into excel for me.