Mysql – Loading a table with input from two different input files

MySQL

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.

Best Answer

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.

Related Solutions

Mysql – Migrating and normalizing data

Just keep the keys. You'll be able for example to locate data in old database much more easily.

But remember to update sequences associated with your keys after migration so they will have a value bigger than the biggest key in database.

Mysql – Dealing with empty strings while loading a table from a CSV

According to the documentation, you can use SET statements to transform the data on the way in.

 [SET col_name = expr,...]

The expr expression can include the column name, which will be interpreted as the data being read from the file and destined for that column... so, for example, at the end of your LOAD DATA INFILE statement you might use:

SET latitude = IF(latitude + 0 = 0,NULL,latitude),
    area_code = IF(area_code = '',NULL,area_code)

This example transforms 2 columns. If latitude + 0 is 0, latitude gets set to NULL, and otherwise it gets set to the value from the file as the data is inserted; if area_code contains an empty string, it gets set to NULL, otherwise to the data from the file. The more appropriate choice will depend on how MySQL handles casting the data, but I suspect either of these constructs would work in your situation.

You do not have to reference columns you don't intend to transform. They'll be inserted as-is.

Best Answer

Related Solutions

Mysql – Migrating and normalizing data

Mysql – Dealing with empty strings while loading a table from a CSV

Related Question