1 2 Previous Next 27 Replies Latest reply on Sep 9, 2016 5:21 AM by Andre Jenei

Connecting to a .csv through API?

Christian Jensen Mar 12, 2015 6:09 AM

Hi all,

I'd like to connect to the following API as my datasource: http://data.ssb.no/api/v0/dataset/1142.csv?lang=no

So far all I have managed to find is instructions through python scripts and whatnot - but I figure this is so simple that I surely must be missing something? How do I input a homepage as my datasource?

Thank you and all the best,

Christian

1. Re: Connecting to a .csv through API?

diego.medrano Mar 17, 2015 11:19 AM (in response to Christian Jensen)

Hey Christian,

Python tends to be the most common way for people to do so as the most people will use Python with our Data Extract API:

Tableau Data Extract API

I am also moving this to our developer community where others may be more knowledgeable about the subject.

-Diego
Like Show 0 Likes(0)

Actions
2. Re: Connecting to a .csv through API?

Christian Jensen Mar 17, 2015 11:48 AM (in response to diego.medrano)

Hi Diego,

Is my understanding correct that I will need to maintain a Python script to update the TDE, which is then just loaded into Tableau?

All the best,
Christian
Like Show 0 Likes(0)

Actions
3. Re: Connecting to a .csv through API?

diego.medrano Mar 17, 2015 11:49 AM (in response to Christian Jensen)

As far as I understand it, that's what most people do when using API's. They simply run the script whenever they want to update the data.

-Diego
1 of 1 people found this helpful
Like Show 0 Likes(0)

Actions
4. Re: Connecting to a .csv through API?

Christian Jensen Mar 17, 2015 12:02 PM (in response to Christian Jensen)

The TDE solution is rather complicated in terms of user-friendliness, especially so when the simplicity of the operation is taken into account.
After all I just want tableau to retrieve a .csv from an URL and use that as data, which should not be much different to retrieving a .csv from a server or desktop location.

Thank you for your useful replies Diego.
Like Show 0 Likes(0)

Actions
5. Re: Connecting to a .csv through API?

Jonathan Drummey Mar 17, 2015 2:18 PM (in response to Christian Jensen)

There's a huge difference between a local file system and the http: protocol, and database drivers are built to use local file systems. There's some extra glue needed to make that http: based file available to Tableau.

There's a workaround someone just posted using Excel here: http://community.tableau.com/message/336457#336457, the thread is a longer piece about using a third party product.

I'm hoping that the web data connector that Tableau has announced will solve this problem, I've been wanting access to Google Spreadsheets for awhile now, see http://community.tableau.com/ideas/1456.
Like Show 0 Likes(0)

Actions
6. Re: Connecting to a .csv through API?

Jeff D Mar 17, 2015 9:59 PM (in response to Christian Jensen)

Hi Christian,

It shouldn't be hard to script downloading a csv file. From what you've described, it sounds like a batch file using curl should be able to do the job. You could create a shortcut, and double clicking the shortcut would download the file and then launch Tableau. There are lots of different ways to do this.

Longer term, as Jonathan mentioned, the announced but not yet available web data connector will allow you to connect to an API. Some amount of programming will be required to create the web connector, but once in place, users won't need to know about it.
Like Show 0 Likes(0)

Actions

7. Re: Connecting to a .csv through API?

Tamas Foldi Apr 1, 2015 11:57 AM (in response to Christian Jensen)

As this post landed in the dev zone, let me solve it as a developer (=with workarounds)

I put together a postgres stored procedure (less than 10 lines of code) which can take an URL as pass back its CSV data as table. With this, you can access every web based csv/tsv/whatever easily.

For your particular case:

1. Add my form_url function to a new/existing postgres like database, you can find all details here.

2. Create a type for your CSV file

2 3 4 5 6 7	CREATE TYPE sales_data AS ( varenummer text, import_eksport text, tid text, statistikkvariabel text, Utenrikshandel int );

3. Get my function and add your type, then put it to your database

CREATE OR REPLACE FUNCTION parse_sales_data(

IN csv_text text,

IN skip_first_line boolean DEFAULT true

)

RETURNS SETOF sales_data

AS $$

import csv, traceback

