Answered

Excel (or CSV) to sharepoint list

(0) Share

Report

Posted on by 55552

682

Is it possible to use Flow to parse data from incoming Excel or csv files (files submitted to the SharePoint list or library) into columns in the same SharePoint list? In other words, we have multiple csv files coming into the SharePoint list from an external forms service, and would like to automate the process of extracting the column data from those files, and adding a row to the SharePoint list for each incoming file.

Categories:

Building flows

I have the same question (0)

All responses (11)

Answers (1)

Sort by

Verified answer

abm abm 32,985 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

Hi @Runner55552,

In order to acheive this you need to follow certain standard such as all excel file should use table. Map the table columns to SharePoint list columns. Also use the file system flow when file is created.

Let me know it goes.

Thanks

Was this reply helpful? Yes No
55552 682 on at

Like (0)

Report
Copy link

Link copied!

Thanks for the reply. I don't think there is an option in Flow to reference an Excel file that resides within SharePoint. The options seemed to allow referencing an Excel file if was was residing externally on OneDrive or other services. Is this correct? If so, this won't work for my particular need.

Was this reply helpful? Yes No
Dean Gross 242 on at

Like (0)

Report
Copy link

Link copied!

I think that it would be better to use Powershell to perform the file manipulations on a local computer before sending it to SharePoint.

Was this reply helpful? Yes No
55552 682 on at

Like (0)

Report
Copy link

Link copied!

We are really looking for a solution that would be contained within SharePoint. Would Nintex workflow be able to handle the parsing automatically?

Was this reply helpful? Yes No
55552 682 on at

Like (1)

Report
Copy link

Link copied!

I ended up using OneDrive, so the Flow now grabs Excel rows from a table within a file that has been sent to OneDrive (from a forms service called Forms on Fire). These rows are then parsed/sent to a SharePoint list. This works well, except due to the process, I always have one blank row along with my data rows. Is there a Flow option to delete an item or delete an Excel row? The number of rows in a file will vary, so I need to be able to search for the last row (the blank one) and delete it. And of course, each time a new form comes in, it creates a new file, and the Flow operates on this new file (unique filename).

Was this reply helpful? Yes No
55552 682 on at

Like (0)

Report
Copy link

Link copied!

Update: Flow seems to have trouble with incoming files if they have "filtering" turned on in the tables. So if I add a file to OneDrive manually with the filtering turned off in the table headers, the Flow works fine. Unfortunately, my automated process involves replacement of fields in a form (like Word's mail merge) each time a file is generated. And that field replacement (merge) process seems to turn the filtering on in the table headers, regardless of my efforts to stop it (used sheet protection to allow only insertion of rows, but filtering was still turned on). At that point, Flow produces an error and says that "Tablexx already exists, bad request, etc.".

Any ideas on how to set up an Excel template with fields (similar to mail merge) and ensure that filtering is NOT turned on when the fields are replaced with actual values?

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi,

Would you be able to share the steps you used to do this?

Thanks

Was this reply helpful? Yes No
55552 682 on at

Like (0)

Report
Copy link

Link copied!

It has been so long since I tried this that I don't remember the steps offhand. I will, however, see if my "Flow" still exists, and see if I can do some screenshots. I do remember that in the end, it only worked part of the time, and the connections to SharePoint and OneDrive had to be refreshed periodically. Overall, I was not satisfied with how Flow functioned, even before it started having problems with the filtering issue.

Was this reply helpful? Yes No
anton-khrit 197 on at

Like (0)

Report
Copy link

Link copied!

You can use Parse CSV action from Plumsail Documents connector. It allows you to convert CSV into an array and variables for each column. Please read this article demonstrating how it works.

Once you parsed the CSV you can iterate through result array and inserd data into your SharePoint lists.

Was this reply helpful? Yes No
55552 682 on at

Like (0)

Report
Copy link

Link copied!

Thanks, years later, Flow has improved, and now allows extracting from Excel tables within SharePoint to SharePoint lists.

Was this reply helpful? Yes No