-
1. Re: Connecting to a .csv through API?
Diego medranoMar 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:
I am also moving this to our developer community where others may be more knowledgeable about the subject.
-Diego
-
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
-
3. Re: Connecting to a .csv through API?
Diego medranoMar 17, 2015 11:49 AM (in response to christian.jensen)
1 of 1 people found this helpfulAs 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
-
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.
-
5. Re: Connecting to a .csv through API?
Jonathan DrummeyMar 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.
-
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.
-
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
23
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
12
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
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
12
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 LeventhalApr 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
-
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.
-
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.
-
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.
-
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
-
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
-
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.