FLOW Import Excel Online Rows into SharePoint Custom List

%3CLINGO-SUB%20id%3D%22lingo-sub-568246%22%20slang%3D%22en-US%22%3EFLOW%20Import%20Excel%20Online%20Rows%20into%20SharePoint%20Custom%20List%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-568246%22%20slang%3D%22en-US%22%3E%3CP%3ESince%20the%20Excel%20Connector%20has%20been%20deprecated%20in%20FLOW%2C%20there%20is%20no%20action%20%22Get%20Rows%22%20anymore.%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20would%20be%20the%20best%20way%20to%20parse%20every%20row%20in%20an%20Excel%20Online%20(Business)%20file%20into%20a%20SP%20Custom%20List%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20know%20a%20new%20feature%20is%20being%20released%20this%20month%20in%20SP%2C%20where%20you%20can%20create%20a%20list%20from%20Excel%2C%20but%20this%20is%20a%20manual%20action%2C%20and%20I%20need%20an%20easy%20automated%20way%2C%20using%20FLOW.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eany%20help%20would%20be%20welcome.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-568246%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMicrosoft%20Flow%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESharePoint%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-572280%22%20slang%3D%22en-US%22%3ERe%3A%20FLOW%20Import%20Excel%20Online%20Rows%20into%20SharePoint%20Custom%20List%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-572280%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F226488%22%20target%3D%22_blank%22%3E%40Stefaan%20De%20Vreese%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAssuming%20I%20understand%20you%20right%2C%20you%20may%20want%20to%20try%20using%20%3CSTRONG%3EList%20rows%20present%20in%20a%20table%3C%2FSTRONG%3E%26nbsp%3Band%20if%20you%20have%20a%20SharePoint%20list%20that%20already%20has%20the%20relevant%20columns%20in%20it%2C%20you%20can%20just%20drop%20what%20the%20spreadsheet%20has%20into%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExcel%20spreadsheet%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20288px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F113669i3C2DCE7C13FCB3F5%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22capture20190516125649700.png%22%20title%3D%22capture20190516125649700.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20flow%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20648px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F113667i1EA764A93779880A%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22capture20190516125214810.png%22%20title%3D%22capture20190516125214810.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eand%20the%20result%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20796px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F113668iE134DFF7FC66469D%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22capture20190516125356356.png%22%20title%3D%22capture20190516125356356.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHopefully%20that%20does%20the%20trick%20for%20you!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECheers%3C%2FP%3E%3CP%3EDamien%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1264685%22%20slang%3D%22en-US%22%3ERe%3A%20FLOW%20Import%20Excel%20Online%20Rows%20into%20SharePoint%20Custom%20List%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1264685%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F89704%22%20target%3D%22_blank%22%3E%40Damien%20Rosario%3C%2FA%3E%26nbsp%3Bhow%20to%20add%20if%20my%20excel%20has%2010k%20rows%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1273480%22%20slang%3D%22en-US%22%3ERe%3A%20FLOW%20Import%20Excel%20Online%20Rows%20into%20SharePoint%20Custom%20List%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1273480%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F3416%22%20target%3D%22_blank%22%3E%40Mark%20Antony%20Imperial%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENot%20sure%20I%20understand%20your%20scenario%2C%20but%20either%20way%20you'll%20need%20to%20have%20those%20rows%20as%20part%20of%20a%20table%20to%20be%20able%20to%20retrieve%20and%20update%20from.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20never%20done%20anything%20with%2010k%20rows%20so%20not%20at%20all%20sure%20how%20to%20manage%20that%20or%20even%20if%20Power%20Automate%20can%20manage%20such%20a%20large%20number%20of%20them%20in%20that%20way.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBest%20wishes%3C%2FP%3E%3CP%3EDamien%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1273986%22%20slang%3D%22en-US%22%3ERe%3A%20FLOW%20Import%20Excel%20Online%20Rows%20into%20SharePoint%20Custom%20List%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1273986%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F3416%22%20target%3D%22_blank%22%3E%40Mark%20Antony%20Imperial%3C%2FA%3E%26nbsp%3Byes%20a%20List%20rows%20present%20in%20a%20table%20action%20CAN%20handles%2010k%20rows.%20But%20you%20need%20to%20go%20to%20the%20settings%20for%20the%20action%2C%20switch%20the%20pagination%20toggle%20button%20to%20On%20and%20set%20the%20threshold%20to%20a%20number%20higher%20than%2010000.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERob%3CBR%20%2F%3ELos%20Gallardos%3C%2FP%3E%3CP%3E%3CFONT%20size%3D%222%22%20color%3D%22%23333399%22%3EMicrosoft%20Power%20Automate%20Community%20Super%20User%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2142069%22%20slang%3D%22en-US%22%3ERe%3A%20FLOW%20Import%20Excel%20Online%20Rows%20into%20SharePoint%20Custom%20List%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2142069%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F89704%22%20target%3D%22_blank%22%3E%40Damien%20Rosario%3C%2FA%3E%26nbsp%3BThe%20pulling%20of%20data%20was%20great%20but%20if%20want%20to%20pull%20live%20data%20every%20one%20hour%20it%20pull%20same%20data%20again%20and%20again%20and%20every%20time%20duplicate%20date%20is%20generated.%20pls%20help.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2142227%22%20slang%3D%22en-US%22%3ERe%3A%20FLOW%20Import%20Excel%20Online%20Rows%20into%20SharePoint%20Custom%20List%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2142227%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F969134%22%20target%3D%22_blank%22%3E%40PrasadRedkar%3C%2FA%3E%26nbsp%3Byou%20need%20to%20have%20an%20extra%20column%20in%20your%20spreadsheet%2C%20perhaps%20called%20Imported%2C%20and%20for%20all%20the%20rows%20that%20have%20already%20been%20imported%20into%20SharePoint%20that%20item%20needs%20to%20have%20Yes%20in%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENext%2C%20the%20trigger%20in%20your%20flow%20will%20be%20a%20Recurrence%20Schedule%20set%20to%201%20hour.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThen%20your%20list%20rows%20present%20in%20a%20table%20action%20should%20have%20a%20filter%20query%20of%20%3CSTRONG%3EImported%20ne%20'Yes'%26nbsp%3B%3C%2FSTRONG%3Eso%20it%20will%20only%20take%20action%20on%20those%20rows%20not%20already%20uploaded%20to%20SharePoint.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAfter%20the%20Apply%20to%20each%20and%20the%20SharePoint%20Create%20item%20actions%2C%20still%20inside%20the%20apply%20to%20each%20you'll%20need%20an%20Excel%20for%20Business%20action%20to%20update%20the%20row%20in%20the%20a%20spreadsheet%20with%20%3CSTRONG%3EYes%3C%2FSTRONG%3E%20in%20the%20Imported%20column.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERob%3CBR%20%2F%3ELos%20Gallardos%3CBR%20%2F%3E%3CFONT%20size%3D%222%22%20color%3D%22%23006400%22%3EMicrosoft%20Power%20Automate%20Community%20Super%20User%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Since the Excel Connector has been deprecated in FLOW, there is no action "Get Rows" anymore. 

