MySQL LOAD FILE Statement Rescues Huge Database File

A client needed to use an existing database, which turned out to be a big gargantuan thing, but to be able work with it on the Web I needed to convert it from MS Access to MySQL.

Instead of trying to convert the Access files to MySQL I chose to work with delimited text files that were available. Ten text files were used to represent ten tables for the database. It was easy enough to read in the delimited text files into OpenOffice Calc by identifying the delimiting characters and importing the new .csv file into MySQL, but one file in particular was really big and presented a problem.

I tried to work with the big text file in Notepad and HTML-Kit, but it was too cumbersome due to the file size – over 500,000 records! Not enough copy and paste operations could be done in a day to make me want to go through it again when the original database gets updated and I have to convert the scary big text file into MySQL format again. Knowing that the original database can be updated at any time, I needed a way to get that huge file into MySQL with a minimum of effort.

After looking around a bit, I downloaded a piece of conversion software called Full Convert from SpectralCore.

Installation went smoothly and I noted there was an uninstall feature in case the software wasn’t a good fit for my needs. It seemed promising in that there are several databases that can be converted back and forth, but the function to convert a text file to MySQL tables did not work for me. The text file used the characters ‘^’ to terminate fields and ‘~’ to enclose fields. However, the software did not give a choice for the field-enclosing character.

Since it didn’t work for me I uninstalled it, but the Full Convert software may be promising if you’re moving files from one database system to another. Here’s the list of DBs they work with:

Supported source databases
– Microsoft Access, dBase, FoxPro, Microsoft Excel documents, Interbase / Firebird, Lotus 1-2-3, MySQL, Oracle, PostgreSQL, ODBC sources, Paradox, SQL Server, Delimited text files, XML

Supported target databases
– MySQL, SQL Server, Oracle, PostgreSQL, Access, Interbase/Firebird

Further digging lead me to a solution for this problem of not being able to import a really big, delimited text file into a table in MySQL.

LOAD DATA INFILE

The solution is to read the file directly into MySQL using the MySQL statement “LOAD DATA INFILE”.

For my situation the code that worked was this –

LOAD DATA INFILE ‘data.txt’ INTO TABLE databasename.tablename FIELDS TERMINATED BY ‘^’ ENCLOSED BY ‘~’;

Don’t forget the trailing semi-colon and don’t use single quotes around the database and table names (and leave the semi-colon in the space provided for the Delimiter).

How did I find my solution? Searching at WebmasterWorld for “convert text to mysql” did the trick.

A simple post by a fellow developer that was written to help someone else out a couple years ago helped lead me to the LOAD_DATA function. Thanks, dreamcatcher!