Tool to export text /XML file to mysql

Can anyone please confirm about the tool which we can use to export text file into database like mysql . I need freeware on it. because I want to export the Awstats report into the database which will allow to use non Awstats tool to generate other report such as trending, data comparison/usage matrices and etc..?
Also confirm if Awstats provide such kind of solution..?
Brijeshk9Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Michael WorshamStaff Infrastructure ArchitectCommented:
Is there a way to have awstats convert its existing data files to xml?

Look at these parameters in your AWStats config file:

   1. BuildHistoryFormat=xml
      AWStats data base files will be in XML format
   2. BuildReportFormat=xhtml
      AWStats report pages will be XML compliant

---

If you want to get the AWStats database in XML format, you have to rebuild everything from the original log files.

If you only need to have the AWStats reports to be XML compliant, you can rebuild them from the existing AWStats database file.

---

The only way to import the XML data into MySQL, however, is to design a custom built XML parser under something like PHP (unless your using MySQL 5.1 -- http://dev.mysql.com/doc/refman/5.1/en/xml-functions.html -- which can use stored procedures to do the import).
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Tony McCreathTechnical SEO ConsultantCommented:
If you can get the data in XML format, you can create sql scripts from it using XSLT.

XSLT is a standard language (based on XML) that can transform XML into any other text format. It is often used to write html pages from the xml data, but can also be used like this to create sql.

There are a lot of XSLT tools out there. just Google "XSLT tool"
0
Brijeshk9Author Commented:
Hamm, I have made the required changes(set both parameters suggested by you) in conf file and now report is coming in XML format but file name is coming with ext: txt .while data under the file is coming in XML&? And now file size for new file created with xml data and ext as text is bigger than the previous text file have data and ext in text  format.
Dont you think it will decrease the Awstats performance for xml data..?  And I found xhtml pages are having smaller size then html but these pages can be opened in browser having higher version..!
Please suggest like which one (XML with xhtml or text with html) is better for Awstats process & performance,because performance with more features (as we get from SEO based tool) is major concern for me.
Is it possible with Awstats to directly store the report in database..? if yes then which is better one as I am looking for freeware on it, also confirm if I need to install anything  extra to get my data in XML..?      
I will look for xslt but can we export text file directly into databse..?
Please provide you valuable inputs for all my questions/doubt given above....!
Thanks...!!      
0
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

nripCommented:
the awstats  performance does not degrade for XML o/p..  Further its better to get the o/p in XML and use a SP to throw it into MySQL...
and yes the db an directly import text/csv/xml files... if you are going to manually export, try out phpmyadmin to do it for you...it has a import feature...which you can use for the same..
0
Brijeshk9Author Commented:
Ok, I have attached one sample text file which I need to export automatically into mysql..?an you please suggest on it. Like if I can export the same file automatically into db and can fetch from db to check for website trend and other detail for particular month  and etc on web or by any other third party tool...?
awstats052009.mywebsite.txt
0
nripCommented:
well brijesh, you can write a  script in any language  (PHP, Perl, etc. ) to parse this and put this in a MySQL DB.  

For interpretive analysis, you can write another tool...however why don't you use awstats itself? Its probably the best too in this category I have seen till date...If you don't use it, then you are better off using the server logs directly for analysis...
0
Brijeshk9Author Commented:
I will really appreciate if you can provide me the solution/document(script) on it&?
 For more detail you can check the attached report file in my previous question(may have size till 200-300 MB),
Also confirm if data in attached file is in uniform format for any database, if not is there any way to get the data in uniform format.
To get more detail on it I have attached the Awstats.pl/and conf file with other required files in Awstats process.(all are example file  having ext:txt)

awredir.pl.txt
awstats.model.conf.txt
awstats.mywebsite.conf.txt
awstats.pl.zip
0
abelCommented:
Not sure if I understand this thread correctly, but what I get from skimming over the comments is that you have awstats, you have an option to get XML from awstats, but you don't want to use that option and instead would like to use the plain logfiles and use an extra tool (yet to be build) to create XML from the stats? Why do you want to do extra work if it is already there or am I missing something crucial here?

I'd like to help, but then I also like to know why you choose to recreate something that's already there, I'm sure there's an obvious reason I'm missing. Can you elaborate please?
0
Brijeshk9Author Commented:
No let me try to more explicit on it. I am getting the report file in XML format (file attached).then I need to insert it into database&.? So that further I can fetch the required data from database&?
Now please help to insert these xml files into database..?
Also confirm if I can directly insert data(that XML report) into DB: like mysql and then can fetch it easily from DB...?
For example I have attached a small report from Awstats in xml format..!
awstats052009.mywebsite.txt
0
abelCommented:
Assuming you have MySQL 5.1.5 or higher, you can do such a thing. It requires a bit of work. Because your dataformat is not completely plain and linear, you'll have to decide your self what parts you want to import into what tables. Earlier, you got an answer about a custom XML parser. However, that does not seem to be necessary for more recent versions of MySQL.

Here's an excellent blog that explains you step by step how to import your data. My suggestion is to first create a simpler XML file then this one and to try it alongside that tutorial. Then you can move upwards to the more elaborate XML like the one from AWStats: http://rpbouman.blogspot.com/2006/03/importing-xml-data-into-mysql-using.html
0
Brijeshk9Author Commented:
Thanks .let me try it...
0
Brijeshk9Author Commented:
Sorry I do not have good knowledge on Mysql/XML/Perl. Let me check with my DBA if he can help me in it. Will update you accordingly&&Thanks..!

0
abelCommented:
Ok. That knowledge is indeed quite handy if you want to deal with xml+mysql ;-). If you need professional hands-on services, let me know and perhaps I can arrange something.
0
Brijeshk9Author Commented:
Thanks, Let me try first with my DBA ,if you can share/explore anything more on it, will be a great help for me/my DBA...!
0
Brijeshk9Author Commented:
stiil waiting for responce from my DBA, will update in next 2 -3 days
0
abelCommented:
take your time, I'll be around :)
0
Brijeshk9Author Commented:
abel , I am stuck because of no support from DBA end, now can you pls tell me how to proceed on it.. for more detail you can check the attached report I posted in my comment on 28th May 09.
(file: awstats052009.mywebsite.txt)
0
Brijeshk9Author Commented:
now i need to insert that file into db..?
0
abelCommented:
thought I left a comment, apparently not... here's an extensive one:

