76 votos

¿Cómo puedo configurar Excel para que siempre importe todas las columnas de los archivos CSV como texto?

Aunque intento evitarlo, de vez en cuando tengo que abrir un archivo CSV en Excel. Cuando lo hago, se formatean las columnas que contienen números, lo que las hace inútiles para mis propósitos. Por lo que sé, la única manera de evitar que esto ocurra es en la importación es cambiar el nombre del archivo para que la extensión no sea .csv y utilizar el asistente de importación para especificar el formato de cada columna individualmente. Para archivos con 50-60 columnas, esto es poco práctico.

Dado que todas las respuestas a esta pregunta tan frecuente en Internet sugieren o bien algún medio para volver a convertir los números formateados una vez abierto el archivo (lo que no me funcionará - quiero resolver el problema general, no unos pocos casos específicos) o bien seleccionar manualmente el tipo de formato de cada columna (lo que no quiero hacer), estoy buscando una forma de establecer una preferencia o estilo global para que todas las columnas de todos los archivos CSV abiertos tengan siempre formato de texto. También sé lo de "blindar" los números con comillas, pero los archivos que recibo no vienen así y esperaba evitar tener que preprocesar los archivos para que Excel no los fastidie.

¿Hay alguna manera de hacer específicamente esto: ¿Dar siempre formato a todas las columnas de los archivos CSV abiertos como texto, sin tener que seleccionar manualmente cada columna durante la importación?

Estoy usando Excel 2003, pero acepto respuestas para el 2007 si es lo que conoces.

1 votos

Hey Scripting Guy hizo un artículo en su blog sobre Importación de CSV a Excel que podría tener algunos datos útiles para usted. Jugar con el objeto de datos dentro de powershell puede permitirte hacer lo que quieres. No se publica como una respuesta, ya que es esencialmente sólo un enlace fuera del sitio, pero podría tener algo de utilidad para usted.

77voto

KevinM1 Puntos 181

Cómo abrir CSVs en Excel

Buen camino

  • Excel → Datos → Obtener datos externos → Seleccionar todas las columnas con Shift y elija Texto

    Ventajas: Trata todos los valores correctamente como texto sin ninguna excepción

    El inconveniente: Más pasos que un simple Haga doble clic en

Mal camino

  • Abrir un CSV con Doble clic o de Excel Abrir con diálogo

    Desventajas: El gestor interno de CSV de Excel interpreta erróneamente los valores con un - o = signo como una fórmula en lugar de un texto

    La desventaja: Se perderán los ceros a la izquierda de los valores binarios como 0001 debido al formato de columna autodetectado por Excel

Buen camino (para VBA)

  • Utilice QueryTables (la contraparte VBA de Get external data ) → Código de ejemplo

    La ventaja: Trata todos los valores correctamente como texto sin ninguna excepción

    Desventajas: Un poco más de código que OpenText método

Mal camino (para VBA)

  • Utilice Workbooks.OpenText método → Código de ejemplo

    Inconveniente: Este método sigue utilizando el gestor de importación CSV interno de Excel con todos sus defectos

    Desventajas: Además, el fieldinfo parámetro de OpenText se ignora si la extensión es CSV. Normalmente, este parámetro permite elegir cada formato de columna, pero no si la extensión es CSV. Puede leer más sobre este comportamiento en Stack Overflow

    Cambiar temporalmente el nombre de la extensión de origen de CSV a TXT y luego de nuevo a CSV es una solución válida si se tiene un control total sobre el archivo de origen

Métodos adicionales

  • Si tiene acceso a la fuente que crea su CSV, puede modificar la sintaxis del mismo.
    Encierre cada valor con comillas dobles y anteponga un signo de igualdad como ="00001" o anteponer un tabulador a cada valor. Ambas formas forzarán a Excel a tratar el valor como texto

    <strong>Contenido original del CSV</strong>
    enter image description here

    <strong>CSV en Excel cuando se abre mediante doble clic</strong>
    enter image description here

    Observe cómo la línea 2 (método de comillas dobles) y la línea 3 (método de tabulación) no son modificadas por Excel

  • ~~Abrir CSV en Bloc de notas y copiar y pegar todos los valores en Excel. A continuación, utilice Datos - Texto a columnas
    El inconveniente: Texto en columnas para cambiar los formatos de columna de general a texto produce resultados inconsistentes. Si un valor contiene un - rodeado de caracteres (por ejemplo, "=E1-S1"), Excel intenta dividir ese valor en más de una columna. Los valores situados a la derecha de esa celda pueden sobrescribirse~~
    (El comportamiento de Texto a columnas se cambió en algún momento entre Excel 2007 y 2013 por lo que ya no funciona)


Complemento de Excel para abrir CSVs e importar todos los valores como texto

