Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1526
  • Last Modified:

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

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
techsedge
Asked:
techsedge
  • 6
  • 5
1 Solution
 
virmaiorCommented:
(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
 
techsedgeAuthor Commented:
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
 
virmaiorCommented:
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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
techsedgeAuthor Commented:
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
 
virmaiorCommented:
just run with community, it's what everyone is using.
0
 
techsedgeAuthor Commented:
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
 
virmaiorCommented:
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
 
techsedgeAuthor Commented:
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
 
virmaiorCommented:
interesting error message.

have you tried just running the query rather than "restoring" the query?
0
 
techsedgeAuthor Commented:
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
 
techsedgeAuthor Commented:
Grade "A". Great having your help today.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now