Lectura y escritura de ficheros de texto con VB para Excel. De KML a GPX V

Sigo con el ejemplo de la entrada anterior, convertir un fichero KML en un fichero GPX. Para convertir un tipo de fichero en otro tipo de fichero necesito conocer la estructura de los datos en ambos tipos de ficheros. Mas o menos ambas están explicadas en la entrada anterior.

Esta vez lo voy a hacer por programación, no mediante el camino, un tanto barroco, de preparar el fichero de texto para colocar los distintos campos de datos a mi conveniencia y mediante formulas excel, separar, concatenar, para volver a editar un fichero de texto y ¡por fin! obtener el resultado final.

Antes de empezar la a trabajar es conveniente conocer algunas particularidades del código ASCII en ficheros de texto, que es el código que se utiliza para representar los distintos caracteres. El código ASCII se puede dividir en dos partes, los llamados caracteres de control y los caracteres imprimibles. De los caracteres de control, desde mi punto de vista, solo tienen interés en un fichero de texto, la tabulación (ASCII 9), el salto de línea (LF, ASCII 10) y el retorno de carro (CR, ASCII 13). El resto de los caracteres de control , algunos, probablemente se sigan utilizando en los teclados y otros  ya no tengan ninguna utilidad, se utilizaban para máquinas hoy en día en prácticamente desuso, como los teletipos.

Dependiendo de donde venga un fichero de texto puede que el fin de línea lo haga con un CR+LF o solamente con un LF. El doble fin de línea, desde mi punto de vista, viene de las ya olvidadas máquinas de escibir, anteriores a cualquier ordenador y a otro tipo de máquinas como los telex o teletipos. Para los que conocimos las máquinas de escribir tiene sentido, era lo que se hacía para pasar a la siguiente línea.

Algunas funciones de VBasic para el manejo de textos:

• Dos textos se concatenan con un &. Es para textos el equivalente al + para los números.
• Asc(Carac). Devuelve el número ASCII del carácter.
• Right(Cadena,n). Devuelve los últimos n caracteres de la derecha de una cadena alfanumérica.
• Left(Cadena,n). Devuelve los primeros n caracteres de la izquierda de una cadena alfanumérica.
• Lin = Mid(Lin, Pos1 + 6,n). Devuelve, a partir de el segundo parámetro (Pos1+6), n caracteres. Este tercer parámetro es opcional.
• InStr(LCase(Lin), "<when>"). Devuelve la posición en la que se encuentra una cadena dentro de otra. 
• LCase(Cad). Devuelve la cadena en minúsculas.
• UCase(cad). Devuelve la cadena en mayúsculas.
• Len(Cad). Longitud o número de caracteres de una cadena alfanumérica.
• Split(Cad,Car). Divide y pasa la cadena alfanumérica a una matriz. El carácter "car" es el separador.
• Chr(n). Devuelve el carácter correspondiente al número n (en decimal).
• Replace("ABCD", "D", "X"). Cambia un conjunto de caracteres por otros. En este caso cambia D por X.
• Format(9, "0.00"). Da formato a un texto, en este caso presenta 9 como 9,00. Da muchas posibilidades.
• Algunas de las funciones anteriores tienen otros parámetros adicionales.

En principio la conversión es relativamente sencilla, incluso es mucho mas sencilla que hacerla sin programación. Sin programación, lo reconozco es una cosa muy compleja.

Proceso de lectura:

• Cierro, el fichero #n con close #n. En este caso n=1.
• Leo, en la primera hoja del libro excel, el nombre y el directorio de trabajo.
• Abro el fichero origen con Open DirT & Nom For Input As #1
• Leo, secuencialmente, hasta el final y carácter a carácter, el fichero de texto origen de los datos.
• En este caso no considero el carácter de ascii 13. No lo trato.
• Concateno el carácter leído con los caracteres leídos con anterioridad. 
• Busco el/los delimitadores, o etiquetas que me marcan los datos. Para la fecha y hora de un punto busco las etiquetas <when> y </when> con  Pos1 = InStr(LCase(Lin), "<when>")  y Pos2 = InStr(LCase(Lin), "</when>"), trabajando en minúsculas. Extraigo los datos con Lin = Left(Lin, Pos2 - 1) y Lin = Mid(Lin, Pos1 + 6). 
• Separo los datos. Una vez encontrados, mantengo fecha y hora pero separo las coordenadas y la altura con Coor = Split(Lin, ",").
• Concateno los datos válidos con las etiquetas correspondientes a los ficheros GPX.
• Un punto está definido por unas coordenadas, su altura y la fecha y hora en que fue tomado, aunque en este fichero kml aparece primero la fecha y hora. Al encontrar una linea de datos pongo a cero (Lin=""). Al encontrar todos los datos de un punto los escribo en el fichero de salida y en la hoja excel Aux e incremento línea para la siguiente vez que escriba en la hoja excel. Hay otras maneras, quizás mas correctas, de hacerlo. Cosas que he heredado de mi mismo. Empiezas a hacerlo de una manera y continuas haciendolo así, sin plantearmelo. 
• Al encontrar un carácter 10 (LF) borro Lin (Lin=""). En otros casos, lo lo mejor, sería conveniente convertirlo en un cáracter nulo.

