Como abrir CSV no Excel

Bom caminho

• Excel → Dados → Obter dados externos → Seleccionar todas as colunas com Shift e escolher Texto

Mau caminho

• Abrir um CSV com Douplo Clique ou Excel’s Abrir com diálogo

Bom caminho (para VBA)

• - Use QueryTables (o equivalente em VBA a Get external data) → Código de exemplo

Bad way (para VBA)

• Use Workbooks.OpenText método → Código de exemplo

Métodos adicionais

• Se tiver acesso à fonte que cria o seu CSV, pode alterar a sintaxe do CSV.
  Coloque cada valor com aspas duplas e prefira um sinal igual como ="00001" ou prefira um separador para cada valor. Ambas as maneiras forçarão o Excel a tratar o valor como texto

• Abrir o CSV em Bloco de notas e copiar&colar todos os valores para o Excel. Depois utilizar Data - Text to Columns Downside: Texto em Colunas para alterar formatos de colunas de volta geral para texto produz resultados inconsistentes. Se um valor contém um - rodeado por caracteres (por exemplo “=E1-S1”), o Excel tenta dividir esse valor em mais do que uma coluna. Os valores localizados directamente nessa célula podem ser sobrescritos (O comportamento de texto para colunas foi alterado algures entre o Excel 2007 e 2013 para que não funcione mais)

Suplemento Excel para abrir CSVs e importar todos os valores como texto

Este é um Plug-in do Excel para simplificar as acções de importação de CSV.
A principal vantagem: É uma solução com um clique e utiliza QueryTables, o mesmo método à prova de bala por detrás de Get external data

• Adiciona um novo comando de menu ao Excel que lhe permite importar ficheiros CSV e TXT. Todos os valores são importados para a folha activa a partir da célula actualmente seleccionada
• O suplemento Excel está disponível para todas as versões Office em Windows e Mac
• O suplemento completo tem apenas 35 linhas de código. Verifique o comentado código fonte & se estiver curioso
• O separador de lista CSV usado (vírgula ou ponto e vírgula) é retirado das suas definições locais do Excel
• A codificação é definida para UTF-8

Instalação

1. Descarregue o Add-In e guarde-o na sua pasta Add-Ins: %appdata%\Microsoft\AddIns
2. Abra o Excel e active o Add-In: File tab → Options → Add-Ins → Go To e seleccione ImportCSV.xla
3. Activar as macros VBA: File tab → Options → Trust Center → Trust Center Settings → Macro Settings → Enable all macros
4. Reiniciar Excel

Vai notar uma nova entrada na barra de menu chamada Add-Ins e usa este botão para abrir rapidamente os seus ficheiros CSV sem passar pelo incómodo do diálogo Importar

PowerShell script para abrir CSVs directamente do Explorador do Windows

Pode usar um script PowerShell para abrir ficheiros CSV e passá-los automaticamente para o Excel. O script utiliza silenciosamente o método de importação de texto do Excel que trata os valores sempre como texto e, como bónus, trata a codificação UTF-8

1. Crie um novo ficheiro de texto e cole o script abaixo. Uma versão comentada pode ser encontrada aqui

$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. Guarde-a algures como C:\my\folder\myScript.ps1. (Note a extensão .ps1)
2. Abra a sua pasta sendto via WinR “ shell:sendto ” Introduza
3. Crie um novo atalho através do botão direito do rato “ Novo ” Atalho e cole esta linha. Não se esqueça de mudar o caminho para o seu próprio, onde colocou o seu script. Dê um nome ao atalho, por exemplo, Excel

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

Agora pode seleccionar (múltiplos) CSVs e abri-los no Excel via Right-click » SendTo » Excel

Isto 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

Utilização:

OpenCsvAsText "C:\MyDir\MyFile.txt"

Ficheiro separado por vírgula está agora aberto como folha de Excel com todas as colunas formatadas como texto.

Note que a solução wizard de @Wetmelon funciona muito bem, mas se estiver a abrir muitos ficheiros, então pode, como eu, cansar-se de, de cada vez, rolar para a coluna 60 a fim de Shift-Click it.

EDIT @GSerg afirma no comentário abaixo que este “não funciona” e “come espaços e zeros iniciais”. Vou apenas citar o comentário à pergunta, que é mais descritivo:

