Power Automate to post CSV files to the API?

[GC13]

Hi all, 

Is anyone successfully posting CSV files to the SIS Import API using Power Automate?   

I've seen the custom connector posted elsewhere on this site which deals with a number of tasks, but doesn't cover posting files.   It's been a great help though as I've managed to work out how to add my own definitions in to post files.

I've got to the point where I can authenticate ok and I can post a CSV file successfully, but Canvas reports the CSV is malformed.     That's the only error I can get out of the system and although the logs show Canvas receives the file and gives it it's own name, I can't get that file to see what it looks like after it's arrived.

The source file can be transferred successfully through the web admin page so I know the file is ok. I can also post it via the API using Postman.

As far as I can see, the Power Automate set up looks to be ok.   Eg, content-type is text/csv.   

However, it does look like it must be something PA is doing to corrupt the file.

Kinda stuck.   Any guidance would be greatly appreciated.

Gareth

[melodyc_lam]

@GC13 

I'm not a super expert at Power Automate, but since it's just a way to do HTTP calls like any other programming language maybe I can help. I do SIS imports daily using a Python script 🙂

The best way to try to diagnose these issues is to look at the API call -- Could you provide the API call (minus the auth key!) and any associated parameters you're passing into the API call?

In addition, is there a way in Power Automate to spit out the file after any processing but before the API call is made to Canvas? That may help with figuring out what's going on.

[GC13]

Hi ... thanks for the response and my apologies for the delay in responding!

It appears to be difficult to get the full API call out of Power Automate (as far as I can see) but I may be able to put enough info in here.   I think it may just be the way I'm passing the CSV attachments.

The model schema suggests the following format, but I'm not quite sure how I would add the files into this field.

"csv_attachments": [ "array" ]

The file is coming from a SharePoint location.  I've attempted using the ID of the file.  This adds in content-type as application/octet-stream, although it's set as text/csv earlier in the piece.

[

  [

    {

      "$content-type": "application/octet-stream",

      "$content": "dXNlcl9pZCxsb2dpbl9pZCxhdXRoZW50aWNhdGlvbl9wcm92aWRlcl9pZCxwYXNzd29yZCxmaXJzdF9uYW1lLGxhc3RfbmFtZSxzaG9ydF9uYW1lLGVtYWlsLHN0YXR1cw0KOTkwMDEsOTk5MDEsLCxBbmdyeSxBbmRlcnNvbixBbmdyeSxhYW5kZXJzb25AZGlzbmV5LmNvbSxhY3RpdmU="

    }

  ]

]

I've also attempted to just use the path of the file but I have no way of knowing if the file is sent.    (screenshot attached).

 

The response is always:{ "sis_import_errors": [ { "sis_import_id": 290, "file": "attachment_1117920240304-1135232-tsp7wm.csv", "message": "Malformed CSV", "row_info": null, "row": null },

My script also sends an email to myself with the files attached - they come through ok.  I'm sure it's just my misunderstanding of the way the files should be pushed through the API.

I'd be interested to know how you push your files through from Python as that may provide enough clues to resolve the issue within Power Automate, assuming you're using the csv_attachment field as well?

Thank you.

[melodyc_lam]

@GC13 

Here's the python code that I use to do a SIS import, using a raw requests call.

In requests, the file has to be a binary file (I set the object to be read only binary) and the field I'm using is attachment, not csv_attachment -- not sure if that may be the issue?

file_send = {"attachment": open(filename, 'rb')}
req = requests.post("https://<insert instance here>/api/v1/accounts/1/sis_imports", headers=headers, files=file_send)

 It could also be the endpoint that you're using, I use https://canvas.instructure.com/doc/api/sis_imports.html#method.sis_imports_api.create for uploading SIS files.

Hope this helps!

[GC13]

Thanks, that's good to know. I'll give this a go.

I did see that documentation and wondered why it refers to "attachments" and not "csv_attachments" which I got from the Live API page.   Interestingly, the Live API doesn't give "attachments" as an option.

I'll report back once I've had time to check this out.

Thanks again.

[GC13]

Thanks @melodyc_lam for the help. 

Using Power Automate is not possible as the file being uploaded needs to be on a local file system, whereas we're trying to manipulate a document in SharePoint.  This isn't supported apparently.   We're going back to the original plan of SFTP now that we understand Instructure will host this and do the development of processing files, etc.  

Far less complicated!

[sendres]

Hi @GC13,

In my experience, the "malformed CSV" error message indicates a true incompatibility of your CSV file with Canvas. I would try things like the following:

• Is the CSV file verified to be encoded using UTF-8? Canvas expects SIS import files to have this encoding.
• Do any of the entries have non-ASCII characters, such as accented or diacritical characters? These tend to be first to cause issues with mismatched encoding.
• How was the CSV file created? CSV files saved from Excel, for example, will be saved as ANSI encoding by default. They'll work fine with plain text but Canvas will complain if it doens't understand any accented characters.
• Have you done a manual SIS import with the same file?

In short, I suspect either the original file is improper or something in your upload process is making it appear improper.

[GC13]

Thanks for the response.  Yes, the file was UTF-8, didn't include any non-ASCII characters and the file was generated in notepad.   The manual upload worked ok.

I've since worked out with Instructure, using Power Automate is not possible as the file being uploaded needs to be on a local file system, whereas we're trying to manipulate a document in SharePoint. 

We're going back to the original plan of SFTP now that we understand Instructure will host this and do the development of processing files, etc.