Script to let user download a specific table from Hubdb

[imerial]

Hi Community,

I want users to be able to export a specific hubdb table that is displayed on my site as a CSV file. How is that typically done in hubspot?

[amwilie · Accepted solution]

Hey @imerial ,

You can export the table as a CSV yourself , upload the file to the file manager, and add a link to it on your page. If you want it to be more dynamic and update automatically when the table is updated I don't believe there is any built in functionality for that. You could however use javascript to output the data into an array and create a csv download from that.

https://stackoverflow.com/questions/14964035/how-to-export-javascript-array-info-to-csv-on-client-si...

Example of outputting HubDB data into a javascript array :

{% set data = hubdb_table_rows('XXXXXX', filters) %}
<script>
  window.products = [
      {% for item in data %}
          {
              hs_id: {{ item.hs_id|tojson }},
	      name: {{ item.name|tojson }},
	      description: {{ item.description|tojson }}
	  }{% if not loop.last %},{% endif %}
      {% endfor %}
  ];
</script>

[imerial]

Hi Alyssa,

As much as I tried, nothing seemed to work in regards of outputting HubDB data into a javascript array. I tried to use your solution with the stackoverflow way to generate csv. But nothing works. Would you mind give me an example of how you would do the conversion? just a simple example will help me come up with a solution.

I thank you in advance.

[amwilie]

<a href="" download="hubdb_table.csv" id="download-link">Download CSV</a>

<script>
  $( document ).ready(function() {
    {% set data = hubdb_table_rows(XXXXXX).objects %}
    {% set columns = hubdb_table(XXXXXX).columns %}

    const rows = [
      [
        {% for column in columns %}{{ column.name|tojson }},{% endfor %}
      ],
      {% for item in data %}
            [
              {% for column in columns %}
                {{ item[column.name]|tojson }},
              {% endfor %}
            ],
      {% endfor %}
    ];

    let csvContent = "data&colon;text/csv;charset=utf-8,";

    rows.forEach(function(rowArray) {
        let row = rowArray.join(",");
        csvContent += row + "\r\n";
    });

    var encodedUri = encodeURI(csvContent);
    var link = $('#download-link');
    link.attr("href", encodedUri);
  });
</script>

Replace XXXXXX with your HubDB table's ID and if using this in a custom module the script will need to be in the HTML/HubL frame since the Javascript frame doesn't allow the use of HubL.

[imerial]

Hi Alyssa,

<a href="" download="hubdb_table.csv" id="download-link">Download CSV</a>

<script>
  $( document ).ready(function() {
    {% set data = hubdb_table_rows(2595031).objects %}
    {% set columns = hubdb_table(2595031).columns %}

    const rows = [
      [
        {% for column in columns %}{{ column.name|tojson }},{% endfor %}
      ],
      {% for item in data %}
            [
              {% for column in columns %}
                {{ item[column.name]|tojson }},
              {% endfor %}
            ],
      {% endfor %}
    ];

    let csvContent = "data&colon;text/csv;charset=utf-8,";

    rows.forEach(function(rowArray) {
        let row = rowArray.join(",");
        csvContent += row + "\r\n";
    });
    console.log(row);

    var encodedUri = encodeURI(csvContent);
    var link = $('#download-link');
    link.attr("href", encodedUri);
  });
</script>

I tried what you had but it didn't work for me. I keep getting "failed- No File" every time I tried to download the csv. 

I created a separete module and added the script to the hubl part of it. But it kept failing. 

Then, I added the module to a page I was working on. This time it downloaded a csv with the entire code source on it(see picture). 

I know I'm missing something or doing something wrong. I tried a few other things I found online but nothing worked. I figured I should ask you again. 

Thank you for your time.

[amwilie]

Sorry, would seem when I paste over the code Hubspot replaces a colon in it with an html entity for some reason. So in the following piece 

let csvContent = "data:text/csv;charset=utf-8,";

replace : with an actual colon :