Is there a way to insert custom data while loading data from a file in MySQL?

Question

I am using the following statement to load data from a file into a table:

LOAD DATA LOCAL INFILE '/home/100000462733296__Stats" 
INTO TABLE datapoints 
FIELDS TERMINATED BY '|' 
LINES TERMINATED BY '\n' 
(uid1, uid2, status);

Now, if I want to enter a custom value into uid1, say 328383 without actually asking it to read it from a file, how would I do that? There are about 10 files and uid1 is the identifier for each of these files. I am looking for something like this:

LOAD DATA LOCAL INFILE '/home/100000462733296__Stats" 
INTO TABLE datapoints 
FIELDS TERMINATED BY '|' 
LINES TERMINATED BY '\n' 
(uid1="328383", uid2, status);

Any suggestions?

Answer 1

2 quick thought (one might be applicable :)):

1. change the value of uid1 in the file to 328383 in every line.
2. temporarily change the uid1 column in the table to be non-mandatory, load the contents of the file, then run a query that sets the value to 328383 in every row. Finally, reset the column to mandatory.

Answer 2

You could use a python interactive shell instead of MySQL shell to interactvely provide values for MySQL tables.

Install the python inerpreter from python.org (only needed if you are under windows, otherwise you have it already), and the mysql connector from http://sourceforge.net/projects/mysql-python/files/ (ah, I see you are on Lunux/Unix --just install teh mysqldb package then)

After that, you type these three lines in the python shell:

import MySQLdb
connection = MySQLdb.connect(" <hostname>", "< user>", "<pwd>", [ "<port>"] )
cursor = connection.cursor

Adter that you can use the cursor.execute method to issue SQL statements, but retaining th full flexibility of python to change your data. For example, for this specific query: myfile = open("/home/100000462733296__Stats")

for line in file:
   uid1, uid2, status = line.split("|")
   status = status.strip()
   cursor.execute("""INSERT INTO datapoints SET uid1="328383", uid2=%s, status=%s""" %(uid2, status) )

voilá ! (maybe with a try: clause around the the "line.split " line to avoid an exception on the last line)

If you don't already, you may learn Python in under one hour with the tutorial at python.org -- it is really worth it, even if the only things you do at computers is to import data into databases.

Answer 3

The SET clause can be used to supply values not derived from the input file:

LOAD DATA LOCAL INFILE '/home/100000462733296__Stats" 
INTO TABLE datapoints 
FIELDS TERMINATED BY '|' 
LINES TERMINATED BY '\n' 
(uid1, uid2, status)
SET uid1 = '328383';

It's not clear what the data type of uid1 is, but being that you enclosed the value in double quotes I assumed it's a string related data type - remove the single quotes if the data type is numeric.

There's more to read on what the SET functionality supports in the LOAD FILE documentation - it's a little more than 1/2 way down the page.