Populate MySQL table from Text file

Question

This should be an easy one. I need to populate a table from a text file. Basically, I'd like to do this in a linux shell script. Thanks!!

Example:

• MySQL Table

Item color shape size

• Textfile

car blue round small

carrot red square big

apple green round medium

Answer 1

You could run a query like this:

LOAD DATA INFILE 'data.txt' INTO TABLE yourTable
 FIELDS TERMINATED BY ' '
 LINES TERMINATED BY '\n'

And then all you need is to write a tiny shell script, executing the query.

Edit:

A complete script could look something like this:

#!/bin/bash
mysql databaseName<<EOFMYSQL
 LOAD DATA INFILE 'data.txt' INTO TABLE yourTable
  FIELDS TERMINATED BY ' '
  LINES TERMINATED BY '\n';
EOFMYSQL

The script could be executed like this:

chmod +x script.sh
./script.sh

You probably run into user issues, since the shell does not handle mysql directly. Try looking into how you can execute mysql without logging in. This should be part of just about any mysql backup script :-)