try:

dialect = csv.Sniffer().sniff(csv_text[:1024])

lines = csv_text.splitlines()

return csv.reader( (lines[1:] if skip_first_line else lines), dialect)

except:

plpy.error("Error parsing csv {0}".format(traceback.format_exc()))

$$ LANGUAGE plpythonu VOLATILE;

4. Check if it is working or not with the following SQL

1 2 3 4	select (sales_data).* from parse_sales_data( from_url(E'http://data.ssb.no/api/v0/dataset/1142.csv?lang=no')) as sales_data

And we are done. From Tableau:

1. Custom SQL

2. Make your workbook

You can even control/build the URL from the workbook with parameters allowing to pass parameters to the web service.

If postgres is not your favorite you can do the same from any other modern db engine.

I think it is not so hard, depending on your use case (if you need real time data, like tickers or operational stuff) it might be useful / enough.

The complete process is documented here: Tableau Live Data Connection to CSV over HTTP/REST -Databoss, including your use case with its challenges.

8. Re: Connecting to a .csv through API?

Daniel Leventhal Apr 2, 2015 12:52 PM (in response to Christian Jensen)

The Web Data Connector that is in beta would probably help you here. Check out the last message in this thread: Re: load data from RESTful web services API
Like Show 0 Likes(0)

Actions
9. Re: Connecting to a .csv through API?

Cristian Vasile Apr 2, 2015 2:21 PM (in response to Christian Jensen)

Until T9 is ready you could do the following in 5 mins.

1. download the csv on local disk using curl / curl and libcurl
2. read the csv in Tableau using Microsoft Text Odbc Driver. / SQLDriverConnect (Text File Driver)
3. Repeat.

Regards,
Cristian.
Like Show 0 Likes(0)

Actions
10. Re: Connecting to a .csv through API?

Tamas Foldi Apr 3, 2015 6:15 AM (in response to Cristian Vasile)
1. download the csv on local disk using curl / curl and libcurl

2. read the csv in Tableau using Microsoft Text Odbc Driver. / SQLDriverConnect (Text File Driver)

3. Repeat.

Yes, this is the most easiest solution, however:
where do you download the data? on the desktop computer? to the server? how will you refresh the datasource after publish to the server periodically? how can multiple users work on the source from different desktop pcs?
This connection never be a live one (this applies to the tableau web data connector as well), thus, you cannot pass parameters to the web service during exploration/discovery time. (in this example how can users change for instance the lang parameter in the url to other languages?)

So in some cases this is enough, but in some cases it does not - so simple.
Like Show 1 Likes(1)

Actions
11. Re: Connecting to a .csv through API?

allan.walker.0 Apr 8, 2015 7:36 AM (in response to Cristian Vasile)

tableau/wgetsfpd at master · allanwalkerit/tableau · GitHub

Will wget the CSV into Postgres as a query.
Like Show 0 Likes(0)

Actions
12. Re: Connecting to a .csv through API?

Tamas Foldi Apr 8, 2015 8:36 AM (in response to allan.walker.0)

CSV provider for Tableau Data Web Connector source: tableau-web-table-connector/providers/csv at master · starschema/tableau-web-table-connector · GitHub

Connection URL: http://cdn.starschema.net/tableau-web-table-connector/v0.5/index.html
Like Show 0 Likes(0)

Actions
13. Re: Connecting to a .csv through API?

Pieter Bisschops Oct 26, 2015 12:23 PM (in response to Tamas Foldi)

Hello,

I'm use Tableau Desktop 9.1 on my iMac and when I load the Connection URL: http://cdn.starschema.net/tableau-web-table-connector/v0.5/index.html via the web data connector. I can see the same as mentioned in the previous post but I cannot load the data. When I click on it, nothing happens.
Do I have to install something before? Does someone have the same issue?

Pieter
Like Show 0 Likes(0)

Actions
14. Re: Connecting to a .csv through API?

Tamas Foldi Oct 26, 2015 11:52 PM (in response to Pieter Bisschops)

This CSV WDC uses the old (beta) WDC API, not the one that was released with 9.1.

Let me check if I can update it quickly for you.
Like Show 0 Likes(0)

Actions