Like many others I use Awstats for checking Website statistics. I also like to put the data into MS Excel so I can view it better. There has never been an easy way to do this. You can highlight the data and copy paste it but that only gives you what is represented on the website and not the underlying data that builds the page.
What I wanted was to be able to import the Awstats data into MS Excel and then use the Functions and Formulas built into excel to build my graphs etc.
Spent time looking around the web for something that will do this and could really only find one program that would do what I want but you have to pay and then have to download an update every 3 months or so to keep it working.
Time to build my own.
I consider myself a bit handy with Excel so applied myself to the task. My criteria was relatively straightforward:
- Get the data into excel
- Be able to have multiple months to select from
- No VBA (because I suck at that)
- Pull the info and be able to see it similar to Awstats but be able to have it looking like I want.
After a fair bit of trial and error I have come up with a spreadsheet that works for me and am happy to share it free of charge for anyone that might be interested in a starting point for their own project. I still have a bit to do with it but it works and does the job. Download link at the end of this post.
The Spreadsheet.
So to get it to work you will need to download your Awstats text files for the months you need. Just FTP into your site and go to your tmp folder then the Awstats folder (I am assuming cPanel is used). Download the months you want. The files are of the form awstats092015.domain.txt so should be relatively easy to find the month you want.
Open the Speadsheet and select the sheet you want use along the tabs at the bottom. I have left all sheets open, the Sheet called “Headings” is used to reference the month and the headings that Awstats uses for beginning and end of its data. “Testing date” is just my original test sheet and I left it just for testing, this can be deleted.
Click on Data then under “Get External Data” Click on “From Text” then browse for the Awstats file and then click on Import.
On the new screen select Delimited then click Next.
On this screen Select both Tab and Space as your delimiters and also ensure that “treat consecutive delimiters as one” is checked then click on Finish.
Next Screen should be fine, just ensure the data will start at $A$1. Click OK and the data will be imported.
Now go back to the front page and select the month you imported and your data will appear.
That’s about it. If you need to redo the month just delete all the data and re import the data.
All formulas etc are unprotected so you can play with them. Some are a bit convoluted but they get the job done at the moment.
I have only done the sheet to reflect a small amount of stats at the moment so if you need others then you should be able to figure out what to do from what I have done.
Get the zipped file and the text of this post here: Download
Any feedback would be appreciated and if anyone comes up with some good improvements they would like to share it would also be appreciated.