► Carga de ficheros no estructurados

Vamos a analizar paso por paso cómo cargar un fichero no estructurado.
Este tipo de ficheros suelen ser informes generados por otras herramientas y exportados a ficheros de texto. Suelen tener cabeceras, pies de página, líneas de texto e información relevante que no siempre está encolumnada en las mismas prosiciones.
Ejemplos: facturas, informes financieros, listados.... etc.

El truco para poderlo cargar es buscar palabras claves que se repitan y que nos den un indicio de dónde empieza y termina la información relevante.

En este caso vamos a cargar el fichero WEB http://www.linux-usb.org/usb.ids.
Este fichero, entre otras cosas contiene una lista de nombres de fabricantes y de dispositivos USB. Está más o menos formateado ya que las líneas comienzan siempre igual: por un carácter en blanco, tabuladores o '#' para los comentarios.

Contiene varias secciones, pero nos vamos a quedar sólo con la parte de los vendedores y sus dispositivos.
La estrategia a seguir es similar en todos estos ficheros y comienza cargando todo el fichero en una tabla. Luego buscaremos registro a registro la información relevante y la almacenaremos en una tabla de hechos.
En este caso cada línea del fichero puede tener un vendedor, un dispositivo o un interface. O sea, sólo uno a la vez. en cambio nuestra tabla de hechos tendrá un registro por cada combinación de vendedor, sus dispositivos y los interface de cada dispositivo.

 Id_Vendedor  |  Vendedor  |  Id_Dispositivo  |  Dispositivo  |  Id_Interface  |  Interface 

Veamos los pasos detalladamente.

1.- El modo de lectura por defecto de Qlik quita los espacios en blanco y los tabuladores a la izquierda del primer carácter de cada campo leído. Nos interesa leer la línea del fichero sin que se pierda nada, por tanto ponemos:

       SET VERBATIM = 1;

Esto nos va a garantizar cargar las líneas bien tabuladas.

