Format and Combine Merch by Amazon Sales CSV reports in seconds with PowerShell

I dabble a bit in Merch by Amazon and have grown to hate the monthly sales reports they give you. For whatever reason, Amazon dumps a bunch of useless information in the first column of the CSV file. Why they do that, I have no idea, but it makes the CSV files difficult to use from a data analysis perspective. There are some tools, most of which you need to pay for, out on the market where you can get your sales information. Since I’m only dabbling with Merch by Amazon, I wasn’t willing to pay for anything, so I decided to solve the problem with PowerShell.

You can use this script on pretty much any Windows machine and can easily adapt it to run it on Mac or Linux with PowerShell installed.

This is what the original reports look like.

U.G.L.Y… the interesting information doesn’t start until row 13. All the other stuff at the top is just stuff in my opinion so we’ll get rid of it.

This is what the combined CSV file will look like.

That is all the data you need to create valuable reports with your sales data using SQL, PowerBI or just using Excel. It runs super-fast; I ran the script on all my sales report back to October of 2017 and it took less than 1 second to create the combined CSV file.

If you’re new to PowerShell and have some trouble getting it running feel free to reach out to me in the comments and I’ll see if I can help you get it going.