2014-05-09

Importar ficheros CSV en Excel mediante VBA

Title En Excel, una de las opciones para importar un fichero de texto es conectarse a él (obtener datos externos) mediante el asistente para importar texto: Alt+D+F+X. El conectarnos a datos externos en lugar de abrirlos, nos permitirá poder actualizarlos en el futuro.

Si queremos automatizar este proceso y evitar el uso repetido del asistente, podemos emplear un código similar al que he creado:

Importar CSV seleccionándolo con un cuadro de diálogo

Sub ImportarCSV()
    Dim t As Single
    t = Timer
    Sheets("DATOS").Cells.ClearContents
    strFile = Application.GetOpenFilename("CSV, *.csv")
        If strFile = Empty Then
           Response = MsgBox("Ningún fichero seleccionado", _
           vbOKOnly, "Error")
        Exit Sub
        Else
        End If

    With Sheets("DATOS").QueryTables.Add(Connection:= _
        "TEXT;" & strFile _
        , Destination:=Sheets("DATOS").Range("$A$1"))
        .Name = "fichero"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 850
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = True 'CSV: punto y coma
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1) '5 columnas
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    MsgBox Timer - t
End Sub
En la primera parte iniciamos el cronómetro. Limpia los contenidos de la hoja DATOS en la que importaremos el fichero de texto CSV desde la celda A1. Abre un cuadro de diálogo que nos permite seleccionar el fichero a importar y nos alerta si no seleccionamos ninguno. Finalmente, importa el fichero de texto delimitado por punto y coma de 5 columnas, y nos indica el tiempo empleado en la importación.

Deliberadamente he dejado todas las propiedades que se detallan al grabar una macro usando el asistente de importación. Sin entrar a explicar todas las propiedades del objeto QueryTable, que casi se explican por sí solas, vamos a detenernos en tres:

.RefreshStyle Establece cómo se agregan o eliminan filas de la hoja de cálculo especificada. Si se sobreescriben las filas o no.
.TextFileStartRow Nos permite especificar la fila a partir de la que comienza la importación. Es 1 por defecto.
.TextFileColumnDataTypes Para especificar los tipos de datos de las columnas importadas mediante constantes. El 1 (xlGeneralFormat) las importa con formato general y el 9 (xlSkipColumn) para saltar esa columna. Ej.: TextFileColumnDataTypes = Array(1, 9, 1, 1, 1) saltaría la segunda columna. Si especifica más elementos para la matriz que columnas disponibles, se omiten esos valores.

Anexar nuevos CSV

Además, si necesitamos anexar nuevos ficheros CSV (asumimos la misma estructura que el anterior) podemos usar el siguiente código:

Sub AnexarCSV()
    Dim t As Single
    t = Timer
    Sheets("DATOS").Select
    Dim LastRow As Long
    LastRow = Range("A1").End(xlDown).Row + 1

    With Sheets("DATOS").QueryTables.Add(Connection:= _
        "TEXT;" & ThisWorkbook.Path & "\fichero.csv" _
        , Destination:=Sheets("DATOS").Range("A" & LastRow))
        .Name = "fichero"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertEntireRows ' Inserta filas
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 850
        .TextFileStartRow = 2 ' Salta 1ª línea con encabezado
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = True 'CSV: punto y coma
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    MsgBox Timer - t
End Sub
En la primera parte iniciamos el cronómetro. Identificamos la última fila escrita e importamos el CSV que de copiará a partir de la fila previamente identificada. En este caso, en lugar de elegir el fichero con un cuadro de diálogo, se trata del fichero.txt ubicado en la misma ruta que nuestro Excel. Finalmente, nos indica el tiempo empleado en la importación.

En cualquier caso, si deseamos indicar manualmente la ruta del fichero y que se copie en la hoja activa, basta con sustituir las tres líneas desde With y a .Name por estas:

With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\TU_RUTA_CORRESPONDIENTE\fichero.csv", _ 
Destination:=Range("$A$1"))

