Fechas de .csv a Excel - otras técnicas

En relación a la nota sobre los problemas que pueden surgir al importar fechas de un archivo texto .csv a Excel, dos lectores me sugieren técnicas que no fueron tratadas en el post. Javi sugiere usar Texto en Columnas (la opción más obvia que ignoré olímpicamente, mea culpa!) y Miguel (Power User en Español) sugiere usar la propiedad "locale" del Power Query.

Ambas técnicas merecen algo más que un comentario al pie del post, así que vamos a mostrar cómo usarlas.

Recordemos que el formato de fechas en los distintos países genera en potencia un problema al importar fechas de un archivo .csv a Excel. Al abrir directamente un archivo .csv, Excel "decide" que tipo de dato irá en cada celda. Todo lo que se vea como número será transformado en número (también si va precedido por uno o más ceros); todo lo que se vea como fecha será transformado en fecha. Todo lo demás será importado como texto.
Dado que el formato de fecha en los Estados Unidos (y en otras áreas del mundo) es mes/día/año mientras que la mayoría de los países hispanoparlantes y en Europa es día/mes/año, las fechas pueden ser importadas incorrectamente. Por ejemplo la fecha 10/06/2014 en los Estados Unidos es el 6 de octubre, mientras que en la Argentina es el diez de junio.

La técnica sugerida por Javi es usar Texto en Columnas y en el tercer paso elegir el formato MDA lo que transformará las fechas en forma correcta. Este video muestra la técnica



La técnica sugerida por Miguel es más avanzada y requiere que tengamos instalado el complemento Power Query (Excel 2010 en adelante).

Entre otras fuentes el Power Query permite importar datos también de archivos .csv. Una vez importados los datos al editor del Power Query podemos determinar el formato de fecha del archivo de origen (la propiedad "locale") para que estas sean interpretadas correctamente. Al cargar los datos a la hoja de Excel estos serán transformados correctamente.

Los datos en el archivo .csv son los siguientes

El proceso con el Power Query puede verse en este video

Importar fechas de un archivo .csv a una hoja de Excel

Ya hemos tocado en el pasado los problemas que pueden surgir cuando abrimos en forma directa archivos texto .csv en hojas de Excel. Por "forma directa" me refiero a archivos .csv abiertos con un doble click o usando Abrir. Excel interpreta los datos de acuerdos a ciertas reglas y pueden producir cambios indeseados. Por ejemplo el texto "012345" que representa, digamos, un número de catálogo será transformado en "12345".

El problema es particularmente grave cuando los datos importados son fechas. Supongamos que recibimos un archivo .csv con una lista de fechas que nos envía una empresa de los Estados Unidos. En los Estados Unidos se usa el formato mes/día/año mientras que en la mayoría de los países hispanoparlantes el formato de fecha es día/mes/año. Al abrir el archivo .csv directamente, los valores que Excel no interpreta como fechas de acuerdo a las definiciones regionales serán transformados en texto.Veamos este ejemplo:

Podemos ver que algunas fechas están alineadas a la izquierda y otras a la derecha.  Los valores alineados a la derecha han sido importados como fechas (al ser números Excel los alinea a la derecha), mientras que los valores alineados a la izquierda son texto. Esto se debe que Excel no puede interpretar  esos valores como fecha siguiendo el formato regional día/mes/año (por ejemplo en la celda A2, donde el número de mes sería 23).

El valor en la celda A3 nos muestra el problema más grave que se puede generar cuando importamos archivos .csv con fechas. La fecha, siguiendo el formato de los Estados Unidos, es el 6 de Octubre pero Excel la ha transformado en el 10 de Junio.

Señalemos que los datos importados deben ser fechas, de manera que podamos realizar operaciones con ellos.

Podemos transformar los textos en fechas usando una fórmula como

=SI(ESTEXTO(A3),VALOR(EXTRAE(A3,4,2)&"/"&IZQUIERDA(A3,2)&"/"&DERECHA(A3,4)),VALOR(TEXTO(A3,"mm/dd/yyyy")))

Podemos ver que el valor de la celda A3 es transformado correctamente por la fórmula en 06/10/2012.

Otra solución es usar una macro para forzar la transformación. La ventaja de la macro consiste en que no debemos crear las fórmulas para cada hoja; podemos guardar la macro en el cuaderno Personal y usarla en cada hoja que necesitemos sin necesidad de cargarla con fórmulas.

El código básico de la macro es