What would be the best way to parse every row in an Excel Online (Business) file into a SP Custom List?

 

I know a new feature is being released this month in SP, where you can create a list from Excel, but this is a manual action, and I need an easy automated way, using FLOW.

 

any help would be welcome.

6 Replies

Hi @Stefaan De Vreese 

 

Assuming I understand you right, you may want to try using List rows present in a table and if you have a SharePoint list that already has the relevant columns in it, you can just drop what the spreadsheet has into it.

 

Excel spreadsheet:

 

capture20190516125649700.png

 

The flow:

 

capture20190516125214810.png

 

and the result: 

 

capture20190516125356356.png

 

Hopefully that does the trick for you!

 

Cheers

Damien

@Damien Rosario how to add if my excel has 10k rows?

Hi @Mark Antony Imperial 

 

Not sure I understand your scenario, but either way you'll need to have those rows as part of a table to be able to retrieve and update from.

 

I've never done anything with 10k rows so not at all sure how to manage that or even if Power Automate can manage such a large number of them in that way.

 

Best wishes

Damien

@Mark Antony Imperial yes a List rows present in a table action CAN handles 10k rows. But you need to go to the settings for the action, switch the pagination toggle button to On and set the threshold to a number higher than 10000.

 

Rob
Los Gallardos

Microsoft Power Automate Community Super User

@Damien Rosario The pulling of data was great but if want to pull live data every one hour it pull same data again and again and every time duplicate date is generated. pls help.

@PrasadRedkar you need to have an extra column in your spreadsheet, perhaps called Imported, and for all the rows that have already been imported into SharePoint that item needs to have Yes in it.

 

Next, the trigger in your flow will be a Recurrence Schedule set to 1 hour.

 

Then your list rows present in a table action should have a filter query of Imported ne 'Yes' so it will only take action on those rows not already uploaded to SharePoint.

 

After the Apply to each and the SharePoint Create item actions, still inside the apply to each you'll need an Excel for Business action to update the row in the a spreadsheet with Yes in the Imported column.

 

Rob
Los Gallardos
Microsoft Power Automate Community Super User