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.
Why didn’t you try doing a web query with excel to pull in the live data? That way you can always have your spreadsheet up to date.
That could be done but still need to get them into some sort of decent form to view. At least by importing the raw stats I don’t have the formatting that was put into the web to contend with.
This is amazing and has saved me an incredible amount of time.. I have been looking all over the net!
Thank you so much!!
Thank you. Glad you have found it and it works for you.
Keen to see what you put together but the download seems to have stopped working. Can you email me the file?
Link has been fixed.
Possibly a silly question…
After importing the data, I don’t have anything loaded in the “Front” tab… My assumption is that this is because my host doesn’t actually have a ‘header’ in the file, it just truncates the file at the end of the month and then continues writing data into the new file? (The first file (which I assume should have had the headers) is long gone out of rotation, and this is a hosted site so I don’t have su access to the server.)
Do you know if this is the issue, or if it is something else? And if the missing headers IS the problem, are there sample headers somewhere that I can just stuff in there to make it work?
Hi Kay,
The text file doesn’t have Headers as such, It does have a list of mapped positions for the various categories. When imported these usually start at Row 10 and go to about Row 39. Basically what the spreadsheet does is to look for these starting positions and subsequent end positions and get the data in between. Each AWSTATS text file is basically the same so they should all import and work the same.
Now after saying that I did have issues with Excel about the middle of last year as an update to Excel (I use 365 so it updates all the time) changed the data import to a “Get and Transform” function. The file would import as text only so no data would be seen in the associated areas. It needs a bit of editing to get Columns 2 to 6 to be imported as Whole Numbers and Column 1 imported as text.
Hope that helps
Hi Michael,
Awesome work I love it! God bless you.
Glad you like it.