You ask for additional help, but all I can offer, is what is basically explained in the step-by-step guide on that link. That you don't get assistance from your DBA guy to do DBA related task is quite a bummer. To get this working you need at least a basic understanding of XML, XPath, MySQL and the SQL query language.

The challenge
We have, however, yet another challenge. The XML that you provided is quite loosely structured (but based on an XSD nevertheless) and there's many data in there. It is impossible for me to "just show you how to import it" because I have no idea what data you want to go where. This is getting pretty close to professional services, but even then, I would first draft a plan for a table structure, then you'd tell me what information you want from those logs and how, and finally we decide on an import structure.

I could tell you how to extract a single value (I will, see the code snippet), but I'm afraid that won't bring you very far. This task is a couple of days work, can be even weeks if you still have to decide on the structure and what you want to do with the information.

The task / track / solution
To give you some idea of what you're up to: in the XML there are several sections. Each section has an id which seems to be something else then an ID, i.e., it seems a categorization, not an identifications. The ids are "keywords", "sider", "general" etc (they reflect an enumeration type of the XSD). Inside these, there seems to be a certain html-table like structure, larded with some extra information (comments and fields I have no idea how to interpret without knowing where they come from or what you want to use them for: "nu", "recnb"). The types of data are untyped, but with a little reference-searching we may find out what the inner types are for each one of them.

Alternative?
What I don't understand is, why do you want to move it into MySQL into a database? It is an aweful lot of work, AWSTATS format is internal (yet documented) and AWSTATS is already an anyalyzer tool. Do you plan on doing additional analytics? You may have more chance and success with using traditional analytics tools that more closely serve your purpose.

Answer: no
Reading back, you ask "Is it possible with Awstats to directly store the report in database..?". The simple answer is: no. What you could work out a batch-updating scheme after you've created a tool for importing the text of xml data into MySQL.