Se trata de un complemento de Excel para simplificar las acciones de importación de CSV.
La principal ventaja: es una solución de un solo clic y utiliza QueryTables el mismo método a prueba de balas detrás de Obtener datos externos

  • Añade un nuevo comando de menú a Excel que permite importar archivos CSV y TXT. Todos los valores se importan a la hoja activa a partir de la celda seleccionada en ese momento
  • Los complementos de Excel están disponibles para todas las versiones de Office en Windows y Mac
  • Todo el complemento tiene sólo 35 líneas de código. Compruebe los comentarios código fuente si tienes curiosidad
  • El separador de listas CSV utilizado (coma o punto y coma) se toma de la configuración local de Excel
  • La codificación se establece en UTF-8

Instalación

  1. Descargue el Complemento y guárdalo en tu carpeta de complementos: %appdata%\Microsoft\AddIns
  2. Abra Excel y active el complemento: File tab → Options → Add-Ins → Go To y seleccione ImportCSV.xla
  3. Habilitar las macros VBA: File tab → Options → Trust Center → Trust Center Settings → Macro Settings → Enable all macros
  4. Reiniciar Excel

Verá que hay una nueva entrada en la barra de menús llamada Add-Ins y que puede utilizar este botón para abrir rápidamente sus archivos CSV sin tener que pasar por el molesto diálogo de importación

enter image description here


PowerShell script para abrir CSVs directamente desde el Explorador de Windows

Puede utilizar un PowerShell script para abrir archivos CSV y pasarlos automáticamente a Excel. El script utiliza silenciosamente el método de importación de texto de Excel que trata los valores siempre como texto y, como ventaja, maneja la codificación UTF-8

  1. Crea un nuevo archivo de texto y pega el siguiente script. Se puede encontrar una versión comentada aquí

    $CSVs = @() $args.ForEach({ If ((Test-Path $) -and ($ -Match ".csv$|.txt$")) { $CSVs += ,$_ } })

    if (-Not $null -eq $CSVs) {

    $excel = New-Object -ComObject excel.application 
    $excel.visible = $true
    $excel.SheetsInNewWorkbook = $CSVs.Count    
    $workbook = $excel.Workbooks.Add()
    
    for ($i=0; $i -lt $CSVs.Count; $i++){
    
        $csv = Get-Item $CSVs[$i]
        $worksheet = $workbook.worksheets.Item($i + 1)
        $worksheet.Name = $csv.basename
    
        $TxtConnector = ("TEXT;" + $csv.fullname)
        $Connector = $worksheet.QueryTables.add($TxtConnector,$worksheet.Range("A1"))
        $query = $worksheet.QueryTables.item($Connector.name)
        $query.TextFilePlatform = 65001
        $query.TextFileTextQualifier = 1
        $query.TextFileOtherDelimiter = $Excel.Application.International(5) 
        $query.TextFileParseType  = 1
        $arrFormats = ,2 * $worksheet.Cells.Columns.Count
        $query.TextFileColumnDataTypes = $arrFormats
        $query.AdjustColumnWidth = 1
        $query.Refresh()
        $query.Delete()
    }

    }

  2. Guárdalo en algún lugar como C:\my\folder\myScript.ps1 . (Obsérvese la extensión .ps1 )

    • Abra su carpeta sendto a través de WinR » shell:sendto » Enter
  3. Cree un nuevo acceso directo mediante el botón derecho " Nuevo " Acceso directo y pegue esta línea. No te olvides de cambiar la ruta por la tuya donde has puesto tu script. Nombra el acceso directo, por ejemplo Excel

"%SystemRoot% \system32\WindowsPowerShell\v1.0\powershell.exe "-SinPerfil -NoInteractivo -EstiloDeVentanaOculta -Archivo " C:\my\folder\myScript.ps1 "

Ahora puede seleccionar (varios) CSV y abrirlos en Excel mediante Right-click » SendTo » Excel

1 votos

El complemento fue probado con Office 2003 y 2013

0 votos

La "mejor manera" de arriba es exactamente lo que necesitaba para una importación única. No hay necesidad de meterse con VBA e incluso puede controlar qué columnas se toman como fecha/números (con el resto tomado como texto). Me gustaría que Excel ofreciera abrir este asistente de importación al hacer doble clic en el CSV.

0 votos

@Vadzim Hola, agradezco tu esfuerzo por arreglar los enlaces muertos. Efectivamente ge.tt da algunos problemas en los últimos meses. Pero creo que he solucionado todos los problemas y los antiguos enlaces originales deberían volver a funcionar. No sé por qué Google Chrome advierte antes de descargar algo de ge.tt. Pero todos los demás navegadores funcionan con normalidad y puedo asegurar que no son malware

9voto

Esto funciona:

