SOLVED

Bulk import data from >2000 webpages to Excel

%3CLINGO-SUB%20id%3D%22lingo-sub-1446937%22%20slang%3D%22en-US%22%3EBulk%20import%20data%20from%20%26gt%3B2000%20webpages%20to%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1446937%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20there..%3C%2FP%3E%3CP%3EI%20need%20to%20import%20some%20data%20from%20%26gt%3B2000%20webpages%20into%20excel%20(or%20any%20other%20database).%20I%20have%20the%20complete%20list%20of%20webpages.%20I%20found%20a%20few%20tutorials%20where%20Power%20Query%20was%20used%20to%20solve%20the%20issue.%20However%2C%20when%20i%20use%20the%20power%20query%20method%2C%20many%20errors%20pop%20up%20(URLs%20are%20correct).%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20anyone%20help%20with%20this.%20Any%20method%20(Power%20query%20or%20not)%20will%20suffice.%3C%2FP%3E%3CP%3EThank%20you.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EP.S.%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3DdAjw9Vu8wYg%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3DdAjw9Vu8wYg%3C%2FA%3E%3CBR%20%2F%3EThe%20tutorials%20I%20saw%20were%20all%20doing%20the%20things%20just%20like%20the%20above.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1446937%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1446996%22%20slang%3D%22en-US%22%3ERe%3A%20Bulk%20import%20data%20from%20%26gt%3B2000%20webpages%20to%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1446996%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F545848%22%20target%3D%22_blank%22%3E%40Jodhvir%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat's%20too%20abstract.%20URL%20could%20be%20correct%2C%20but%20other%20steps%20to%20transform%20data%20could%20be%20incorrect.%20Without%20the%20sample%20it's%20hard%20to%20say%20what%20could%20be%20done.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1447337%22%20slang%3D%22en-US%22%3ERe%3A%20Bulk%20import%20data%20from%20%26gt%3B2000%20webpages%20to%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1447337%22%20slang%3D%22en-US%22%3E%3CP%3EThnku%20for%20the%20reply%20sir.%3C%2FP%3E%3CP%3EWith%20errors%20I%20meant%20that%20only%20some%20showed%20the%20error%2C%20while%20others%20were%20fine%20(Also%2C%20sometimes%20different%20row%20entries%20show%20the%20errors%20even%20though%20they%20had%20no%20error%20previously%20and%20vice%20versa).%20So%2C%20I%20presumed%20that%20the%20error%20can%20not%20be%20with%20the%20urls.%20I%20also%20tried%20to%20replicate%20this%20in%20Google%20Sheets%20via%20the%20importhtml%20formula.%20If%20I%20run%20the%20formula%20in%20bulk%20it%20shows%20Errors%20again%20and%20if%20the%20same%20formula%20is%20used%20one%20by%20one%20then%20no%20error%20pops%20up.%26nbsp%3B%3CBR%20%2F%3ECould%20it%20be%20that%20the%20website%20does%20not%20entertain%20so%20many%20requests%20at%20the%20same%20time%3F%20If%20yes%2C%20how%20can%20we%20increase%20the%20timegap%3F%3CBR%20%2F%3EIf%20any%20other%20method%20can%20be%20used%2C%20kindly%20elaborate%20that%20too.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1447683%22%20slang%3D%22en-US%22%3ERe%3A%20Bulk%20import%20data%20from%20%26gt%3B2000%20webpages%20to%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1447683%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F545848%22%20target%3D%22_blank%22%3E%40Jodhvir%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYes%2C%20that%20could%20be.%20You%20may%20delay%20an%20execution%20of%20each%20call%20with%20Function.InvokeAfter().%20More%20details%20at%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fblog.crossjoin.co.uk%2F2015%2F04%2F30%2Fusing-function-invokeafter-in-power-query%2F%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3EUsing%20Function.InvokeAfter()%20In%20Power%20Query%3C%2FA%3E%26nbsp%3Band%26nbsp%3B%20others%20like%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fmedium.com%2F%40AndreAlessi%2Fbuilding-delays-into-power-bi-api-queries-function-invokeafter-and-google-maps-api-68b475c73a2c%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3EBuilding%20delays%20into%20Power%20BI%20API%20queries%20%E2%80%94%20Function.InvokeAfter()%20and%20Google%20Maps%20API%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1449999%22%20slang%3D%22en-US%22%3ERe%3A%20Bulk%20import%20data%20from%20%26gt%3B2000%20webpages%20to%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1449999%22%20slang%3D%22en-US%22%3EThank%20you%20for%20the%20guidance%20sir.%20It%20worked%20out%20pretty%20well.%20Turns%20out%2C%20without%20a%20premium%20membership%2C%20the%20website%20only%20entertained%2010%20requests%20at%20a%20time.%20Thanx%20again.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1450404%22%20slang%3D%22en-US%22%3ERe%3A%20Bulk%20import%20data%20from%20%26gt%3B2000%20webpages%20to%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1450404%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F545848%22%20target%3D%22_blank%22%3E%40Jodhvir%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

Hi there..

I need to import some data from >2000 webpages into excel (or any other database). I have the complete list of webpages. I found a few tutorials where Power Query was used to solve the issue. However, when i use the power query method, many errors pop up (URLs are correct). 

Can anyone help with this. Any method (Power query or not) will suffice.

Thank you. 

 

P.S. https://www.youtube.com/watch?v=dAjw9Vu8wYg
The tutorials I saw were all doing the things just like the above.

5 Replies
Highlighted

@Jodhvir 

That's too abstract. URL could be correct, but other steps to transform data could be incorrect. Without the sample it's hard to say what could be done.

Highlighted

Thnku for the reply sir.

With errors I meant that only some showed the error, while others were fine (Also, sometimes different row entries show the errors even though they had no error previously and vice versa). So, I presumed that the error can not be with the urls. I also tried to replicate this in Google Sheets via the importhtml formula. If I run the formula in bulk it shows Errors again and if the same formula is used one by one then no error pops up. 
Could it be that the website does not entertain so many requests at the same time? If yes, how can we increase the timegap?
If any other method can be used, kindly elaborate that too.

 

Thank you.

Highlighted
Best Response confirmed by Jodhvir (Occasional Contributor)
Solution

@Jodhvir 

Yes, that could be. You may delay an execution of each call with Function.InvokeAfter(). More details at Using Function.InvokeAfter() In Power Query and  others like Building delays into Power BI API queries — Function.InvokeAfter() and Google Maps API 

Highlighted
Thank you for the guidance sir. It worked out pretty well. Turns out, without a premium membership, the website only entertained 10 requests at a time. Thanx again.
Highlighted

@Jodhvir , you are welcome