As part of a migration activity i need to load a table in mysql with data from two different input files. As in few fields in the table are loaded from one input file and rest from another. Also the input files are not order wise,what i mean is field 1 in input file 1 might not be the first field in the table. So data needs to be loaded into proper fields in the table from input files. Hope my question is clear. If anyone can suggest any scripts or anything for this would be helpful Thanks in advance.
Create a table (t1) with columns matching the columns of one file. Load that file into it.
Create another table (t2) with columns matching the columns of the other file. Load that file into it.
Now create the main table (if it does not already exist). Load it with something like
INSERT INTO main (a,b,c,d,e) SELECT t1.x, t2.y, t1.z, t1.w, t2.v -- in whatever order is correct FROM t1 JOIN t2 ON ... -- explain how the two tables (files) need to be paired up.
If you can have your file in CSV format, then you will be able to order its columns by opening it with Excel viewer.
To covert your file into a CSV format you need to replace the current columns seperator by ; (use Notepad++ with advanced replacement) .
Once your files are ordered you can then copy the second file content into the other by using also Excel . Here you will have 1 file having same columns as your table and ordered as your table as well.
then what is left is to do :
LOAD DATA INFILE 'C:\file.CSV' INTO TABLE DB.Table FIELDS TERMINATED BY ';'
This is of course if you need to import only for 1 time . Otherwise there should be a work around depending on your case .