Convert newly uploaded Excel file(table) in to SharePoint list

[ragavanrajan]

Hi Folks, 

          I am trying to achieve this for the last two days but no luck. I need your expertise to solve this problem. 

Background: In PowerApp, users will upload the "Excel Template" which definitely contains a Table.  I would like to take the recently uploaded file especially the table inside in it and update one of my SharePoint list. Due to file lock issue I have decided to create a file name with "UTC Time stamp on it" . Please see my flow in screenshot 

Problem: I have designed the flow which does allow users to upload Excel file from PowerApp and as soon as they upload it, I can see the file in the document library. It works fine. But the area where I am stuck is take the newly created file which contains table and convert that in to SharePoint lists.  Because the newly created file has timestamp on it, the table is not appearing. 

Is there  anyway or an alternative way I can achieve this. Kindly help 

[Yutao]

Hi @ragavanrajan ,

Do you know the table name of the newly uploaded Excel file (workbook)? If yes, you can first click on "Enter custom value" from the table name dropdown list, then manually type in the table name.

Hope this helps!

Yutao

[ragavanrajan]

@Yutao  Thanks for your reply. Yes the name is standard. Table1 (With No space). I have tried this as well as a custom value and it throws error "coudnt find that resource". Here is the error for reference 

Any insights pls. 

[Amanthaper]

Hello, I believe your issue is 2-fold. First with the filename in the SP create file action and second the *File reference in the List rows present in table action. I have a tested fix for your problem. Give me a few minutes to upload.

Thx

Aman

[Amanthaper · Accepted solution]

2 suggestions. First try adding the excel file extension to the SP file name and second, change the Excel *File reference to the SP file "ID" instead of the variable.

Hope this helps.

[efialttes]

Hi!

Nice challenge! Excel Online (Business) connector, right? And the table name is dynamically generated... Did you consider using 'Get tables' just before 'List rows present in a table' to get both the table name and its Id?

https://docs.microsoft.com/en-us/connectors/excelonlinebusiness/#get-tables

Hope this helps

[ragavanrajan]

Hi @Amanthaper ,

 Thank you for the solution. By adding id as filename it does pickup my newly created file which is awesome. But the flow is keep on failing due to two issues. 

1. In my Excel File, some of the columns are number fields. In SharePoint the relevant column fields are "number" as well and it is not mandatory fields. But my flow always expect the value in Excel. How do we solve this issue ? 

2. In my Excel File, few columns are date fields. In my flow it is throwing error with below . Doesnt have the expected format date. I can confirm that it is a date field in Excel. I did search many posts but clearly I am not getting output. 

Any help is appreciated. 

[efialttes]

@ragavanrajan 

REgarding issue#1, did you consider using string() WDL function to convert your Sharepoint number column values into the format expected by Excel API?

Regarding issue#2, I recommend this great thread

https://powerusers.microsoft.com/t5/Connecting-To-Data/Date-format-in-data-table-from-Excel-source-u...

Hope this helps

[ragavanrajan]

@efialttes Thanks very much. It works. 

[efialttes]

@ragavanrajan 

Issue solved?

Hurray! Great job!

THanx for your help making this community great!

[ragavanrajan]

@efialttes  Yes. It is solved. Thanks again.