Enorable
Base De Datos

12/11/2019 | vistas:

Oracle: leer los datos de un archivo e insertarlos en una tabla con PL/SQL

Ejemplo de cómo leer un archivo con extensión .csv o .txt con registros delimitados por coma, dos puntos o punto y coma, para luego insertarlos en una tabla de la base de datos de Oracle haciendo uso de PL/SQL.

En el mercado actual de la tecnología nos encontramos muchas veces con sistemas informáticos, aplicaciones y herramientas administrativas que pueden ofrecer la opción de poder exportar ciertos datos para su posterior uso de parte de los usuarios. Pero en el ámbito del desarrollo, el objetivo principal de trabajar con este tipo de opciones se visualiza cuando se requiere realizar la migración de una data, de un sistema a otro e incluso recuperar alguna información que se haya perdido anteriormente, o cuando la información se ha vuelto inconsiste o corrupta y amerita una restauración desde algún punto del sistema.

Cuando trabajamos con la plataforma de Oracle este proceso también es posible a través de diferentes métodos. En esta oportunidad voy a mostrar un recurso que ya es muy conocido por muchos para lograr leer datos que se encuentran escritos en un archivo externo con una extensión .csv o .txt, estando los campos delimitados por un carácter especial, como por ejemplo: dos puntos (:) o punto y coma (;).

Leer y guardar los datos desde un un archivo externo con PL/SQL

Existe una manera sencilla de leer un archivo externo mediante el uso del lenguaje interno de Oracle (PL/SQL) y la utilidad de UTL_FILE, insertar sus valores en una tabla de la base de datos y posteriormente hacer uso de la información almacenada.

Dicho proceso se puede realizar directamente desde un bloque anónimo en PL/SQL, pero aquí dejaré un procedimiento para que su uso sea regular, aunque primero que nada, debemos crear una tabla «temporal» para alcanzar el objetivo que nos hemos trazado.

Nota: Si este recurso te ha servido de ayuda recuerda que puedes apoyar mi trabajo realizando una contribución a través de mi perfil de usuario de la página y/o puedes enviar tus comentarios a través del sistema de contacto.

Crear un archivo .csv o .txt

Para este ejemplo se debe crear un archivo con extensión .csv o .txt delimitado por el caracter especial punto y coma (;), en una ruta específica del servidor donde se encuentra alojada la base de datos y debe contener las siguientes líneas de datos:

00000;LINDA;HAMIL;35;F;LA PLATA;BUENOS AIRES;ARGENTINA
12345;JOSE;ROJAS;25;M;SEATTLE;WASHINGTON;ESTADOS UNIDOS
98765;MARIA;PEREZ;50;F;CUA;MIRANDA;VENEZUELA
54321;RAUL;GONZALES;20;M;SANTIAGO;METROPOLITANA DE SANTIAGO;CHILE
56789;LUISA;VIVAS;40;F;MADRID;MADRID;ESPAÑA

Crear una tabla temporal para guardar los datos extraídos del archivo externo

Creamos una tabla nombrada «MI_TABLA_TEMP» para almacenar la información que se usará luego. La misma acepta solo ocho (8) campos, tal y como se describe a continuación:

CREATE TABLE MI_TABLA_TEMP (MYID NUMBER NOT NULL,
NAMES VARCHAR2 (100) NULL,
OLD_NAMES VARCHAR2 (100) NULL,
AGE NUMBER NULL,
SEX VARCHAR2(10) NULL,
CITY VARCHAR2 (100),
STATE VARCHAR2 (100),
COUNTRY VARCHAR2 (100));

Table created.

Crear un procedimiento para extraer los datos contenidos en un archivo .csv

Vamos a crear nuestro procedimiento nombrado «P_SPLIT» para leer el archivo con extensión .csv, usar la información que contiene para insertarlos ordenadamente en una tabla de la base de datos, y así poder manipularla a discreción.

NOTA: Es obligatorio conceder permisos especiales al usuario que corre el script para poder tener acceso a las opciones de lectura y escritura del archivo que se encuentra en la ruta descrita. En caso de que el archivo se encuentre en una ruta sin permisos deberá crear un «enlace simbólico» a través de comandos SQL de la siguiente manera:

SQL> CREATE DIRECTORY log_dir AS '/home/usr/scripts';
SQL> GRANT READ ON DIRECTORY log_dir TO DBA;
SQL> GRANT WRITE ON DIRECTORY log_dir TO DBA;

