Solved

Easily view and extract data from MySQL dump files...

Posted on 2008-06-17
11
1,469 Views
Last Modified: 2011-10-19
I have been given a bunch of .sql files. They are basically text files that contain data that was "dumped" from a MySQL database. I know this because the beginning of the files contains the following:
-- phpMyAdmin SQL Dump
-- version 2.9.0
-- http://www.phpmyadmin.net

I need some hand-holding here. I think that my ideal result will be to somehow get this data in an Access database and from there be able to pick and choose what to export - probably to an Excel spreadsheet. I do not have access to the original databases. I have only the .sql files and MS access to work with. I do not have any database experience, so please keep it simple. I will be happy to use whatever other software products are needed, as long as they are "free"and hopefully have a "GUI" and not a lot of command line action.

Thanks
0
Comment
Question by:techsedge
  • 6
  • 5
11 Comments
 
LVL 20

Accepted Solution

by:
virmaior earned 500 total points
ID: 21803139
(1) Depending on the format of the data, it might  be possible just to load it up into Access.  This is rather unlikely.  To fix this, you would have to modify a large number of the queries/

(2) In lieu of this, I would recommend installing MySQL on the computer where you want to look at this:
   (a) install MySQL from mysql.com
   (b) after everything is setup, you will have to suffer the command line for a little while so that you can import  the queries
   (c) install MySQL's ODBC driver ( http://dev.mysql.com/downloads/connector/odbc/5.1.html )
   (d) go into Control Panel -> Administrative -> "Connections" and add a connection to the correct DB in the MySQL install.
   (e) open access, add new tables that are "linked tables" pointing to the corresponding data in the MySQL DB.

Obviously, this is far from trivial to do...
0
 
LVL 1

Author Comment

by:techsedge
ID: 21803323
OK - so you are saying there is no way to "Easily view and extract data from MySQL dump files..."?

I can install some software, but in your example, step "b" will blow me away almost certainly. Am willing to give it a shot if I must.
0
 
LVL 20

Expert Comment

by:virmaior
ID: 21803392
the difficulty is that they are by definition SQL files, which means they are designed to be loaded into a database.

If you can get them to send the files as CSV, then you could open them in excel or import them into Access without any issues.

If you just have the SQL, then you either need to work with the SQL in a database or just start manipulating them down to the equivalent of CSV files.  (e.g. go through and strip out all of the statements like "INSERT" etc).

but even CSV files may not be helpful.  Data in a database is structured in a way that is not an option for a flat file like CSV.  By this, I mean that a row might not appear to have meaningful data if you just look at it in excel or something like  that.  (this of course depends on what the data are).
0
 
LVL 1

Author Comment

by:techsedge
ID: 21803741
I have no choice - the dump files are all I get - the original data is long gone.

So - from the mysql page, I see 2 choices.
MySQL Community Server or MySQL Enterprise - Will the "community" version be any less painful than the Enterprise trial?

Thanks
0
 
LVL 20

Expert Comment

by:virmaior
ID: 21804136
just run with community, it's what everyone is using.
0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 1

Author Comment

by:techsedge
ID: 21804483
I went ahead and installed it, did the "Standard" configuration, and now have MySQL running. I have also downloaded and installed the MySQL ODBC driver as outlined in step c -  

Looks like I still need to import the sql dumps somehow. I see that it points to a bunch of files under C:\Program Files\MySQL\MySQL Server 5.0\data\
as its "Path to dataset root". I can't seem to figure out further than this what to do next? The GUI doesn't offer any right click options or other help for importing.. When trying to run the MySQL Administrator program, I am being asked for "connection information" as shown in the attached file..

Thanks for the help thus far!


connect.png
0
 
LVL 20

Expert Comment

by:virmaior
ID: 21804542
it should have asked you to set a password when  you installed MySQL

enter the following:

Server Host: localhost
Username: root
Password: (whatever you picked during install)

Then click OK.

just leave the pw blank if you did not set one.
0
 
LVL 1

Author Comment

by:techsedge
ID: 21804931
OK - I am in... Have been messing with the options a bit, I can't get it to "Restore" any .sql file. The error presented is:

"The selected file was generated by mysqldump and cannot be restored by this application."


0
 
LVL 20

Expert Comment

by:virmaior
ID: 21805239
interesting error message.

have you tried just running the query rather than "restoring" the query?
0
 
LVL 1

Author Comment

by:techsedge
ID: 21806273
virmaior,
Using the "MySQL Query Browser", I have been able to do what I need, exporting from there direct to Excel -

Much thanks for the help...
0
 
LVL 1

Author Closing Comment

by:techsedge
ID: 31467950
Grade "A". Great having your help today.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now