Jun 02 2020 08:25 AM - edited Jun 02 2020 08:35 AM
I am trying to import data from our ERP software to a shared calendar on SharePoint. The data is the delivery schedule. Dates may change for individual order and we need that to be reflected in the calendar. We have set up a report that runs every 15 minutes exporting data to an excel file. The data is not in a table format and is overwritten every time the report runs. The output file looks like the following.
BillToName, OrderType, SalesOrderNo, ShipExpireDate
John Dow, S, 3000859, 5/13/2020
Sports Construction, S, 3000900, 5/11/2020
Jan Smith, S, 300094, 5/13/2020
Bob World, S, 3001194, 5/13/2020
Sports Construction, S, 3001900, 5/11/2020
Sports Construction, S, 3001901, 5/11/2020
Sports Construction, S, 3001902, 5/12/2020
Sports Construction, S, 3001903, 5/12/2020
Sports Construction, S, 3001904, 5/13/2020
Sports Construction, S, 3001905, 5/13/2020
Sports Construction, S, 3001906, 5/15/2020
I have tried using Flow/PowerAutomate But I get lost when writing the data to a SharePoint list. I can export to CVS file using a batch file but I don't know what to do with it then. I am thinking if I know what kind of file a SharePoint list is, I could over-right it when a new file is created by ERP software. Otherwise, I would need to delete all events and write the new data. also need to save the SalesOrderNo column to the Title column. BillToName column to the Location column. ShipExpireDate column to Start Time column.
Any ideas on how to get this done would be appreciated.
Jun 02 2020 08:34 AM
Jun 04 2020 07:34 AM
I have made some changes and progress.
I now have the ERP software saving the files as .xlsx and running a macro to convert the data into a table.
I created a new flow with these elements. Excel online - Get The Tables > Excel online - List the Rows Present in the Table > SharePoint – Create Idem.
I created a list on SharePoint and added a Calendar View.
What I need now is to add something to the flow to delete items in the list before I create the updated items.