.: El Blog de Inwe :.

Este artículo se basa en una traducción (y transcripción) de un podcast de PHP Abstract, el cual explica de manera sencilla y a través de un ejemplo, como migrar o importar datos de una supuesta table de empleados a otra tabla existente de clientes, la cual viene con otro formato distinto.

Esta es la tabla de empleados (Employee), el origen de datos:

+-----------+-----------------------+------+-----+---------+----------------+

| Field     | Type                  | Null | Key | Default | Extra          |

+-----------+-----------------------+------+-----+---------+----------------+

| id        | mediumint(8) unsigned | NO   | PRI | NULL    | auto_increment |

| Name      | varchar(255)          | YES  |     | NULL    |                |

| Dept      | varchar(255)          | YES  |     | NULL    |                |

| Join_Date | varchar(50)           | YES  |     | NULL    |                |

| Email     | varchar(255)          | YES  |     | NULL    |                |

| Comments  | text                  | YES  |     | NULL    |                |

+-----------+-----------------------+------+-----+---------+----------------+

Mientras que el sistema al que se quiere migrar es la siguiente tabla, más una tabla auxiliar para facilitar las búsquedas de los departamentos:

+---------------+--------------+------+-----+---------+----------------+

| Field         | Type         | Null | Key | Default | Extra          |

+---------------+--------------+------+-----+---------+----------------+

| employee_id   | int(11)      | NO   | PRI | NULL    | auto_increment |

| first_name    | varchar(50)  | YES  |     | NULL    |                |

| last_name     | varchar(50)  | YES  |     | NULL    |                |

| department_id | int(11)      | YES  |     | NULL    |                |

| join_date     | datetime     | YES  |     | NULL    |                |

| email         | varchar(255) | YES  |     | NULL    |                |

| comments      | text         | YES  |     | NULL    |                |

+---------------+--------------+------+-----+---------+----------------+
+---------------+--------------+------+-----+---------+----------------+

| Field         | Type         | Null | Key | Default | Extra          |

+---------------+--------------+------+-----+---------+----------------+

| department_id | int(11)      | NO   | PRI | NULL    | auto_increment |

| name          | varchar(255) | YES  |     | NULL    |                |

+---------------+--------------+------+-----+---------+----------------+

De la tabla original, debemos hacer las siguientes transformaciones:

• El id se transforma en employee_id y el tipo de dato cambia ligeramente
• El Name se seàra en dos, first_name y last_name
• El campo Dept se necesita sacar fuera a una tabla de búsqueda y se crea el department_id en esta tabla
• El campo Join_date tiene que cambiar su tipo de dato
• Y los campos Email and Comments permanecen inmutables

Convertir el tipo de dato - Employee ID

Es bastante sencillo, simplemente se añade una nueva columna dentro de la tabla Employees y luego se cargan los datos

 alter table Employees add column employee_id int;

update Employees set employee_id = id;

Separando Strings - El campo Name

Por suerte, los datos que se almacenan en este campo tienen el nombre y el apellido separados por un espacio. Para separar el nombre y el apellido empezaremos con la siguiente sentencia select:

 select Name , substring_index(Name,' ',1) as first_name,
 substring_index(substring_index(Name,' ',2),' ',-1) as last_name
 from Employees limit 10;
+--------------------+------------+-------------+

| Name               | first_name | last_name   |

+--------------------+------------+-------------+

| Anika Lott         | Anika      | Lott        |

| Jenette Gibson     | Jenette    | Gibson      |

| Lacy Barton        | Lacy       | Barton      |

| Noelani Peck       | Noelani    | Peck        |

| Lacy Prince        | Lacy       | Prince      |

| Glenna Rowland     | Glenna     | Rowland     |

| Isabella Hall      | Isabella   | Hall        |

| Hadley Fitzpatrick | Hadley     | Fitzpatrick |

| Elaine Steele      | Elaine     | Steele      |

| Kathleen Davidson  | Kathleen   | Davidson    |

+--------------------+------------+-------------+