Conclusion:
If you want to do this, prepare for quite a bit of time planning, preparing, learning and designing and finally: building. AWSTATS is open source, if you've made it eventually, the community might be quite pleased if you could share the results of your effort back to the open source group (not sure of the license type, but some license types actually demand this).

The answer is not "you cannot do this", but the answer is: a tool does not exist, so either create your own, or choose another approach (i.e., there are logging systems that support writing to the database directly, think log4j for instance, but this is not logging, this is analytics). Either way, you're up to quite a bit of work to get this done (and yes, if requested, you may contact me for professional services).

-- Abel --

PS: there's an error in the XML file: it has an element, the root node, going by the name "xml". This is illegal in XML: elements cannot start with [xX][mM][lL].

<!-- will extract one field into a table -->
 
-- load the xml
set @xml := load_file('D:/Data/awstats.xml');
 
-- load the data of header into a table, first two rows only
insert into awstatstbl values (
    extractValue(@xml,'/*/section[@id='header']/table/tr[1]/td[1]/text()'), 
    extractValue(@xml,'/*/section[@id='header']/table/tr[2]/td[2]/text()'))
    ,
    (extractValue(@xml,'/*/section[@id='header']/table/tr[1]/td[1]/text()'), 
    extractValue(@xml,'/*/section[@id='header']/table/tr[2]/td[2]/text()'));
 
-- etc, you'd need to wrap this in a little script, see prev. link

Open in new window

0
Brijeshk9Author Commented:
Thanks for all the information, I have already discussed with my client for some other better tool.
But Client has decided not to go for any other tool and not interested to purchase anything else, so this is the big problem for me.
Now let me clear the current requirement, I checked the report file which is showing data in very complex format.
Client want to put this all data in database(using free tools) so that he can use that database to check for the website trends like hits, total visits ,unique visitor, bandwidth utilization of particular month/date. Which can be checked by any third party tool or by running any query in DB to see the record in meaningful format.
Please clear some of my doubts..
Ok Awstats cant put data directly into db, but can Awstats take the data from DB directly, please share the evidence if any as I need to give the same to client&?
Is it possible to put some of records from this report into db (m not sure because table is very complex), if yes pls. suggest the better option for the required records lie: hits, bandwidth utilization per, particular URL /keyword hits per day/month/year..?
Any better way to understand the data in report, because it is in complex form..?
Also confirm if we have any better option free/paid.?
Will it be better if we take help from dev resource to write a program to simplify the data and then we can insert it into db, he will also create application (web) to view that data in meaningful format-this we need for long term process, like we can get the record for last 2- years..?
Kindly share some the evidences which I can share with my client, because now client is asking for Timeline on it, which is not possible for me now..?

0
abelCommented:
Have a look at this site: http://www.nltechno.com/awstats/awstats.pl?config=destailleur.fr, it shows the awstats output, with all the information you want.

Now, it looks as if your client is asking to do that all again: write the awstats data that was collected and interpreted by awstats to a database and then read it back and show it.

You ask for a free tool. Awstats is a free tool and does everything you ask and more. It uses its own format, it doesn't need to get data from a database. However, all logfiles are saved in a typical "logfile style", i.e., in a file system as files. Putting this data inside a database and writing a program to get it out again will be the same as saying "I want to go from A to B by train, but I don't want to use a train, please create a train for me". Of course that's possible, but is that really what your client wants?

