load data infile using query: possible??

[hessodreamy]

I am well pleased with myself for having managed to load data from a text file into mysql. However have now come up against another problem.

One of the columns in the data file refers to a field in a table, but the table into which it is being inserted needs to extract the primary key relating to said data. Make sense?

The data in file has column 'productCode'.
However the table im inserting into has column 'productID', which is related to productCode in another table.

So what I'm wanting to do is insert from file (column1, column2, column3, (SELECT productID FROM products WHERE productID = productCode), column5....

Is this at all possible?

 

[TonyGroves]

No. You can't combine a LOAD DATA INFILE with a SELECT query.

However, what you could do would be to load your text file into a temporary table, and then do a SELECT query on your temporary table joined with other tables, and insert the results into the destination table.

 

[hessodreamy]

Thats what I did in the end. Nice to know that I did it right!!
Cheers for help.

 

[pookie62]

Hi hessodreamy,
Could you please share your update code ?
I'm looking for a couple of days now (you know what it's like..) but can't find what I need.
Reading your post looks exactly the thing I need..
I also need to import an uploaded file into MySQL.
So please share the complete code..

Thanks very much !
Hans

 

[hessodreamy]

Hi there Hans.
It's been a couple of months since I did this, so I don't have the code anymore, but let's see if I can help anyway.

First you insert the data from the file into a table you create:

CREATE TABLE TEMP_TABLE(`column1` tinyint, `column2` varchar(50));
LOAD DATA INFILE "C:\\test.txt" INTO TABLE TEMP_TABLE FIELDS TERMINATED BY ',' ENCLOSED BY '/';

You'll need to make sure that the data in the file is the right type to go in the table ie strings should be in quotes etc. Also note that you'll need to use double backslashes instead of backslashes in your file path, like I did.

Okay. Now you can insert stuff into another table by doing a query on the table you just created. The basic syntax is like this:

CREATE TABLE TEMP_TABLE2(`column1` tinyint, `column2` varchar(50));
insert into temp_table2 select * from temp_table;

But if you're being clever and you need to do a join, then it would be something like this:

CREATE TABLE TEMP_TABLE(`column1` tinyint, `column2` varchar(50));
insert into temp_table2 select t1.column1, t3.column2 from temp_table t1, temp_table3 t3 WHERE t1.column1 = t3.column1;

I hope this helps. Let me know if it doesn't make sense!

 

[pookie62]

Hi hessodreamy,
Thanks for your reply, I will start with the examples you gave and see where I get..
You'll probably see me again..
Cheers !