2.- Cargamos cada línea del fichero en un registro de una tabla temporal. Luego borraremos esta tabla.

       DISPOSITIVOS_TMP:
        LOAD recno() AS "Num Linea",
                  @1        AS Linea
        FROM [http://www.linux-usb.org/usb.ids] (txt, utf8, no labels, delimiter is ';');

3.- Volvemos a establecer el modo de carga por defecto

      SET VERBATIM = 0;

4.- Ya tenemos cargada cada línea del fichero original en el campo "Línea" de la tabla "DISPOSITIVOS_TMP". Vamos a recorrer cada registro de la tabla para buscar y obtener la información relevante. El registro leído lo metemos en una variable para poderlo manipular.

       FOR i = 0 to NoOfRows('DISPOSITIVOS_TMP')-1
               vL.Linea=peek('Linea',$(i),'DISPOSITIVOS_TMP');

5.- Sabemos que el bloque de vendedores comienza por una línea que contiene el texto '# vendor  vendor_name' y que el siguiente bloque de datos comienza en una línea que contiene el texto '# C class  class_name'. Por tanto estableceremos un FLAG que indique si estamos en el bloque de vendedores o no.

       IF substringcount(vL.Linea,'# vendor  vendor_name') > 0 THEN // Comienzo Lista de vendedores
              vL.Flag_Fabricantes=1;
       END IF
       IF substringcount(vL.Linea,'# C class  class_name') > 0 THEN // Fin Lista de vendedores
              vL.Flag_Fabricantes=0;
       END IF

6.-  Llega el momento de verificar la información de cada línea. Si la línea pertenece al bloque de los Vendedores, la analizaremos, si no, directamente la desechamos.

       IF vL.Flag_Fabricantes=1 THEN  

7.- Para analizarla sabemos:

•  que las líneas de comentarios comienzan por '#'
•  que las líneas que comienzan por ' ' (espacio blanco) son líneas en blanco a desechar
•  que las líneas de los dispositivos comienzan por un caracter tabulador (CHR(9))
•  que las líneas de los interfaces comienzan por 2 caracteres tabulador (CHR(9)&CHR(9))

Dependiendo del tipo de línea detectada, guardaremos su información relevante en variables diferentes. 

     IF MATCH(LEFT(vL.Linea,1),'#',CHR(09),' ') = 0 THEN //Si no hay tabs ni #, es una línea de fabricante
              vL.Id_Fabricante = SubField(vL.Linea,' ',1); 
              vL.Nombre_Fabricante = TRIM(MID(vL.Linea, index(vL.Linea,' ')));
          
              //Cada vez que cambiamos de fabricante reseteamos los valores de los dispositivos y de los interfaces
              vL.Id_Interface='';
              vL.Nombre_Interface='';
              vL.Id_Dispositivo='';
              vL.Nombre_Dispositivo='';
          
              vL.Grabar_Linea=1;
       
             ELSEIF LEFT(vL.Linea,2) = CHR(09)&CHR(09) THEN // Es un Interface
                  vL.Id_Interface = Subfield(REPLACE(vL.Linea,'\t',''),' ',1);  
                  vL.Nombre_Interface = TRIM(MID(vL.Linea, index(vL.Linea,' ')));
             
                  vL.Grabar_Linea=1;
          
                 ELSEIF LEFT(vL.Linea,1) = CHR(09) THEN // Es un Dispositivo
                       vL.Id_Dispositivo = Subfield(REPLACE(vL.Linea,'\t',''),' ',1);  
                       vL.Nombre_Dispositivo = TRIM(MID(vL.Linea, index(vL.Linea,' ')));
                
                       //Cada vez que cambiamos de dispositivo reseteamos los valores de los interfaces
                       vL.Id_Interface='';
                       vL.Nombre_Interface='';
                
                       vL.Grabar_Linea=1;                
       ENDIF    

      

8.-  Si en cualquier IF anterior se ha marcado la línea como "grabable", procedemos a meterla en la tabla de hechos

      IF vL.Grabar_Linea=1; //Con este IF no duplicamos la última línea válida antes de una inválida que comience por #

           DISPOSITIVOS:

            LOAD

                 '$(vL.Id_Fabricante)'             AS Id_Vendor,

                 '$(vL.Nombre_Fabricante)'   AS Vendor,

                 '$(vL.Id_Dispositivo)'            AS Id_Device,

                 '$(vL.Nombre_Dispositivo)'  AS Device,

                 '$(vL.Id_Interface)'               AS Id_Interface,

                 '$(vL.Nombre_Interface)'     AS Interface              

             AUTOGENERATE 1;

    

          vL.Grabar_Linea=0;   //Reseteo del Flag que nos indica que hay que grabar la línea

       ENDIF  

9.- Repetimos el proceso para una nueva línea

      ENDIF //Cierre del IF que preguntaba si estamos en el bloque de Vendedores
    
     NEXT i  //Fin del bucle que lee línea a línea de la tabla temporal

10.- Por último borramos la tabla auxiliar

         DROP TABLE DISPOSITIVOS_TMP;


Con esto ya tendríamos transformado el fichero en una tabla lista para ser usada en nuestro Dashboard.

La filosofía para cualquier fichero de texto es siempre la misma: Cargar todo el fichero, fila a fila, en un campo de una tabla y luego leer cada registros de la tabla buscando información relevante como palabras clave que se repitan o patrones de palabras. Para ello usaremos con asiduidad casi todas las funciones de cadena que permitan trocear y limpiar texto como SUBFIELD, TRIM, LEFT, MID, RIGHT, INDEX, SUBSTRINGCOUNT...
Tendremos que crear variables de control para cargar la línea en curso y saber qué debemos hacer con ella. Usaremos también bucles FOR-NEXT para repetir trabajo sobre líneas, controles IF-THEN-ELSE para verificar si tenemos que tratarla o desecharla y por último un bloque donde grabaremos registro a registro los datos extraídos previamente en variables.