Disclaimer
This is my personal blog. The opinions and views I express are my own. The information I provide is on an as-is basis. I make no representations as to accuracy, completeness, currentness, suitability, or validity of any information on this blog and will not be liable for any errors, omissions, or delays in this information or any losses, injuries or damages arising from its use.

Format and Combine Merch by Amazon Sales 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.

Merch by Amazon Report Before

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.

Merch by Amazon Report After

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.

Code: https://github.com/signalwarrant/Powershell/blob/master/Format-MerchReports.ps1

1 Comment

  1. suvodipmitraSuv | | Reply

    Hi David,
    The concept is pretty useful, I had to combine a lot of files like these, and I used Excel earlier, however that is resource intensive and a bit slow. However when I tried this script, I didn’t get any error nor the output file – I tried it both in c:\ and d:\ (just in case c:\ didn’t have permission). I also ran “Set-ExecutionPolicy -ExecutionPolicy Unrestricted” because otherwise PS was throwing up an error. Is there a way I can see why the script is not working?

    Cheers,.
    suv

Leave a Reply to suvodipmitraSuv Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.