Import BCP output file into MySQL database

Sun, Jun 19, 2011

MySQL

MySQL

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 🙂

, , ,

4 Comments For This Post

  1. Ahmad Says:

    HI…

    i got issue on it

    Error MSG : ” This cell contains NuL characters.They have been converted to ASCII spaces (SP). Press ESC to cancel editing ”

    Please help to advise .

  2. Ben Says:

    Hi Ahmad,

    From a quick Google search, it look like that is an error from “HeidiSQL”, and not MySQL. Try connecting directly to MySQL!

    Ben

  3. avi lugassy Says:

    Hi guys,

    Just a quick note. If you can’t get your bcp_import.slq file to run using the above command, try the below:

    cd /path/to/file
    mysql -u USER -p DATA_BASE_name < bcp_import.sql

    You will be prompted for the password for: USER
    Enter it and leave the file to run, it may take a while to complete.

    Hope this helps.
    Avi

    Asides from that, thank you very much for the help!
    I was about to make this sql file manually which would have taken some time to figure out, you saved me lots of time on this one. Appreciated!

  4. Ben Says:

    Hi Avi,

    Thanks for the comment. That is probably a better way of doing it anyway, cheers for the contribution 🙂

    Ben

Leave a Reply