Forum Discussion
scrapping amazon
Hi mrpowrbihelp
Here are two articles for you to see if they are helpful to you:
Loop through Multiple Web Pages using Power Query
Iterating over multiple pages of web data using Power Query
Regards,
Cherie
Thank you! I apologize this is all new to me.
I can get it to loop thru pages using
https://www.mattmasson.com/2014/11/iterating-over-an-unknown-number-of-pages-in-power-query/
with my Code looking like..
(Page as number) as table =>
let
Source = Web.BrowserContents("https://www.amazon.com/gp/offer-listing/B01MQWUXZS/ref=olp_page_next?ie=UTF8&f_all=true&startIndex=" & Number.ToText(Page)),
#"Extracted Table From Html" = Html.Table(Source, {{"Seller", ".olpSellerName"}, {"Price", ".a-color-price"}}, [RowSelector=".olpOffer"])
in
#"Extracted Table From Html"
let
PageRange = {0,10,20,30,40,50},
Source = List.Transform(PageRange, each try {_, GetData(_)} otherwise null),
First = List.FirstN(Source, each _ <> null),
Table = Table.FromRows(First, {"Page", "Column1"}),
Expanded = Table.ExpandTableColumn(Table, "Column1", {"Seller", "Price"}, {"Seller", "Price"})
in
Expanded
BUT I'm having trouble combining the two, I need it to also go thru all the different ASINs and I want my code to look more like
(Page as number, Asin as text) as table =>
let
Source = Web.BrowserContents("https://www.amazon.com/gp/offer-listing/" & Asin & "/ref=olp_page_next?ie=UTF8&f_all=true&startIndex=" & Number.ToText(Page)),
#"Extracted Table From Html" = Html.Table(Source, {{"Seller", ".olpSellerName"}, {"Price", ".a-color-price"}}, [RowSelector=".olpOffer"])
in
#"Extracted Table From Html"
How do I adjust the next Query to look at a list of Page numbers and a list of Asins?
hi mrpowrbihelpty this code for replacing the query starting with PageRange
let PageRange = {0,10,20,30,40,50}, #"Converted to Table" = Table.FromList(PageRange, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Pages"}}), AddASIN = Table.AddColumn(#"Renamed Columns", "ASIN", each {"aaa","bbb","ccc"}), #"Expanded ASIN" = Table.ExpandListColumn(AddASIN, "ASIN"), GetData = Table.AddColumn(#"Expanded ASIN", "Custom", each (try {_, GetData([Pages],[ASIN])} otherwise null)), GetTable = Table.AddColumn(GetData, "Table", each [Custom]{1}), //this part may return error if the output of GetData doesn't have 2 parameters #"Expanded Table" = Table.ExpandTableColumn(GetTable, "Table", {"Seller", "Price"}, {"Seller", "Price"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded Table",{"Custom"}) in #"Removed Columns"it converts list to table, and adds second column with ASIN code
GetData in next step is using these 2 columns as input for the parametersHi Stachu,
Thank you for your response!
I tried using your suggestion with a random ASIN. (see below). This example had 3 pages of data it should have pulled in (24 lines), but the third page of data repeated for the pages that should have been blank resulting in 36 lines of data.
let
PageRange = {0,10,20,30,40,50},
#"Converted to Table" = Table.FromList(PageRange, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Pages"}}),
AddASIN = Table.AddColumn(#"Renamed Columns", "ASIN", each {"B01M0GB8CC"}),
#"Expanded ASIN" = Table.ExpandListColumn(AddASIN, "ASIN"),
GetData = Table.AddColumn(#"Expanded ASIN", "Custom", each (try {_, GetData([Pages],[ASIN])} otherwise null)),
GetTable = Table.AddColumn(GetData, "Table", each [Custom]{1}), //this part may return error if the output of GetData doesn't have 2 parameters
#"Expanded Table" = Table.ExpandTableColumn(GetTable, "Table", {"Seller", "Price"}, {"Seller", "Price"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Table",{"Custom"})
in
#"Removed Columns"Also, can I bring in an excel file with a list of ASIN's in stead of how you have "AddASIN = Table.AddColumn(#"Renamed Columns", "ASIN", each {"B01M0GB8CC"})," to list each one?
Thank you!
Hi mrpowrbihelp,
I've combined the code into one query:
let fnScrapeASIN = (ASIN as text) => List.Generate( () => [Table = null, page = 10, PrevTable = 0], each [Table] <> [PrevTable], each [Source = Web.BrowserContents("https://www.amazon.com/gp/offer-listing/" & ASIN & "/ref=olp_page_13?ie=UTF8&f_all=true&startIndex=" & Text.From(page)), Table = Html.Table(Source, {{"Seller Name", ".olpSellerName"}, {"Seller Price", ".a-color-price"}, {"Condition", ".olpConditionColumn > .a-section"}, {"Ships From", ".a-unordered-list.olpFastTrack > :nth-child(2):nth-last-child(2)"}}, [RowSelector=".olpOffer"]), PrevTable = [Table], page = [page]+10], each [Table] ), Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQzNDUxNzA2MVGK1YlWMjA3NjWztDA2jVCKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ASIN = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ASIN", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Skip(fnScrapeASIN([ASIN]),1)), #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"), #"Expanded Custom1" = Table.ExpandTableColumn(#"Expanded Custom", "Custom", {"Seller Name", "Seller Price", "Condition", "Ships From"}, {"Seller Name", "Seller Price", "Condition", "Ships From"}) in #"Expanded Custom1"All you have to do is to replace the bold part in the Source-step by a reference to a table with your ASIN-numbers (column named "ASIN").
This is a recursive query that retrieves the next page (+10) if the result of the current request is not equal to the result of the previous request (as the page seems to always return the last page, even if the numbers go up)
