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
-- 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
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
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).
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).
ASKER
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
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
just run with community, it's what everyone is using.
ASKER
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
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
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.
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.
ASKER
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."
"The selected file was generated by mysqldump and cannot be restored by this application."
interesting error message.
have you tried just running the query rather than "restoring" the query?
have you tried just running the query rather than "restoring" the query?
ASKER
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...
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...
ASKER
Grade "A". Great having your help today.
ASKER
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.