LOAD DATA INFILE into Single Field on MySQL

Question

I am very much a noob at MySQL.. I need to populate one field with a .txt file.

My script currently reads:

LOAD DATA INFILE '/attachments/file.txt' into table files (data);

However, when I run the query, MySQL shows that I have updated not just one record, but multiple records -- each line of my .txt file going to a different field.

Is it possible to require my script to upload the entire .txt file into a single field?

Thanks!

So, does each row of your text file contain a single value, that you want to put into a single column in a table, one row in the text file = one row in the table? Can you give an example of the text file? — Duncan Lock, Jun 14 '13 at 16:57
The text file is on the large side: around 200 lines of text. Is it even possible to load all of this data into a single field and still maintain the structure of the file? — David Wright, Jun 14 '13 at 17:03
@bill: "upload the entire .txt file into a single field". Explain to me how load_file() DOESN'T do that, then feel free to criticize. — Marc B, Jun 14 '13 at 17:04

Duncan Lock · Accepted Answer · 2013-06-15T09:05:40.793

There are a couple of ways of dong this, depending on the details of your scenario:

LOAD DATA INFILE

You probably want something like this:

LOAD DATA LOCAL INFILE '/path/to/file/data_file.csv'
    IGNORE
    INTO TABLE `databasename`.`tablename`
    CHARACTER SET utf8
    FIELDS
        TERMINATED BY '\n'
        OPTIONALLY ENCLOSED BY '"'
    IGNORE 1 LINES
    (column1)
SHOW WARNINGS;

This will import from /path/to/file/data_file.csv into databasename.tablename, with each complete line in the text file being imported into a new row in the table, with all the data from that line being put into the column called column1. More details here.

LOAD_FILE

Or you could use the LOAD_FILE function, like this:

UPDATE table
  SET column1=LOAD_FILE('/path/to/file/data_file.csv')
  WHERE id=1;

This will import the contents of the file /path/to/file/data_file.csv and store it in column1 of the row where id=1. More details here. This is mostly intended for loading binary files into BLOB fields, but you can use it to suck a whole text file into a single column in a single row too, if that's what you want.

Using a TEXT Column

For loading large text files, you should use a column of type TEXT - it's can store very large amounts of text with no problems - see here for more details.

LOAD DATA INFILE into Single Field on MySQL

1 Answers1

LOAD DATA INFILE

LOAD_FILE

Using a TEXT Column