Libro excel:

• Tiene dos hojas, en la primera ("Config"), sitúo el directorio de trabajo y el nombre del fichero de texto a leer.
• El fichero de salida hereda tanto directorio de trabajo como nombre del fichero de entrada.
• Con Alt+F11 se puede entrar a los módulos VBasic. Localizo la macro LeeTexto2 y la ejecuto con F5.
• La macro LeeTexto2 convierte el fichero KML a fichero GPX.

Escritura en fichero de texto de salida:

• Cierro, el fichero #n con close #n.
• Abro el fichero de salida. Nombre, incluido directorio, y  tipo de E/S. En este caso Open DirT & NomRut For Output As #2. A partir de este momento podemos escribir en el fichero de salida con Print #2, Texto
• Escribo la cabecera propia de los ficheros GPX.
• Incorporo los datos encontrados durante el proceso de lectura del fichero origen (#1)
• Escribo la cola propia de los ficheros GPX.
• Cierro fichero.

Código de LeeTexto2:

Sub LeeTexto2()

Dim Carac, Lin, Pos1, Pos2, DirT, Nom, N, Longitud, Coor, LinT, NomRut, H

NomRut = "prueba"

Nom = Sheets(1).Range("b2")

DirT = Sheets("Config").Range("a2")

Set H = Sheets("aux")

H.UsedRange.Clear

Close #1

Close #2

N = 1

NomRut = Left(Nom, Len(Nom) - 4) & "xx.GPX"

'******************** Fichero gpx de salida ********************************

Open DirT & NomRut For Output As #2

'*************************************************Cabecera fichero GPX ***************

Print #2, "<?xml version='1.0' encoding='UTF-8' standalone='no' ?>"

Print #2, "<gpx xmlns='http://www.topografix.com/GPX/1/1' creator='MapSource 6.12.4' version='1.1' xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance' xsi:schemaLocation='http://www.topografix.com/GPX/1/1 http://www.topografix.com/GPX/1/1/gpx.xsd'>"

Print #2, "<metadata>"

Print #2, "<link href='http://www.garmin.com'>"

Print #2, "<text>Convertido por programa</text>"

Print #2, " </link>"

Print #2, "</metadata>"

Print #2, "<trk>"

Print #2, "<name>" & NomRut & "</name>"

Print #2, "<extensions>"

Print #2, "<gpxx:TrackExtension xmlns:gpxx='http://www.garmin.com/xmlschemas/GpxExtensions/v3' xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance' xsi:schemaLocation='http://www.garmin.com/xmlschemas/GpxExtensions/v3 http://www.garmin.com/xmlschemas/GpxExtensions/v3/GpxExtensionsv3.xsd'>"

Print #2, "<gpxx:DisplayColor>Red</gpxx:DisplayColor>"

Print #2, "</gpxx:TrackExtension>"

Print #2, "</extensions>"

Print #2, "<trkseg>"

'*************************************************Cabecera fichero GPX ***************

Open DirT & Nom For Input As #1

Do While Not EOF(1)

Carac = Input(1, #1)

Lin = Lin & Carac

Pos1 = InStr(LCase(Lin), "<when>")

Pos2 = InStr(LCase(Lin), "</when>")

If Pos1 > 0 And Pos2 > 0 Then

Lin = Left(Lin, Pos2 - 1)

Lin = Mid(Lin, Pos1 + 6)

LinT = Lin

Lin = ""

End If

Pos1 = InStr(LCase(Lin), "<coordinates>")

Pos2 = InStr(LCase(Lin), "</coordinates>")

Longitud = Len("<coordinates>")

If Pos1 > 0 And Pos2 > 0 Then

Lin = Left(Lin, Pos2 - 1)

Lin = Mid(Lin, Pos1 + Longitud)

Coor = Split(Lin, ",")

'Sheets("Aux").Range("b" & N).Value = Coor(0)

'Sheets("Aux").Range("c" & N).Value = Coor(1)

'Sheets("Aux").Range("d" & N).Value = Coor(2)

'Sheets("Aux").Range("b" & N & ":d" & N) = Split(Lin, ",")

Lin = ""

Print #2, "<trkpt lat='" & Coor(1) & "' lon='" & Coor(0) & "'>"

Print #2, "<ele>" & Coor(2) & "</ele>"

Print #2, "<time>" & LinT & "</time>"

Print #2, "</trkpt>"

H.Range("a" & N).Value = "<trkpt lat='" & Coor(1) & "' lon='" & Coor(0) & "'>" & _

"<ele>" & Coor(2) & "</ele>" & "<time>" & LinT & "</time>"

N = N + 1

End If

If Asc(Carac) = 10 Then

'H.Range("a" & N).Value = Lin

Lin = ""

'N = N + 1

End If

Loop

Close #1

'*************************************************Cierre ruta fichero GPX ***************

Print #2, "</trkseg>"

Print #2, "</trk>"

Print #2, "</gpx>"

'*************************************************Cierre fichero salida***************

Close #2

End Sub

Convertir fichero Kml en fichero GPX con Excel IV.

Cuarta entrega de como convertir un fichero tipo KML (de Google herth) a fichero tipo GPX (Garmin). KML presenta un código demasiado abierto para hablar de certezas en todos sus formatos. Lo único que siempre he encontrado en los ficheros KML son, hacia el final del texto del fichero, las coordenadas de todos los puntos, sin fecha, encuadradas entre las etiquetas <coordinates> y </coordinates>. A su vez, en algunos ficheros estas coordenadas pueden aparecer de una en una en medio del texto del fichero KML. 

En este caso un compañero de correrías montañeras, me mando un fichero KML en el que las coordenadas aparecen, además de al final del fichero, de una en una en medio del fichero, precedidas de la fecha y hora del momento en el que se tomó el punto.

En este caso caso, ya que las tengo, me interesa incorporar la fecha/hora del punto al fichero GPX.

Primero, como siempre, por precaución y por facilitar el trabajo, copié el fichero KML en otro con extensión TXT. A partir de este momento se puede trabajar con cualquier editor de textos, incluido el bloc de notas (notepad) de Windows.

Lo primero es identificar los distintos campos que queremos incorporar. En nuestro caso encontramos:

     <TimeStamp><when>2017-03-12T09:34:18Z</when></TimeStamp>

            <styleUrl>#track</styleUrl>

            <Point>

              <coordinates>-3.642419,41.230943,965.91</coordinates>

            </Point>

Como vemos, los datos que nos interesan, están situados en distintas líneas. Pasar a una solo línea aquellos parámetros que en principio vienen en varías  líneas se puede hacer de varias maneras,  aunque alguna de ellas funciona en windows XP pero no funciona en windows 7.

En este caso decido evitar esos saltos de línea con código html. Edito la copia del fichero kml y sustituyo aquellos caracteres o conjunto de caracteres que posteriormente puedan confundirse por mi código por espacios o por nulos:

• <br> por un nulo. Normalmente no debe haber ninguno, pero por si acaso, los elimino.
• ; por un nulo.

Preparo el fichero para llevar los datos a excel.

• Introduzco un salto de línea delante de <when>. Reemplazo <when> por <br><when>.
• Reemplazo <when> por ; y </when> por ;. Esto deja cada fecha separada por puntos y comas. 
• Reemplazo <coordinates> por ; y </coordinates> por ;. Esto deja cada coordenada separada por puntos y comas. 
• Salvamos como htm.

Llevo los datos a excel:

• Hacemos doble click sobre el fichero htm. El fichero se abrirá con el nuestro navegador de internet (IExplorer o google chrome o ...). Como es un fichero extenso tarda un poco.
• Copiamos el texto que aparece en el navegador.
• Pegamos ese texo en una hoja excel.
• Eliminamos el contenido de la primera línea.
• Ya en Excel, con texto en columnas seleccionamos, de todo lo que hemos importado, los campos útiles, que son fecha y hora y coordenadas con altura.
• Seleccionamos texto en columnas, delimitados, y como delimitador,punto y coma.
• La primera columna la saltamos, la segunda (fecha y hora) la importamos como texto, la tercera no la importamos, la cuarta están las coordenadas y la altitud la importamos como texto y el resto no las importamos.
• Es importante importarlas como TEXTO.
• Quedan, por tanto, dos columnas, fecha y coordenadas. 
• Repetimos el proceso texto en columnas para la columna B.
• En este caso el carácter de delimitación es  coma, en vez de punto y coma, e importamos los tres campos campos como texto.
• En la columna e, celda e1, colocamos la siguiente fórmula:

="<trkpt lat=" &CARACTER(34) & C2  &CARACTER(34) & "  lon=" &  CARACTER(34)& B2  &CARACTER(34)& "><ele>" & D2 & "</ele><time>" & A2 & "</time></trkpt>"

Arrastramos la fórmula hasta el final de la columna. Editamos Modelo.gpx, copiamos la columna e y pegamos los datos en Modelo.gpx entre las líneas <trkseg> y </trkseg>. Guardamos como y le damos el nombre que queramos darle.

'**********************************************************************************
No solo de windows vive el hombre. Como trabajar los ficheros KML directamente en linux:

• Abrimos un terminal.
• Con grep 'when' x.kml|cut -d'>' -f3|cut -d'<' -f1|cat -n>when.txt pasamos las fechas al fichero when.txt
• Con grep 'coordinates' x.kml|cut -d'>' -f2|cut -d'<' -f1|cat -n>coor.txt pasamos las coordenadas al fichero coor.txt.
• Con join when.txt coor.txt>unidos.txt
• Pasamos a una hoja de cálculo de open office o de libre office y procedemos como lo explicado anteriormente para windows.

Encontrar todas las permutaciones de seis elementos.

Esta vez se trata de encontrar las 720 posibles permutaciones de 6 elementos.

Variables con nombre utilizadas:

• Elementos=IZQUIERDA(Permutaciones!$A$2;6)
• Fact2, Fact3, Fact4, Fact5. Son el factorial de de 2, 3, 4, y 5, utilizando la función Fact(n).

Funciones utilizadas:

• Extrae(texto,pos. inicial, n. caracteres). =EXTRAE(Elementos;G2+1;1)
• Sustituir(texto;Carácter a sustituir;carácter sustituto). =SUSTITUIR(Elementos;M2;"")
• Residuo(Dividendo;divisor)
• =ENTERO($A2/Fact5)
• =CONTAR.SI(W:W;W2)

Sabemos que el número de permutaciones de n elementos es n! (factorial de n). En este caso, permutaciones de 6 elementos 6*5*4*4*2*1=720. Para encontrar todas las posibles permutaciones de 6 elementos tengo que encontrar un mecanismo que me permita multiplicar matrices. Esas 720 permutaciones son el resultado de multiplicar la matriz con las 120 permutaciones de 5 elementos por seis elementos. Las 120 permutaciones de 5 elementos es el resultado de multiplicar, matricialmente, las 24 permutaciones de 4 elementos por 5 elementos, etc.
En la hoja Aux, columna A, coloco los 720 valores de 6!, con un pequeño truco, empiezo desde cero. En la columna B encontramos, mediante la función RESIDUO, lo que sería el número de permutación de los 120 posibles valores de una permutación de 5 elementos. Así Sucesivamente hasta la columna E.
En la columna G, mediante la función ENTERO(), se calcula el ordinal del elemento que queda mas a la izquierda. Sucesivamente, de las columnas H a la L encontramos los ordinales de 5 elementos, de 4, etc.
En la columna M encontramos el elemento de mas a la izquierda (=EXTRAE(Elementos;G2+1;1)). En N quitamos del conjunto de elementos ese primer elemento encontrado SUSTITUIR(Elementos;M2;""). En el resto de elementos, una vez desaparecido el primero, repetimos sucesivamente la operación. Encontramos un primer elemento de los que quedan y lo quitamos.
Para finalizar concatenamos los elementos encontrados (=M2&O2&Q2&S2&U2&V2). En la columna X compruebo que hay concatenaciones repetidas mediante la función CONTAR.SI
(=CONTAR.SI(W:W;W2)). Esas concatenaciones las paso a la hoja PERMUTACIONES referenciando celdas (=Aux!W2).