SQL> CREATE DIRECTORY USER_DIR AS '/home/usr/scripts/';
SQL> GRANT READ ON DIRECTORY USER_DIR TO PUBLIC;
SQL> GRANT WRITE ON DIRECTORY USER_DIR TO PUBLIC;

Una vez que tengamos resuelta esta situación, entonces podemos correr nuestro procedimiento.

CREATE OR REPLACE PROCEDURE P_SPLIT (p_ruta IN VARCHAR2,
p_filename IN VARCHAR2,
p_deli IN VARCHAR2 := ':')
IS
/* ************************************************************************************
* Procedimiento: p_split
* Descripción: Permite separar las columnas de un archivo .csv o .txt por un delimitador
* Parámetros: p_filename Nombre del archivo
* p_ruta Ruta en la base de datos para UTL_FILE
* p_deli Delimitador dentro del archivo
* *********************************************************************************
*/
v_archivo UTL_FILE.FILE_TYPE; -- variable que contendrá el archivo
v_linea VARCHAR2 (32767);
v_lineab VARCHAR2 (32767);
v_idx pls_integer;
v_nrocampo NUMBER := 0;
v_salida VARCHAR2 (255);
v_reg NUMBER:= 0;
BEGIN
-- Abrir el archivo «myfile.csv» ubicado en «/home/usr/scripts»
v_archivo := UTL_FILE.FOPEN (p_ruta,p_filename,'R');
LOOP
v_salida := ''; -- inicializar la variable v_salida
BEGIN
UTL_FILE.GET_LINE (v_archivo, v_linea); -- leer las líneas del v_archivo y capturar sus datos en memoria
v_lineab := v_linea;
v_reg := v_reg + 1; -- conteo de filas
LOOP
v_nrocampo := v_nrocampo + 1; -- conteo de campos
v_idx := instr (v_lineab,p_deli); -- la posición del caracter delimitador que define el campo
-- Leer y organizar los datos contenidos en el archivo
IF v_idx > 0 THEN
v_linea := (SUBSTR (v_lineab,1,v_idx-1));
v_lineab := SUBSTR (v_lineab,v_idx + LENGTH (p_deli));
ELSE
v_linea := (SUBSTR (v_lineab,1,LENGTH (v_lineab)));
END IF;
v_salida := v_salida || '''' || v_linea || ''','; -- armar los datos leídos para crear el Insert
EXIT WHEN v_idx < 0 OR v_idx = 0;
END LOOP;
-- Armar DML para insertarlos en la tabla temporal MI_TABLA_TEMP
v_salida := 'INSERT INTO MI_TABLA_TEMP VALUES (' || SUBSTR (v_salida,1,LENGTH (v_salida)-1) || ');';
DBMS_OUTPUT.PUT_LINE (v_salida); -- se imprime la salida final
EXECUTE IMMEDIATE v_salida; -- ejecuta la salida para insertar los datos en la tabla
EXCEPTION
WHEN OTHERS
THEN
EXIT;
END;
END LOOP;
COMMIT;
UTL_FILE.FCLOSE (v_archivo); -- cerrar el archivo abierto
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE ('ERROR: Se ha presentado un problema en el proceso.');
END;
/

Procedure created.

Ejecutar el procedimiento P_SPLIT

Para probar el procedimiento creado creamos un bloque anónimo y pasamos los parámetros requeridos de la siguiente manera:

BEGIN
P_SPLIT('/home/usr/scripts','archivo.csv',';');
END;

Statement processed.

Siendo el resultado impreso como el que se muestra a continuación:

INSERT INTO MI_TABLA_TEMP VALUES ('00000','LINDA','HAMIL','35','F','LA PLATA','BUENOS AIRES','ARGENTINA');
INSERT INTO MI_TABLA_TEMP VALUES ('12345','JOSE','ROJAS','25','M','SEATTLE','WASHINGHTON','ESTADOS UNIDOS');
INSERT INTO MI_TABLA_TEMP VALUES ('98765','MARIA','PEREZ','50','F','CUA','MIRANDA','VENEZUELA');
INSERT INTO MI_TABLA_TEMP VALUES ('54321','RAUL','GONZALES','20','M','SANTIAGO','METROPOLITANA DE SANTIAGO','CHILE');
INSERT INTO MI_TABLA_TEMP VALUES ('56789','LUISA','VIVAS','40','F','MADRID','MADRID','ESPAÑA');

Si no ha ocurrido un error en la compilación del procedimiento y al ejecutar el proceso todo resulta como se espera, es posible verificar que los datos estén contenidos en la tabla «MI_TABLA_TEMP», siendo los mismos que aparecieron en la salida anterior.

Consulta Lo+ Top de Base De Datos