MySQL: Loading multiple files into a table

Question

I've been trying to load multiple files into a table, so that they would fit the same row.

I can insert them separately, but then the issue lies within the NULL values, and I plan to JOIN this table. If that happens, I get too many NULL values -- useless data.

LOAD DATA LOCAL INFILE 'malefirst.txt, femalefirst.txt, allfirst.txt, allfirst.txt' 
INTO TABLE fnames 
 (mal, fml, unk, cpx);

Another thing I have actually looked into was joining the files together with

paste -d " " (1.txt 2.txt ....)

However, it turned into a mess. If the first method does not work, then I can use the second, but I will need advice on it as well.

Answer 1

You could load the 4 files into 4 (temporary) tables (each with an autonumbered field) and then JOIN (using the ids) these files INTO your TABLE.

Answer 2

When I have to do things like this, I generally use AWK.

Here's how I would solve your problem if I had it:

cat file1 file2 filegroup* | awk '{print "insert into TABLE set FIELD1=\""$1"\";"}' | mysql -u USER -p DATABASE