If so, just fool your client: place the data files as file-blobs in the database, create a little wrapper tool that gets the files out (places them in a directory) and that runs awstats. It would be a waste of your effort, but you achieve three things: 1. the data is in the database (but you can't do much with it, it is in blog-format), 2. the data is retrievable and can be interpreted by a free tool: awstats. 3. your client is happy (until he finds out).

Never forget that interpreting statistics and writing your own visual tool for it is very time consuming (think years, not months). The free tool is already in your possession (awstats), why use another free tool (there isn't any that can use awstats files, but you may find another free statistics tool) to do the same task?

-- Abel --
0
Brijeshk9Author Commented:
Thanks Abel ,I agree with All you comments given above.
As I was doing research on it since last 15-20 days, so will it be the right time to tell my client that DB solution is not feasible with Awstats..? and if yes- can you pls. share some of the evidences/proven artifacts , so that I can share with client.
I think you can better understand my query over here, so pls. help me to put the evidences in front of client.
Quick response will be really appreciated..!!
Thanks for the Help.
0
abelCommented:
I understand that quick responses are appreciated but when it is day at your place, it is night here, so don't expect miracles ;-)

It will be hard to gather "evidence". There will be no whitepaper or article that says "do not use database with awstats". But it would be the same as trying to search for a tool that can do a database connection with the logfile of a poker online program: it just doesn't exist.

Awstats analyzes logfiles (text, not database) of websites. It uses an intermediary xml logfile for own internal use (the one you posted). Awstats has chosen text files for three reasons: speed, ease of analysis (choice of language was Perl for the same reason) and ease/indepency of installment (install perl and awstats and it just works, no complex db installments required). All this info I found on the awstats homepage: http://awstats.sourceforge.net/

Of course it is not impossible to "hack your way" into another program, since it is open source it is not very hard to do. But adding database support for functionality that is already there (to analyse the analyses?) is really overdoing it.

If your client really wants this done I can help you setup the system with MySQL or SQL Server or whatever database. The trick is then to use one table for each of the tables in the logs. Displaying this data through a webpage should really only be done by using awstats and changing it such that it can read from the database. This will be a few weeks work (and totally redundant, but hey, if the client really really wants it...) and I can give you a quote on that if requested by you or your client (see my contact details in my profile).

To make it crystal clear to your client, draw him something like this:

Current situation:
W3 logfile > AwStats Datamining > Analysis XML > AwStats display(html)
New situation:
W3 logfile > AwStats Datamining > Analysis XML > Your tool "todb" > Database > Your tool "fromdb" > Analysis XML > AwStats display (html)

I'm sure that's not your client's wish. You can ask your client an open question what the target is of storing this in the database and what advantage (s)he expects from it. You can then offer that advantage from another angle (for instance: installing awstats?).

Some servers can store logfile information in a database (IIS, log4j, log4net). That type of logging information cannot be read by awstats.

-- Abel --

PS: if it is of any help to you if I talk directly to your client to help you out, just let me know.
0
Brijeshk9Author Commented:
Thanks abel,let me discuss with my clinet, will let you know..!!
0
Brijeshk9Author Commented:
I have confirmed with client and now we are looking for solution like freeware only. thats the main reason behind choosing Awstats.
Now he is looking for any better solution/suggestion to use log parser tool provided by Microsoft as third party tool for reporting inserting logs reports to DB
Here exactly we are looking for exact number of hits (internal /External) like Nimbus/Keynotes provide count for internal hits.
Thanks for all your support and looking for value added solution/suggestions on it.
0
abelCommented:
There are literally thousands of them, it can take the good part of a month to investigate them all. Your client should ask the question whether freeware / open source is the right track if awstats does not suit his or her need, it is one of the better analysis software around.

Try this google search, it give you a bunch of commercial and freeware (add freeware/open source to the search) solutions: http://www.google.com/search?q=log+statistics+software+overview

I personally use things like awstats (if provided by hosting provider), netstat and google stats, but it depends on my clients wishes. Netstat is commercial and free and in commercial edition it can give much more info. Clients using blogging software usually use plugins for the specific needs of blogging stats.

I have rarely been in need of very precise statistics (the overall picture has always been more important, unless when tracking abuse), I'm afraid I can't help there, but all statistics software give the exact numbers of hits (awstats too iirc), views and visits. Splitting internal and external is usually a matter of adding filters, as internal is hard to define. Some statistics software automatically remove non-wan requests from the counts, some don't, because the percentage of internal hits is often so low (if you have hundreds of thousands of visitors, these view hits internal really don't influence the outcome significantly), but I can't judge for your situation of course.

-- Abel --
0
Brijeshk9Author Commented:
Thanks for sharing your valuable suggestions..!
0
abelCommented:
@Brijesk, thanks for the compliment and that you consider my comments valuable. I'd kindly request you have a look here for in the future if you continue asking and grading questions through EE: http://www.experts-exchange.com/help.jsp?hi=403
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Development

From novice to tech pro — start learning today.