MySQL: Loading multiple files into a table

mysql sql load-data-infile
Michael picture Michael · Jul 1, 2011 · Viewed 15.1k times · Source

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

ypercubeᵀᴹ picture ypercubeᵀᴹ · Jul 2, 2011

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.

Related questions

LOAD DATA LOCAL, How do I skip the first line?

I'm trying to Load a CSV file into my MySQL database, But I would like to skip the first line. I fact It contains the name of my columns and no interesting data. Here is the query I'm using: LOAD …

Read More
PHP - Import CSV file to mysql database Using LOAD DATA INFILE

I have a .csv file data like that Date,Name,Call Type,Number,Duration,Address,PostalCode,City,State,Country,Latitude,Longitude "Sep-18-2013 01:53:45 PM","Unknown","outgoing call",'123456',"0 Secs","null","null","null","null","null",0.0,0.0,,, "Sep-18-2013 01:54:14 PM","Unknown","outgoing …

Read More
How to change string date to MySQL date format at time of import of CSV using MySQL's LOAD DATA LOCAL INFILE

I'm using MySQL's LOAD DATA LOCAL INFILE SQL statement to load data from a CSV file into an existing database table. Here is an example SQL statement: LOAD DATA LOCAL INFILE 'file.csv' INTO TABLE my_table FIELDS TERMINATED BY …

Read More