Working example of a CSV update with an API

Hi,
I’m looking for some help to display a dataset from a CSV file that gets updated regularly. In previous Xibo installation(1.7.9) on Windows, this was done by running a VBA in Excel, that would run a SQL query at a fixed interval to get the latest data and save the results in CSV format directly into Xibo CMS library.

Since the release of Xibo 1.8.1 I have moved on to the latest Docker based version on Ubuntu VM machine, I’m trying to achieve the same with the current version of Xibo but not sure what path to choose.

I’m considering the following two options but lack of technical competency is the major hurdle in achieving either.

1- Share a Windows folder in Linux, set it up as CMS library and use the same Excel file that I used in the previous version of Xibo to generate the CSV file. Save the results in the shared folder CMS library(not sure what needs to be done on both, the Windows and the Linux, ends and is it even a reasonable option!!)

2- Use an API to update the CSV dataset (I would like to go this path but feel like a deer in the headlights for being a beginner)

I’m hoping that one of the experts here might suggest a solution and possibly share a working example of an API to update a dataset.

Thank you very much!

I think it is initially worth pointing out that dropping files directly into the CMS library folder isn’t (and wasn’t) a good idea - i’m not sure exactly how you got that working with 1.7.9.

Aside from that, I agree with your initial conclusion - being that using the API would be a good approach to achieve this. You could use the Tasks functionality in Xibo, and write a task that connects to your 3rd party SQL database, runs the query you had before, and pushes the results into a DataSet. How you’d do that is not massively complicated, and yet for a deer in headlights :wink: I can understand why it might be a bit daunting.

We work mainly with PHP, and as such have written API wrappers in PHP, which you can find here: https://github.com/xibosignage/oauth2-xibo-cms

The long and the short of it, is that you can use these wrappers to greatly simplify the task - you create an Xibo entity provider:

// Register a provider and entity provider to act as our API wrapper
$provider = new \Xibo\OAuth2\Client\Provider\Xibo([
    'clientId' => '<clientId>',
    'clientSecret' => '<secret>',
    'redirectUri' => '',
    'baseUrl' => '<your cms url>'
]);

$xibo = new \Xibo\OAuth2\Client\Provider\XiboEntityProvider($provider);

You can then use $xibo to talk to the API - and do things like:

$xibo->post('/dataset/importjson/' . $dataSetId, ['json' => [
    'rows' => [
        <your data in here>
    ]
]]);

Sorry I don’t have time to elaborate further - but I hope this points you in the right direction? If you’d like someone to write something for you, please do drop us an email.

1 Like

Hi Dan,
Thank you very much for pointing in the right direction. I’ll go over the basics of API wrappers and try following along your instructions. Hopefully, I’ll be able to get somewhere. The worst case scenario, I’ll request again someone here to help me.
Thank you again for kind support!

1 Like