29 comentarios:

  1. HOla,

    Felicidades y gracias por el post.

    Mi duda es si es normal que cada vez que ejecute el macro se genera una nueva conexión (he definido el archivo manualmente en el código) y de si puedes ayudarme a modificarla, ya que cuando ejecuto una conexión normalmente se añaden los registros nuevos, pero en esta ocasion se borran todos y se vuelven a "importar" todos.
    Otra pregunta es que opcion he de usar para que las formulas de la derecha se expandan a todos los registros (esta opcion esta cuando haces mediante el asistente una conexion), cosa que supongo que va unida a lo antes preguntado.
    Yo he dejado asi mi codigo.

    Gracias de nuevo

    Sub ImportarCSV()

    Sheets("Llistat").Range("a1:e20000").ClearContents
    With Sheets("Llistat").QueryTables.Add(Connection:= _
    "TEXT;G:\IMMOBLES_B\EXPEDIENTS\GestorDocumental_EBI\BD_GestorDocumentalEBI_CSV.csv", _
    Destination:=Sheets("Llistat").Range("$A$1"))
    .Name = "fichero"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = True
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .RefreshStyle = xlOverwriteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 15
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = 850
    .TextFileStartRow = 1
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = True
    .TextFileSemicolonDelimiter = True 'CSV: punto y coma
    .TextFileCommaDelimiter = False
    .TextFileSpaceDelimiter = False
    .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1) '5 columnas
    .TextFileTrailingMinusNumbers = True
    .Refresh BackgroundQuery:=False
    End With
    'MsgBox ("Expedients actualitzats")
    End Sub

    ResponderEliminar
  2. Hola Oscar:

    Gracias por tu comentario.

    Respecto a tus dudas:

    1. Para que no te borre los datos elimina de tu código la línea:
    Sheets("Llistat").Range("a1:e20000").ClearContents

    2. Para que incluya todos las columnas elimina la línea:
    .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1) '5 columnas
    O bien añade tantos unos como columnas quieras importar.

    Saludos

    ResponderEliminar
  3. Hola,
    Genial post, un 10!

    Mi pregunta es la siguiente, como puedo copiar el nombre del archivo que estoy importando? He intentado varias cosas pero siempre me da el nombre del archivo donde importo y el que que quiero importar.

    Gracias,
    Un saludo

    ResponderEliminar
  4. Hola,

    Genial post, muy útil.

    Mi pregunta es la siguiente, como puedo complementar en esta MACRO la importación del título del archivo que se ha importado? Solo he conseguido el título del fichero donde he importado pero no el que estoy importando.

    Gracias,
    Un saludo!

    ResponderEliminar
    Respuestas
    1. Hola Iván:
      Ya tienes el nombre del fichero almacenado en la variable strFile. Solamente necesitas indicar la celda en la que deseas pegarlo. Por ejemplo añadiendo la línea: Sheets("Hoja1").Range("$A$1") = strFile. Si no quieres la ruta completa, solamente el nombre del fichero y la extensión: Sheets("Hoja1").Range("$A$1") = Mid(strFile, InStrRev(strFile, "\") + 1)
      Saludos

      Eliminar
  5. hola:
    a mi con el codigo de arriba me funciona bien cuando son archivos de menos de 30000 filas pero no funciona cuando son de mas de 30000, tengo el Excel 2016, teneis algun comentario

    ResponderEliminar
    Respuestas
    1. Hola Enric, lo siento pero no puedo reproducir tu error. Con Excel 2013 no tengo ningún problema para importar el número máximo de filas 1.048.576. Te recomiendo que plantees tu duda en un foro de Excel donde quizá puedan ayudarte. Saludos

      Eliminar
  6. Buenos días:

    He copiado la función "ImportarCSV()" en Excel 97. A la hora de ejecutarlo me deja seleccionar el fichero de manera correcta pero el error viene en el "With".

    Mostrandome este mensaje:
    "Se ha producido el error '1004' en tiempo de ejecución:
    Error definido por la aplicación o el objeto"

    Me he documentado todo lo que he podido sobre este error pero no he podido encontrar la solución.
    ¿Podrías echarme una mano?

    En la empresa solo tenemos Office97 y no se plantean actualizarlo.

    ResponderEliminar
    Respuestas
    1. Hola Chete, con la información que me proporcionas, es prácticamnete imposible determinar el motivo del error. Además, Excel 2000 incorporó cambios en VBA. Podrías probarlo en un ordenador que tenga una versión posterior para comprobar si es un tema de compatibilidad. Aunque sea obvio, tienes que dar el nombre DATOS a una hoja o modificar el código. Te recomiendo que busques ayuda en algún foro detallando de una manera mucho más precisa el problema. Algunos foros también permiten adjuntar ficheros y podrían examinar el error.
      Saludos,

      Eliminar
  7. hola... buenas noches; sucede que necesito crear una rutina vba que me permita importar pero sólo algunas columnas del csv; puedes plantear un caso en que se aborde esto ?...
    saludos

    ResponderEliminar
    Respuestas
    1. Hola. Quizá en el futuro dedique una entrada a esta pregunta. Una alternativa es importar todo el csv y luego eliminar las columnas que no necesites. Saludos.

      Eliminar
  8. Hola, buenos dias.

    Tu codigo me ha servido muchisimo, solo tengo una consulta.

    Que instruccion tendria que cambiar, si lo que deseo es que en vez de ir agregando columnas, se agreguen filas, asi no va creciendo a los lados sino que hacia abajo, para posibles analisis de data que deseo hacer.

    ResponderEliminar
    Respuestas
    1. Gracias. No comprendo la pregunta: el código anexa filas no columnas.

      Eliminar
  9. Muchas gracias por tu excelente aporte. Muy útil, claro y preciso.

    ResponderEliminar
    Respuestas
    1. A ti por el comentario, Edu. Me alegro. Saludos.

      Eliminar
  10. Excelente Sub muchísimas gracias, tengo un par de días perdidos buscando resolver este problema, lo único que no me está funcionando es que el texto no toma los acentos ni caracteres especiales (p.e. Finalizaci├│n planificada), me imagino que podría ser uno de los parámetros de configuración, si tienes algún comentario al respecto te lo agradecería.

    Mil gracias y saludos

    ResponderEliminar
    Respuestas
    1. Hola Cesar, yo he tenido el mismo problema y he sustituido la línea:

      .TextFilePlatform = 850

      por:

      .TextFilePlatform = xlMSDOS

      Y ya me funciona correctamente.

      No soy experto, confio en haber sido de ayuda.

      Saludos.

      Eliminar
    2. Gracias por este aporte

      Eliminar
  11. Muchas gracias por el aporte, me has simplificado mucho una tarea tediosa.
    Enhorabuena!!

    ResponderEliminar
  12. Muchas gracias por los codigos. un buen aporte

    ResponderEliminar
  13. Hola, como podría importar varios txt y que queden en la misma hoja , pero que se vayan agregando horizontalmente?, es decir en las columnas

    ResponderEliminar
  14. muchas gracias, muy buen material
    saludos

    ResponderEliminar
  15. En mi archivo CSV hay una columna que tiene muchos datos en una sola celda utilizando saltos de línea, al usar la macro se pierde la información por que se generan nuevas lineas y las columnas siguientes quedan en blanco. Hay alguna forma para que se quede todo el texto de esa celda con los saltos de línea?

    ResponderEliminar
  16. Hola tengo un problema, ya que en una de las celdas tengo muchos datos con salto de línea. Al usar la macro esa columna genera nuevas líneas no inserta toda la información en la misma celda, entonces las columnas siguientes quedan en blanco. Hay alguna forma de respetar los saltos de línea de mi archivo CSV?

    ResponderEliminar
  17. Buenas tardes.
    En mi trabajo se generan muchos reportes en cvs, lo cual que hay que transformar en archivos excel (se generan de a uno) y que cada uno de esos excel necesito sumar el total de importe y contar las transacciones. Es posible?
    Tambien que el resultado de cada uno se vaya a una linea en una hoja para las estadisticas con el total Importe y el total cantidad.
    Existe esa posiblidad?
    Una vez por mes se generan 135 reportes de este tipo y volcar el resumen a una hoja, lo cual es muy tedioso el trabajo. Gracias!

    ResponderEliminar
    Respuestas
    1. Sí existe esa posibilidad. Aunque probablmente sea más eficiente hacerlo con Python o R. Saludos.

      Eliminar

Nube de datos