Cómo abrir CSVs en Excel

Manera buena

• Excel → Datos → Obtener datos externos → Seleccionar todas las columnas con Mayúsculas y elegir Texto

Manera mala

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

Modo bueno (para VBA)

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

Mala manera (para VBA)

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

Métodos adicionales

• Si tiene acceso a la fuente que crea su CSV, puede alterar la sintaxis del CSV.
  Encerrar cada valor con comillas dobles y anteponer 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

• Abra el CSV en el Bloc de notas y copie&pegue todos los valores en Excel. Luego use Datos - Texto en Columnas Desventaja: Texto en Columnas para cambiar los formatos de las columnas 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 ubicados justo a esa celda pueden sobrescribirse (El comportamiento de Texto a columnas fue cambiado 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

Este es 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 le permite importar archivos CSV y TXT. Todos los valores se importan a la hoja activa a partir de la celda seleccionada en ese momento
• El complemento de Excel está disponible para todas las versiones de Office en Windows y Mac
• Todo el complemento tiene sólo 35 líneas de código. Compruebe el código fuente comentado si tiene 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 Add-In y guárdelo en su carpeta de Add-Ins: %appdata%\Microsoft\AddIns
2. Abra Excel y active el Add-In: File tab → Options → Add-Ins → Go To y seleccione ImportCSV.xla
3. Active las macros VBA: File tab → Options → Trust Center → Trust Center Settings → Macro Settings → Enable all macros
4. Reinicie Excel

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

• *

Script PowerShell para abrir CSVs directamente desde el Explorador de Windows

Puede utilizar un script PowerShell 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. Cree un nuevo archivo de texto y pegue el siguiente script. 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()
    }
}

1. Guárdelo en algún lugar como C:\my\folder\myScript.ps1. (Tenga en cuenta la extensión .ps1)
2. Abra su carpeta sendto a través de WinR “ shell:sendto ” Introduzca
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. Nombre el acceso directo, por ejemplo, Excel

“%SystemRoot%\system32\WindowsPowerShell\v1.0\powershell.exe ” -NoProfile -NonInteractive -WindowStyle Hidden -File “C:\my\folder\myScript.ps1”

Ahora puede seleccionar (múltiples) CSVs y abrirlos en Excel mediante Right-click » SendTo » Excel

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 estás abriendo muchos archivos puede que, como yo, te canses de, cada vez, desplazarte hasta la columna 60 para hacer Shift-Click en ella.

EDIT @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, incluso si proporcionas explícitamente los 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.

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] de 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

¡Cuidado!

Cuando utilice el método manual “Excel → Datos → Obtener datos externos → Seleccionar todas las columnas y elegir Texto”……

Esto sólo establecerá las columnas como texto “que tienen datos en la primera fila” (normalmente una fila de encabezado). 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:

Row1Col1, Row1Col2, Row1Col3

Row2Col1, Row2Col2, Row2Col3, Row2Col4

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 texto antes de importar!!!!

Aquí está el procedimiento alternativo al código publicado anteriormente por Jean-François Corbett

Este procedimiento importará el archivo csv a Excel con todas las columnas formateadas como Texto

Public Sub ImportCSVAsText()
    Dim TempWorkbook As Workbook
    Dim TempWorksheet As Worksheet
    Dim ColumnCount As Integer
    Dim FileName As Variant
    Dim ColumnArray() As Integer

    'Get the file name
    FileName = Application.GetOpenFilename(FileFilter:="All Files (*.*),*.*", FilterIndex:=1, Title:="Select the CSV file", MultiSelect:=False)

    If FileName = False Then Exit Sub

    Application.ScreenUpdating = False

    'Open the file temporarily to get the count of columns
    Set TempWorkbook = Workbooks.Open(FileName)
    ColumnCount = TempWorkbook.Sheets(1).Range("A1").SpecialCells(xlCellTypeLastCell).Column
    TempWorkbook.Close SaveChanges:=False

    'Resize the array to number of columns
    ReDim ColumnArray(1 To ColumnCount)

    For i = 1 To ColumnCount
        ColumnArray(i) = xlTextFormat
    Next i

    Set TempWorkbook = Workbooks.Add
    Set TempWorksheet = TempWorkbook.Sheets(1)

    Application.DisplayAlerts = False
    TempWorkbook.Sheets(2).Delete
    TempWorkbook.Sheets(2).Delete
    Application.DisplayAlerts = True

    With TempWorksheet.QueryTables.Add("TEXT;" & FileName, TempWorksheet.Cells(1, 1))
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 1251
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = ColumnArray
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With

End Sub

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. Por lo que sé, no hay manera 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 intente analizar.

Como petición, enviando mi comentario como respuesta (con un poco más de información añadida):

Hey Scripting Guy hizo un artículo en su blog sobre Importing CSV into Excel que podría tener algunas cositas útiles para ti. Jugar con el objeto de datos dentro de powershell puede permitirte hacer lo que quieres.

Aunque el artículo sólo menciona específicamente la importación de los datos en las celdas, lo que puede dejar el formato numérico, puede ser posible jugar con algunas de las propiedades y métodos de Excel ComObject para forzar que los datos entren en las celdas como texto sin procesar (o forzar el formato de las celdas en texto antes o después de la importación).

Por razones desconocidas, aunque proporcione explícitamente formatos para todas las columnas, Excel lo ignorará si la extensión del archivo es CSV.

Algunas opciones:

• Crear una consulta para importar los datos, como sugiere Wetmelon .
  Inconveniente: es posible que te falten los drivers de la base de datos CSV en una máquina de 64 bits.

• Utilice el código de Jean , pero incorpore la copia del archivo a una carpeta temporal y cambie la extensión de la copia.
  Desventaja: No hay enlace con el archivo original (al guardar se sobrescribe la copia); tendrá que borrar manualmente la copia después. Aun así, puede guardar manualmente como sobre el CSV original.

• Abrir el CSV en el Bloc de notas, Ctrl+A, Ctrl+C, pegar en Excel, luego Datos - Texto a Columnas, entonces es el asistente habitual donde puedes establecer todas las columnas a Texto de una sola vez. Es un sabor diferente de la opción anterior, porque también oculta la preciosa extensión de Excel.
  Desventaja: manual.

• Tener un bucle VBA muy sencillo que lea todo el archivo en memoria y lo ponga en la hoja, celda por celda.
  Desventaja: más lento, feo.

Si entiendo bien la pregunta, esto se resuelve fácilmente utilizando la opción de transposición en Paste-Special como se describe aquí .