Sub USDdate_to_EURdate()
    Dim rngcell As Range

    On Error Resume Next
    For Each rngcell In Selection
        rngcell = CDate(Format(rngcell, "mm/dd/yyyy"))
    Next rngcell
    On Error GoTo 0

End Sub

Esta macro reemplaza los valores en el rango seleccionado por fechas con formato (dd/mm/yyyy).

Este código más elaborado nos permite elegir el rango donde copiar los resultados

Sub USDdate_to_EURdate_2()
    Dim rngcell As Range
    Dim rngOrigin As Range, rngDest As Range
    Dim iX As Long

    'seleccionar el rango a transformar
    Set rngOrigin = Application.InputBox(prompt:="Seleccione el rango a transformar", _
                                        Title:="Rango a transformar", _
                                        Type:=8)

    'comprobar si el rango elegido es vertical/columna
    If rngOrigin.Columns.Count > 1 Then
        MsgBox "El rango seleccionado debe contener solo una columna", vbCritical, "Error en la seleccion"
        Exit Sub
    End If

    'seleccionar la primer celda del destino
    Set rngDest = Application.InputBox(prompt:="Seleccione la primer celda del rango del destino", _
                                        Title:="Destino", _
                                        Type:=8)
    'comprobar que se haya elegido una sola celda
    If rngDest.Count > 1 Then
        MsgBox "Debe seleccionar solo una celda", vbCritical, "Error en la seleccion"
        Exit Sub
    End If

    Application.ScreenUpdating = False
    On Error Resume Next 'en caso de haber celdas vacias o valores no validos en el rango elegido
    For iX = 0 To rngOrigin.Count - 1
        rngDest.Offset(iX, 0) = CDate(Format(rngOrigin(iX + 1), "mm/dd/yyyy"))
    Next iX
    On Error GoTo 0
    Application.ScreenUpdating = True
 
End Sub

Otro uso del Power Query - Dividir columna de ancho variable

Esta vez Eduardo entró a mi oficina sonriendo.

- El jefe me pidió que separe esta lista en dos columnas: una con el nombre del cliente y la otra con el país

- Usá Texto en Columnas, le dije sin mirar la lista ocupado como estaba con mis propios asuntos.
- No se puede. Fijate que el país es la última palabra en la celda pero cada celda contiene un número distinto de palabras.
- Y si no se puede por qué sonreís.
- Porque encontré la solución. Me fijé en el post que publicaste hace unos años sobre cómo extraer el último elemento de una celda  y apliqué la que mostrabas allí a mi problema.
- Ah, que bueno. Mostrame lo que hiciste.

Lo que hizo Eduardo es crear una columna auxiliar con esta fórmula

=SUSTITUIR(A2," ","#",LARGO(A2)-LARGO(SUSTITUIR(A2," ","")))

Lo que hace esta fórmula es poner un símbolo # en lugar del último espacio en la celda. De esta manera creamos un criterio para encontrar la última palabra o valor en la celda

La expresión LARGO(A2)-LARGO(SUSTITUIR(A2," ","")) calcula la cantidad de espacios entre palabras que hay en la celda y este resultado lo usamos como argumento en la función SUSTITUIR para poner el # en el último espacio.

Para poder usar Texto en Columnas, Eduardo tuvo que eliminar las fórmulas de la columna auxiliar con Copiar-Pegado Especial-Valores. Y finalmente

La misma tarea puede hacerse con Power Query. El Power Query tiene también un método para dividir columnas pero con la ventaja de ser más flexible ya que nos permite determinar la ubicación del separador (en nuestro ejemplo el espacio) si éste se repite dentro del registro (celda).

Veamos el proceso. Empezamos por convertir la lista de clientes en Tabla (Insertar-Tablas-Tabla) para poder usar el Power Query con facilidad. Activamos la pestaña del Power Query en la cinta y elegimos Excel Data-From Table

Excel exporta los datos de la tabla a la ventana del editor del Power Query. En la ventana del editor de la consulta seleccionamos la columna y apretamos el icono Split Column- By Delimiter

En el diálogo que se abre elegimos las opciones Espacio (Space) y "el último a la derecha" (At the right-most delimiter) y apretamos OK

Todo lo que nos queda por hacer es transferir el resultado a una hoja de Excel, lo que hacemos con el menú  Close&Load

Los nombres de las columnas los podemos cambiar en la ventana del editor del Power Query antes de transferir la consulta la hoja de Excel (usando Rename) o directamente en la hoja de Excel.