Ahora nos toca modificar la tabla con los nuevos campos e ir poniendo los datos en su correspondiente lugar:

 alter table Employees add column first_name varchar(50);

 alter table Employees add column last_name varchar(50);
update Employees set first_name = substring_index(Name,' ',1),
 last_name = substring_index(substring_index(Name,' ',2),' ',-1)

Del tipo string para las Fechas al tipo Fecha real

Tenemos una columna llamada join_date en nuestra tabla Employee, por lo que crearemos otra columna con otro nombre:

 alter table Employees add column join_date_formatted datetime;

De nuevo, una sentencia Select primero, y a continuación la conversión con una sentencia Update:

 select join_date , str_to_date(join_date, '%m-%d-%y') as machine_date
 from Employees limit 10;
update Employees set join_date_formatted = str_to_date(join_date, '%m-%d-%y');

MySQL por defecto pondrá la hora a la medianoche del día seleccionado.

Datos enumerados (o casi enumerados) pasan a la tabla de búsqueda

En un principio, vamos a echar un vistazo a los datos que contiene la tabla de origen:

 select distinct Dept from Employees;
+--------------------------+

| Dept                     |

+--------------------------+

| Human Resources          |

| Research and Development |

| Accounts                 |

| Finances                 |

| Quality Assurance        |

| Accounting               |

| Tech Support             |

| LegalPayroll             |

| QA                       |

| Sales and Marketing      |

| Customer Service         |

| Advertising              |

+--------------------------+

Observando los datos, hay unas cuantas cosas que tenemos que tener en cuenta, como por ejemplo “LegalPayroll” debería ser “Legal and Payroll”, y tenemos también “Accounts” y “Accounting” además de “Quality Assurance” y “QA”, que son de hecho el mismo departamente. Como el conjunto de datos es realmente pequeño, vamos a pasar los datos manualmente a la tabla de búsqueda, junto con un índice.

+---------------+--------------------------+

| department_id | name                     |

+---------------+--------------------------+

|             1 | Human Resources          |

|             2 | Research and Development |

|             3 | Accounting               |

|             4 | Finances                 |

|             5 | Quality Assurance        |

|             6 | Tech Support             |

|             7 | Legal and Payroll        |

|             8 | Sales and Marketing      |

|             9 | Customer Service         |

|            10 | Advertising              |

+---------------+--------------------------+

A continuación, creamos la nueva columna en la vieja tabla de Employees y vamos dandole las entradas “fáciles”.

    alter table Employees add column department_id int;
    update Employees set department_id = (select department.department_id
    from department where Employees.Dept = department.name);

Echando un vistazo a los registros que no se actualizaron, nos queda todavía cosas para corregir:

 select Name, Dept from Employees where department_id is null;
+--------------------+--------------+

| Name               | Dept         |

+--------------------+--------------+

| Lacy Barton        | Accounts     |

| Isabella Hall      | Accounts     |

| Kathleen Davidson  | Accounts     |

| Chiquita Rodriguez | LegalPayroll |

| Emi Ochoa          | LegalPayroll |

| Wanda Sullivan     | QA           |

| Yen Wilder         | QA           |

| Echo Hopkins       | LegalPayroll |

| Germane Stout      | Accounts     |

| Evangeline Oconnor | Accounts     |

| Hadley Ochoa       | QA           |

| Carly Riley        | LegalPayroll |

| Destiny Brock      | QA           |

| Mariam Baldwin     | Accounts     |

+--------------------+--------------+
update Employees set department_id = 3 where Dept = "Accounts";

update Employees set department_id = 5 where Dept = "QA";

update Employees set department_id = 7 where Dept = "LegalPayroll";

El paso final

Finalmente insertamos en la tabla employee con todos los nuevos datos preparados y formateados:

 insert into employee select id, first_name, last_name, department_id, join_date_formatted,
 Email, Comments from Employees;

Artículo original: DevZone

Podcast: PHP Abstract

Trackback a esta entrada | Comentarios por RSS