CSV dataset API or Remote

Hello,
I’ve searched and searched and am not having any luck getting remote datasets or the API to work.
I am running Xibo 4.0.11 in docker on Ubuntu 22.04

When trying to use a remote data set, the task runs, but all the columns are blank.
Testing the data does return records.
All of the results I’ve found says the column type should be set to remote. However the only options I have are Value and Formula.
Most of the results I found said the API was preferred for this.

I am using PowerShell, and have successfully obtained a token. However I keep getting file type not allowed.

Here is a bit of my code

One error I got said I could not use both body and infile. So this current example is using infile, but the body definition is still here.
I’ve also switched out what line is commented out for the content type.

#CSV import of dataset
$headers = @{}
$headers.Add("Accept", "application/json")
#$headers.Add("Content-Type", "multipart/form-data")
$headers.Add('Content-Type','text/csv')
$tokenvalue = $token.access_token
$headers.add("Authorization","Bearer $($(Get-Variable -Name "Tokenvalue").Value)")

$file = "C:\inetpub\wwwroot\file.csv"

$body = @{
  "files" = '@file.csv;type=text/csv' 
  "csvImport_{dataSetColumnId}" = '1'
  "overwrite" ='1' 
  "ignorefirstrow" = '1'

}

Invoke-RestMethod -Uri "$xibourl/api/dataset/import/7"  -Method Post -Headers $headers   -InFile $file

API response

files                                                                         
-----                                                                         
{@{name=1716398735-3872; size=336; type=text/csv; error=Filetype not allowed}}

I was able to upload the same csv manually via the web interface.

Ideally the remote data set would be perfered. For the current case I have the files on a local web server. However if I can get remote data sets working I’d like to pull data from Google sheets.
I could always use PS as an middle man to download the csv from Google if I had to though.

Thank you

And if you tried with postman?

No it appears to be a web service and my xibo url is not publicly accessible.

There was some try it your self items in the API docs I filled in my information. It gave curl commands.

I translated them to PowerShell the best I could.

Postman is an local application and can be run with local URL.
Once tried and works, it give you an idea to PowerShell code.

Please change your Content-Type sent to Header.

I was not able to get postman to work. I imported the json from the website and it didn’t want to accept the base url. I tried to import the json from my own xibo instance and it was either giving proxy errors and or / just timing out. Both with hostname and IP.

I did get it to work in the end though. I’m not a big fan of AI because it gets a lot of code wrong but…

I went back to the documentation examples that give curl commands and asked chatgpt to convert that to powershell. The parameters were incorrect. I researched those errors and found they are only available in PS7. I asked it to rewrite in PS5 but I didn’t have much luck with that. I never got it to work at all.
I installed PS7 and I was back to the blank columns getting imported.

After reviewing the documentation again I was leaving the {dataSetColumnId} placeholder in, instead of replacing it with the actual value.

The documentation allows you to fill in most of the data to get a sample command. However that feild was not fillable and I overlooked it.

So PS7 and replace all the place holders.

Here is the snip that is working for me now for future reference

#CSV import of dataset
$headers = @{}
$headers.Add("Accept", "application/json")
$tokenvalue = $token.access_token
$headers.add("Authorization","Bearer $($(Get-Variable -Name "Tokenvalue").Value)")

$file = "/opt/feeds/feed.csv"



# Define the form data
$formData = @{
    "files" = Get-Item "$file"
    "csvImport_7" = "1"
    "overwrite" = "1"
    "ignorefirstrow" = "1"
}

# Invoke the REST method
Invoke-RestMethod -Uri "$xibourl/api/dataset/import/7" -Method Post -Headers $headers -Form $formData -ContentType "multipart/form-data"
1 Like