Forum Discussion
Import excel column as an item in SharePoint list
I have a huge excel file with a bunch of tabs that I would like to convert into one SharePoint List through PowerAutomate so that the list is always up to date.
All worksheets have the same format. The first column will be the sharepoint columns and the "Colonne2" will be a new item for each of the worksheet. I don't need the "Colonne1" in the SharePoint List.
Here is an example of one worksheet:
| Numéro de compte | Colonne1 | Colonne2 |
| 4160 | Subventions total | 4000 |
| 4310 | Billeterie | |
| 4351 | Parrainages Commandites | 1500 |
| 4353 | Ventes souvenirs | |
| 4450 | Revenus divers | |
| 4541 | Membres ou cartes d'adhésion | |
| 4512 | Prélèvements | |
| 4530 | Dons | |
| 4560 | Ventes à commission d'oeuvres artistique | |
| 4460 | Ventes boutique | |
| 4550 | Intérêts | |
| 4650 | Transferts de garderie codé comme paie de DNSSAB | |
| 4651 | Transfert de garderie pour événements culturels | |
| 5210 | Sous contractuel | 0 |
| 5310 | Transportation | 10 |
| 5311 | Per Diem nourriture bénévoles et artistes | 0 |
| 5320 | Cachets hors Ontario | 0 |
| 5321 | Cachets artistes Ontario | 0 |
| 5331 | Hébergement Artistes Conférenciers | 0 |
| 5332 | Techniciens | 300 |
| 5334 | Équipements de programmation Locations et Achats | 421 |
| 5335 | Location lieux, permis de terrain, etc. | 207,01 |
| 5350 | Publicité | 483,94 |
| 5354 | Relations publiques (réseautage) | 0 |
| 5356 | Achats matériaux prix | 3 034,15 |
| 5415 | Sécurité | 0 |
| 5420 | Traiteurs et nourritures pour vendre au public | 167,72 |
Any ideas how to do this? I want to use PowerAutomate so that the SharePoint list is always current but also gives me the history of each project. I guess I could copy the colums and paste as horizontal on each worksheet, but then the Excel will be clunky. Each worksheet is a project and we will be adding projects all the time.
Hi JoelGDucharme, I hope that I understsood the problem correctly. We can create a flow with the following actions:
Initialize Object: type object Value {}
Apply for each: @{outputs('Get_tables')?['body/value']}
Set reset Object: Value {}
Apply to each row: @{outputs('List_rows_present_in_a_table')?['body/value']}
Compose: @{addProperty(variables('Object'),items('Apply_to_each_row')?['Numéro de compte'],items('Apply_to_each_row')?['Colonne2'])}
Set variable : @{outputs('Compose')}
In Compose 2 you can check the result @{variables('Object')}
which in my case is:
{"4160": "4000","4310": "","4351": "1500","4353": "","4450": "","4460": "","4512": "","4530": "","4541": "","4550": "","4560": "","4650": "","4651": "","5210": "0","5310": "10","5311": "0","5320": "0","5321": "0","5331": "0","5332": "300","5334": "421","5335": "207,01","5350": "483,94","5354": "0","5356": "3 034,15","5415": "0","5420": "167,72"}
8 Replies
Hi JoelGDucharme, I hope that I understsood the problem correctly. We can create a flow with the following actions:
Initialize Object: type object Value {}
Apply for each: @{outputs('Get_tables')?['body/value']}
Set reset Object: Value {}
Apply to each row: @{outputs('List_rows_present_in_a_table')?['body/value']}
Compose: @{addProperty(variables('Object'),items('Apply_to_each_row')?['Numéro de compte'],items('Apply_to_each_row')?['Colonne2'])}
Set variable : @{outputs('Compose')}
In Compose 2 you can check the result @{variables('Object')}
which in my case is:
{"4160": "4000","4310": "","4351": "1500","4353": "","4450": "","4460": "","4512": "","4530": "","4541": "","4550": "","4560": "","4650": "","4651": "","5210": "0","5310": "10","5311": "0","5320": "0","5321": "0","5331": "0","5332": "300","5334": "421","5335": "207,01","5350": "483,94","5354": "0","5356": "3 034,15","5415": "0","5420": "167,72"}- I'm not sure I'm getting this. There seems to be something missing or that I don't understand.
- I can't find "Initialize Object", "Set reset Object" connectors.
- Also, how do I refer to the values of the "Apply to each" in the "List rows present in tables" connector?- I got it to work! Thanks!
- Interesting. So after this, I can just add a "Add an item" loop to create a new sharepoint item in my list?
