Category Archives: Office

MS Excel import Awstats

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:

  1. Get the data into excel
  2.  Be able to have multiple months to select from
  3. No VBA (because I suck at that)
  4. 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.

Screen1

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.

Screen2Click 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.

Screen3On 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.

Screen4Next Screen should be fine, just ensure the data will start at $A$1.  Click OK and the data will be imported.

Screen5Now 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.

Office 365

Yes I know there are free Office Suites available and that free cloud storage solutions abound but I like Microsoft Office and so does the rest of the family.

office365When we wanted to upgrade from Office 2010 and in some cases 2007 I found that Office 365 Home was hard to beat.  All Office applications, Word, Excel, PowerPoint, OneNote, Outlook, Publisher, and Access, fully installed and kept up to date on 5 computers (PC or Mac), plus 5 tablets and phones, (Windows, Android or Apple OS’s).  Plus the advantage of having 1 TB of storage per user.

Shopping around I have been able to get it for around $80 (Australian) per yearly subscription which in my opinion is a bargain, especially with all the storage available, could not get 5TB of storage for that price let alone all Offices apps and 60 minutes of Skype calls a month.