MySQL

Recently, A project that I was working on required that I import a fairly large BCP file containing around 260k entries. I spent quite a while trying to work out the best way to import it directly into my MySQL database with little or no pre-processing required. The answer turned out to be quite simple, I created a private directory (as the file contained some confidential data) on the server that was hosting MySQL, I then uploaded the 100mb bcp file to it and also created an SQL file with the following content:

LOAD DATA LOCAL INFILE 'BCP_FILE' INTO TABLE TABLE_NAME
FIELDS TERMINATED BY '@**@'
LINES TERMINATED BY '*@@*'
(TABLE_FIELD_1, TABLE_FIELD_2, TABLE_FIELD_N)

You then replace BCP_FILE, TABLE_Name and TABLE_FIELD_* with your details (BCP_FILE with the BCP file’s name, TABLE_NAME with the name of the table you want to import the data into and finally replace TABLE_FIELD_* with the fields in your table which match those in your BCP file). Once you have done this, save the file as something like bcp_import.sql. Just as a quick sanity check, you should now have a directory with two files in it, one bcp file and one sql file. Now for the fun part . Login to the server via SSH (or terminal if you are local), then login to mysql with:

mysql -u YOUR_USERNAME -p

You will then be prompted to enter your password. Next, select the database with the table that you want to import into:

use DATABASE_NAME;

Finally, run your import file with:

\.bcp_import.sql

That’s it, with a bit of luck, the data should now be in your database table. Be patient with it though if you are importing a large BCP (or have a very slow machine).

As usual, comments, suggestions, problems, thanks below