MySQL: Stata (or StatTransfer)

[Josh McSorley]

Hi:

I have several SQL files, and since I have little experience using MySQL I would like to read them/convert them into Stata.
My setup:
- Win 7 x64 computer
- Stata 12.1 MP
- StatTransfer v12
- installed both the 32- and 64-bit MySQL drivers (http://dev.mysql.com/downloads/connector/odbc)


My first instinct was to use StatTransfer. When I select Input File Type: ODBC Data Source. ODBC Data Source: <my name> - MySQL ODBC 5.3 ANSI Driver, I was unable to see how I could actually select one of my SQL files. I contacted StatTransfer and they were not at all helpful.

I next tried Stata but am not having any luck. I saw these two helpful posts but I was not able to get their instructions to work:
http://www.andrewdyck.com/connect-to...e-using-stata/
http://statadaily.ikonomiya.com/2011...rst-encounter/
For example using the first source I get:
. local db "DRIVER={MySQL ODBC 5.3 ANSI Driver};SERVER=localhost
invalid syntax
r(198);


I am pretty sure that I am doing something wrong with configuring the ODBC connection. I followed the instructions here:
https://www.youtube.com/watch?v=K3GZidOwGmM
When I am at the Administrative Tools > ODBC Data source > Add Data source step of the video (around 1:46 of the video). When I hit test I get the error listed in my attachment:



If anyone has any suggestions for what I am doing wrong please let me know. I apologize this is probably a very basic question but I have been struggling with this all day with no luck.

Thanks,
Josh

[Joe Canner]

Josh,

I haven't used the Stata ODBC connector for MySQL databases, so I can't help you there, but I can see right off that your code that starts "local db..." is missing a double quote at the end.

Regards,
Joe

[Josh McSorley]

Hi Joe:
Thanks. Sorry I actually had the colons but did not get them when I copied and pasted.
I just re-tried with one or two colons and continue to get the r(198) error message.

[Joe Canner]

Josh,

It's not the colons (semicolons?) I was worried about. Regardless of what text you assign to the macro db, you need to end the string with a closed quote. Otherwise, you ge a synax error.

Joe

[Josh McSorley]

Hi Joe:

Thanks and ugh my bad. I think I am entering the right commands now but am still getting an error:

. local db "DRIVER={MySQL ODBC 5.3 ANSI Driver};SERVER=localhost;DATABASE=C:\Users\Josh\De sktop\lVotes.sql;UID=root;PWD=pass;"

. local sql "SELECT a, b, c, d FROM userVotes"

. odbc load, exec("`sql'") conn("`db'") clear
The ODBC driver reported the following diagnostics
[MySQL][ODBC 5.3(a) Driver]Can't connect to MySQL server on 'localhost' (10061)
SQLSTATE=HY000
r(682);


NOTE:
The space in the directory for the database is being added by the forum here --> there is no space in the original

[Brendan Halpin]

My understanding is that OBDC is for connections to a live database, whereas you seem to have SQL dump files (textual representations of the structure and content of the database). The best (but somewhat onerous) option would be to set up a MySQL database, load the data into it, and then use OBDC. Alternatively, there may be ways to extract the content into a CSV file, particularly if there is only one table in the database.

[Josh McSorley]

Hi Brendan:

Thanks yes I think your description is right (someone sent me a bunch of SQL files which are sitting on my hard drive).

Converting them to CSV would be great. I am assuming this is something StatTransfer would do. Does anyone have experience with this (sorry I know this is a bit off-topic)? There does not seem to be a way to select a particular SQL file: see attached.



Perhaps there is another tool that people know which convert SQL -> CSV?

[Joe Canner]

I think using StatTransfer will have the same limitations as using Stata directly: it uses the ODBC driver and requires a live database. Have you looked into the SQL files to see their format? Are they binary files that must be used by an SQL database, or are they exports from SQL?. The latter are usually a series of SQL statements that can be used to reconstruct the database. If this is what you have, perhaps you can post a sample and someone can suggest a method for parsing them.

[Josh McSorley]

Hi Joe:

I think it is the latter (they are text files which I can for example read in emEditor). Here are a few sample rows:

LOCK TABLES `btT` WRITE;
/*!40000 ALTER TABLE `btT` DISABLE KEYS */;
INSERT INTO `btT` VALUES (9,'2011-04-23 02:43:40','d','scrape',1,50,0,'scarywater_ann','sc arywater_scr','***','scarywater_6969');
INSERT INTO `btT` VALUES (65,'2009-12-05 04:54:32','d','scrape',1,50,0,'games_ann','games_s cr','***','games_6969');

I believe each file only has one table (I have about a dozen or so of these files).

There is another files called "schema.sql" which I am guessing has the MySQL commands to access these (I can add them if you like).

I also have seen several standalong programs that say they are MySQL --> CSV converters. For example:
http://www.convert-in.com/sql2csv.htm
I have not been able to get this to work: I think I need a MySQL server installed (which I do not). Any suggestions on this approach would also be appreciated

[Brendan Halpin]

You do need a server. The SQL files are more like do-files with in-line data than data files, so their structure is too variable for other program to read. Installing a MySQL server is not difficult, but there is a (moderate) learning curve involved in getting the data loaded. It's worth doing if you'll have to deal with SQL data in future.

If the data structure is simple (e.g., one table per file with one INSERT statement per case) an alternative would be to edit the text and massage them into CSV format. Note that the content of the INSERT statements looks quite like CSV, and it might be enough to remove the header, the "INSERT INTO `btT` VALUES (" and the ");". You'll lose variable names, and it would require some trial and error, but if this is a one-off problem it might be a practical option (search and replace in a text editor would do most of the work).

[Phil Schumm]

I also have seen several standalong programs that say they are MySQL --> CSV converters. For example:
http://www.convert-in.com/sql2csv.htm
I have not been able to get this to work: I think I need a MySQL server installed (which I do not).

There are several conversion scripts available to convert a mysqldump file (what you have) to SQLite (e.g., this one on GitHub, but use Google and you'll find several others too). If you're using OS X or Linux, you already have SQLite installed, and if you're using Windows it's just a double-click install—there are even Firefox add-ons that will let you read and manipulate an SQLite file (e.g., SQLite Manager). From SQLite, you can easily export the data into a delimited text file and then read them into Stata. If you don't have MySQL installed and/or don't have any experience with MySQL (and if you don't have a friend you can get to help you with this), this is the strategy I'd recommend.

[Josh McSorley]

Hi Phil:

Thanks (and sorry I am asking such basic questions: I should have understood my files better before posting).

I am having some problems with the first step: MySQL dump -> SqLite. I am using a Win XP x64 computer and it seems GitHub support for Win XP ended recently so I am unable to use the myql2sqlite code snippet. I tried googling but did not have much luck finding another conversion tool. Any suggestions?

And again I apologize to everyone here about being a bit off-topic for the forum, though perhaps other users will have similar files and this thread will be of use for them.

[Phil Schumm]

I am using a Win XP x64 computer and it seems GitHub support for Win XP ended recently so I am unable to use the myql2sqlite code snippet.

The script is just a text file—you can either click Download Gist or cut and paste.

I tried googling but did not have much luck finding another conversion tool. Any suggestions?

The search

https://www.google.com/#q=convert+mysqldump+to+sqlite

returns a couple pages of relevant links, several of which reference the same script I cited above.

Most scripts you're going to find—including the one I suggested (I've used it before myself, which is why I suggested it)—are shell scripts, which means you'll need bash to execute them. You can do this on Windows using Cygwin, however unless you have some experience using Unix/Linux (or a desire to learn), you probably don't want to try that. If you have a friend or computing center that can run the script for you on your mysqldump files and give you back the resulting SQLite files, then great, but if not, you're probably better off trying another route.

Note that the script I cited (and others like it) make only relatively minor changes to the mysqldump file so that it may be read by SQLite. Thus, one option would be to make these changes yourself manually in a text editor. A few of the pages returned by the search above describe the changes that have to be made, and depending on your particular file(s), you may only have to make a couple of them. If you decide to try this, just make very certain you don't change anything else in the file(s).

All this having been said, loading a mysqldump file into a running MySQL server and then exporting the data into a delimited text file is an easy (and quick) thing to do for someone with basic MySQL knowledge. Thus, if this is something you'd like to do quickly and don't anticipate having to do it again soon, your best bet is probably finding someone who is willing to do it for you.

[Joe Canner]

If this is a once-off activity, I would be seriously tempted to do what Brendan Halpin suggested in his second paragraph. From the snippet of the SQL dump file you sent, it looks like it wouldn't be too hard to do a little editing and make it into a CSV file.

[Josh McSorley]

Yes just treating this as a CSV file worked fine! There were some issues with Stata reading in my test file for some reason (hexdump did not indicate any unusual characters), but I just edited it lightly in EmEditor and then Stata read it in. So it looks like I am good to go.

Thanks to Joe, Phil and Brendan for your many fine suggestions and patience. It is greatly appreciated!