Best answer

Sync a Onedrive for Business CSV file to Microsoft SQL DB table

  • 23 July 2020
  • 1 reply
  • 317 views

I have a file coming from a EDI system daily in the form of a csv file.  I want to use it in Power BI Services.  This works locally and personally however when I publish the Power BI report online in Power BI Services and attempt to refresh the report that has both SQL and this CSV file I get sync (refresh errors) just for the CSV.  So I want to send the file to a SQL Server Database Table daily (once at about 7am)  from the MS OneDrive for Business csv file.  the file is new every day and always the same name: HCOInv.csv.  So does Zapier support this and how difficult is it to setup?

icon

Best answer by PaulKortman 25 July 2020, 02:14

View original

This post has been closed for comments. Please create a new post if you need help or have a question about this topic.

1 reply

Userlevel 7
Badge +10

@rmcvicar technically Zapier doesn’t support this as a good use case for their system, essentially parsing CSV files. However, many of us do this all the time. There is a hack/trick that needs to happen, but once that is in place the rest is simple. 

 

The hack basically is a code step that receives the URL (publically accessible) of a CSV and then breaks it into line items (Zapier’s terminology for row by row processing) And then the rest of the zap runs for each row. (so you can add it to a SQL database. )

 

The key is in the python code step. here’s a screenshot of how I do this using Code by Zapier > Python

note the fileurl is a publically acessible URL to the CSV, in this case hosted on Google Drive.

The specific code block is: 

import csv
with requests.Session() as s:
download = s.get(input_data['fileurl'])
decoded_content = download.content.decode('utf-8')
cr = csv.DictReader(decoded_content.splitlines(), delimiter=',')
return {"Rows": list(cr)}

This will return a key of “Rows” where the first line item in Rows is the first row of the spreadsheet. here’s an example output:

There will be a 0: 1: 2: etc for each row in the CSV, and the column headings are in bold in this screenshot

 

So using that step in a zap for you I would do the following:

Step 1: Trigger: Schedule by Zapier, Daily at 7:15am

Step 2: Code by Zapier > Python -- copy the code from above, use the URL of the HCOInv.csv file

Step 3. SQL Server > New Row use the output of step 2 to populate the row values in your database

 

Let me know if that works for you.