Por razões desconhecidas, mesmo que forneça explicitamente formatos para todas as colunas em VBA, o Excel irá ignorá-lo se a extensão do ficheiro for CSV. Assim que alterar a extensão, esse mesmo código produzirá os resultados correctos.

Assim, o código acima “funciona”, mas é morto por este comportamento ridículo do Excel. Seja qual for a forma como o corta, fica preso a ter de mudar a extensão para algo que não seja “.csv”, desculpe! Depois disso, está em casa livre.

A sugestão de Wetmelon funciona (mesmo com .CSV) se fizer o seguinte:

1. abra o Excel a uma pasta de trabalho em branco ou a uma folha de trabalho
2. Clique em Data > [Obter Dados Externos] a partir do Texto
3. Use o “Assistente de Importação de Texto” como Wetmelon descreve (Vírgula delimitada, seleccione a primeira coluna, SHIFT+CLICK a última coluna, defina tudo para Texto).

Eu sei que são mais passos, mas pelo menos permite-me abrir CSV desta forma sem ter de alterar a extensão

Cuidado!

Ao usar o método manual “Excel → Data → Obtenha dados externos → Seleccione todas as colunas e escolha Texto”……

Isto apenas definirá as colunas para texto “que tenham dados na primeira linha” (tipicamente uma linha de cabeçalho). Este assistente não mostra as colunas mais à direita que possam ter dados mais abaixo mas não na primeira fila. Por exemplo:

Row1Col1, Row1Col2, Row1Col3

Row2Col1, Row2Col2, Row2Col3, Row2Col4

Nunca verá Col 4 no assistente de importação, pelo que não terá a opção de o alterar do formato geral para o formato de texto antes de importar!!!!

Aqui está o procedimento alternativo ao código publicado acima por Jean-François Corbett

Este procedimento importará o ficheiro csv para Excel com todas as colunas formatadas 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

Se estiver sempre a importar os mesmos dados (formato de registo constante, layout, etc…) poderá escrever uma macro Access usando uma especificação de importação e depois descarregar os dados de volta para o Excel. Isto pode ser feito por vezes. A outra forma de o fazer é usar VBA e ler os dados na folha de trabalho um registo de cada vez e analisá-los à medida que lêem. Tanto quanto sei, não há forma de definir um formato padrão durante a importação em Excel e mesmo que pudesse causar problemas com o próximo tipo de ficheiro que se tente analisar.

Como pedido, enviando o meu comentário como resposta (com um pouco mais de informação adicionada):

Hey Scripting Guy fez um artigo no blog sobre Importar CSV para o Excel que pode ter algumas dicas úteis para si. Brincar com o objecto de dados dentro da powershell pode permitir-lhe fazer o que quiser.

Embora o artigo mencione especificamente apenas a importação dos dados para as células que podem deixar o formato numérico, pode ser possível brincar com algumas das propriedades e métodos do Excel ComObject para forçar os dados a entrar nas células como texto em bruto (ou forçar a formatação das células em texto antes ou depois da importação).

Por razões desconhecidas, mesmo que forneça explicitamente formatos para todas as colunas, o Excel irá ignorá-lo se a extensão do ficheiro for CSV.

Algumas opções:

• Criar uma consulta para importar os dados, como Wetmelon sugere .
  Desvantagem: podem faltar controladores de base de dados CSV numa máquina de 64 bits.

• Usar código de Jean , mas incorporar a cópia do ficheiro para uma pasta temporária e alterar a extensão da cópia.
  Desvantagem: Sem ligação ao ficheiro original (guardar irá sobrescrever a cópia); terá de apagar manualmente a cópia depois. Mesmo assim, pode guardar manualmente como sobre o CSV original.

• Abra o CSV no Bloco de Notas, Ctrl+A, Ctrl+C, cole no Excel, depois Data - Text to Columns, depois é o assistente habitual onde pode definir todas as colunas para Texto de uma só vez. É um sabor diferente da opção anterior, porque também esconde a preciosa extensão do Excel.
  Desvantagem: manual.

• Tenha um laço VBA muito simples que lê todo o ficheiro na memória e o coloca na folha, célula a célula.
  Desvantagem: mais lento, mais feio.

Se bem entendi a questão, isto é facilmente resolvido utilizando a opção Transpor em Colar - Especial como descrito aqui .