Sub OpenCsvAsText(ByVal strFilepath As String)

    Dim intFileNo As Integer
    Dim iCol As Long
    Dim nCol As Long
    Dim strLine As String
    Dim varColumnFormat As Variant
    Dim varTemp As Variant

    '// Read first line of file to figure out how many columns there are
    intFileNo = FreeFile()
    Open strFilepath For Input As #intFileNo
    Line Input #intFileNo, strLine
    Close #intFileNo
    varTemp = Split(strLine, ",")
    nCol = UBound(varTemp) + 1

    '// Prepare description of column format
    ReDim varColumnFormat(0 To nCol - 1)
    For iCol = 1 To nCol
        varColumnFormat(iCol - 1) = Array(iCol, xlTextFormat)
        ' What's this? See VBA help for OpenText method (FieldInfo argument).
    Next iCol

    '// Open the file using the specified column formats
    Workbooks.OpenText _
            Filename:=strFilepath, _
            DataType:=xlDelimited, _
            ConsecutiveDelimiter:=False, Comma:=True, _
            FieldInfo:=varColumnFormat

End Sub

Uso:

OpenCsvAsText "C:\MyDir\MyFile.txt"

El archivo separado por comas se abre ahora como hoja de Excel con todas las columnas formateadas como texto.

Ten en cuenta que la solución del asistente de @Wetmelon funciona bien, pero si abres muchos archivos puede que, como yo, te canses de desplazarte cada vez hasta la columna 60 para hacer Shift-Click.

EDITAR @GSerg afirma en el comentario de abajo que esto "no funciona" y "se come los espacios y los ceros a la izquierda". Me limitaré a citar el comentario a la pregunta, que es más descriptivo:

Por razones desconocidas, aunque proporcione explícitamente formatos para todas las columnas en VBA, Excel lo ignorará si la extensión del archivo es CSV. En cuanto cambies la extensión, ese mismo código dará los resultados correctos.

Así que el código de arriba "funciona", pero es asesinado por este ridículo comportamiento de Excel. Sea como sea, tienes que cambiar la extensión a otra que no sea ".csv", ¡lo siento! Después de eso, estás libre.

1 votos

No funciona. Se come los espacios, se come los ceros a la izquierda, etc.

1 votos

1 votos

El método utilizado Workbooks.OpenText tiene sus defectos por diseño (Ver mi respuesta más abajo). Utilice QueryTables en su lugar.

4voto

Colorado Techie Puntos 128

La sugerencia de Wetmelon funciona (incluso con .CSV) si haces lo siguiente:

  1. Abrir Excel en un libro u hoja de trabajo en blanco
  2. Haga clic en Datos > [Obtener datos externos] del texto
  3. Utiliza el "Asistente de importación de texto" como describe Wetmelon (delimitado por comas, selecciona la primera columna, SHIFT+CLICK la última columna, establece todo como Texto).

Sé que son más pasos, pero al menos me permite abrir CSVs de esta manera sin tener que cambiar la extensión

2voto

PeterV Puntos 21

¡Cuidado!

Al utilizar el método manual "Excel → Datos → Obtener datos externos → Seleccionar todas las columnas y elegir Texto"......

Esto sólo establecerá las columnas con texto "que tienen datos en la primera fila" (normalmente una fila de cabecera). Este asistente no muestra las columnas más a la derecha que podrían tener datos más abajo pero no en la primera fila. Por ejemplo:

Fila1Col1, Fila1Col2, Fila1Col3

Fila2Col1, Fila2Col2, Fila2Col3, Fila2Col4

¡¡¡¡Nunca verá la Col 4 en el asistente de importación, por lo que no tendrá la opción de cambiarla de formato general a formato de texto antes de importar!!!!

1 votos

Una buena advertencia, ya que el 99,9999% de las veces, la gente no se desplaza hacia abajo en el asistente de importación, e incluso entonces es difícil coger todas las columnas. Sin embargo, técnicamente, puedes desplazarte hacia abajo mientras estás en el asistente de importación y coger las columnas adicionales que no tienen datos en la primera fila.

0voto

user88859 Puntos 11

Si siempre se importan los mismos datos (formato de registro constante, diseño, etc...) se podría escribir una macro de Access utilizando una especificación de importación y luego volcar los datos de nuevo a Excel. He hecho esto muchas veces. La otra forma en que lo he hecho es utilizar VBA y leer los datos en la hoja de trabajo un registro a la vez y analizarlo a medida que lee. Hasta donde yo sé, no hay forma de establecer un formato por defecto durante la importación en Excel, e incluso si se pudiera, causaría problemas con el siguiente tipo de archivo que se intente analizar.

1 votos

La cuestión es que no estoy intentando hacer ningún análisis sintáctico, excepto poner la cadena de texto que estaba entre las comas en las columnas cuando la importo. Si necesito que se formatee de cierta manera, puedo hacerlo (como hacer que las cadenas de fecha sean fechas, etc.), pero sólo necesito que los datos se queden solos inicialmente. ¿Por qué debería ser tan difícil?

EnMiMaquinaFunciona.com

EnMiMaquinaFunciona es una comunidad de administradores de sistemas en la que puedes resolver tus problemas y dudas.
Puedes consultar las preguntas de otros sysadmin, hacer tus propias preguntas o resolver las de los demás.

Powered by: