Hoe CSV’s te openen in Excel

Goede manier

• Excel → Gegevens → Externe gegevens ophalen → Selecteer alle kolommen met Shift en kies Tekst

Slechte manier

• Open een CSV met Double Click of Excel’s Openen met dialoogvenster

Goede manier (voor VBA)

• Gebruik QueryTables (VBA tegenhanger van Get external data) → Voorbeeld code

• Slechte manier (voor VBA)

• Gebruik QueryTables (VBA tegenhanger van Workbooks.OpenText). Gebruik QueryTables (de VBA tegenhanger van ="00001") → Voorbeeld code

Slechte manier (voor VBA)

• Gebruik - methode → Voorbeeld code

Aanvullende methoden

• Als je toegang hebt tot de bron die je CSV maakt, kun je de CSV-syntax veranderen.
  Sluit elke waarde in met dubbele aanhalingstekens en zet er een gelijkheidsteken voor zoals QueryTables of zet een tab voor elke waarde. Beide manieren dwingen Excel om de waarde als tekst te behandelen

• Open CSV in Kladblok en kopieer&plak alle waarden naar Excel. Gebruik dan Data - Tekst naar Kolommen Nadeel: Tekst in Kolommen voor het wijzigen van kolomindelingen van algemeen terug naar tekst levert inconsistente resultaten op. Indien een waarde een %appdata%\Microsoft\AddIns bevat omgeven door tekens (b.v. “=E1-S1”), probeert Excel die waarde op te splitsen in meer dan één kolom. Waarden die zich rechts van die cel bevinden kunnen overschreven worden (Het gedrag van Tekst naar kolommen is ergens tussen Excel 2007 en 2013 veranderd, dus het werkt niet meer)

Excel Add-In om CSV’s te openen en alle waarden als tekst te importeren

Dit is een Excel Plug-in om CSV Import acties te vereenvoudigen.
Het belangrijkste voordeel: het is een één-klik oplossing en gebruikt File tab → Options → Add-Ins → Go To, dezelfde bulletproof methode achter Get external data

• Het voegt een nieuwe menu-opdracht toe aan Excel waarmee je CSV en TXT bestanden kunt importeren. Alle waarden worden geïmporteerd naar het actieve blad, beginnend bij de huidig geselecteerde cel
• Excel Add-in zijn beschikbaar voor alle Office versies op Windows en Mac
• De hele Add-In heeft slechts 35 regels code. Controleer de becommentarieerde bron code als u nieuwsgierig bent
• Het gebruikte CSV lijst scheidingsteken (komma of puntkomma) is overgenomen van uw lokale Excel instellingen
• Codering is ingesteld op UTF-8

Installatie

1. Download de Add-In en sla hem op in uw Add-Ins map: ImportCSV.xla
2. Open Excel en activeer de Add-In: File tab → Options → Trust Center → Trust Center Settings → Macro Settings → Enable all macros en selecteer: C:\my\folder\myScript.ps1
3. Schakel VBA macro’s in: .ps1
4. Herstart Excel

U ziet een nieuwe menubalk met de naam Add-Ins en u gebruikt deze knop om snel uw CSV bestanden te openen zonder het gedoe van het Import dialoogvenster

• *

PowerShell script om CSV’s direct vanuit Windows Verkenner te openen

U kunt een PowerShell script gebruiken om CSV bestanden te openen en ze automatisch aan Excel door te geven. Het script gebruikt in stilte Excel’s tekst import methode die waarden altijd als tekst behandelt en, als bonus, UTF-8 codering

1. Maak een nieuw tekst bestand en plak het onderstaande script. Een becommentarieerde versie kan [ hier ]0x3&

$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. Sla het ergens op zoals shell:sendto. (Let op de extensie Right-click » SendTo » Excel)
2. Open je sendto folder via WinR “ 0x6& ” Enter
3. Maak een nieuwe snelkoppeling via Rechtermuisknop “ Nieuw ” Snelkoppeling en plak deze regel. Vergeet niet het pad te veranderen naar je eigen pad waar je je script hebt gezet. Geef de snelkoppeling een naam, bijvoorbeeld Excel

“%SystemRoot%system32WindowsPowerShell:v1.0powershell.exe ” -NoProfile -NonInteractive -WindowStyle Hidden -File “C:\foldermyScript.ps1”

Nu kunt u (meerdere) CSV’s selecteren en openen in Excel via 0x6&

Dit werkt:

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

Gebruik:

OpenCsvAsText "C:\MyDir\MyFile.txt"

Komma-gescheiden bestand wordt nu geopend als Excel sheet met alle kolommen geformatteerd als tekst.

Merk op dat @Wetmelon’s wizard-oplossing prima werkt, maar als je veel bestanden opent, kun je, net als ik, moe worden van het telkens scrollen naar kolom 60 om daar op te Shift-klikken.

EDIT @GSerg zegt in het commentaar hieronder dat dit “niet werkt” en “spaties en voorloopnullen opeet”. Ik zal het commentaar bij de vraag citeren, dat is meer beschrijvend:

Om onbekende redenen, zelfs als je expliciet formaten opgeeft voor alle kolommen in VBA, zal Excel het negeren als de bestandsextensie CSV is. Zodra je de extensie wijzigt, levert diezelfde code de juiste resultaten op.

Dus de code hierboven “werkt”, maar wordt om zeep geholpen door dit belachelijke gedrag van Excel. Hoe je het ook wendt of keert, je zit vast aan het veranderen van de extensie in iets anders dan “.csv”, sorry! Daarna bent u vrij.

Wetmelon’s suggestie werkt (zelfs met .CSV) als je het volgende doet:

1. Open Excel naar een lege werkmap of een leeg werkblad
2. Klik op Gegevens > [Haal externe gegevens op] Uit tekst
3. Gebruik de “Tekst Import Wizard” zoals Wetmelon beschrijft (Komma afgebakend, selecteer de eerste kolom, SHIFT+CLICK de laatste kolom, zet alles op Tekst).

Ik weet dat het meer stappen zijn, maar het stelt me tenminste in staat CSV’s op deze manier te openen zonder de extensie te hoeven veranderen

Pas op!

Bij gebruik van de handmatige methode “Excel → Gegevens → Externe gegevens ophalen → Selecteer alle kolommen en kies Tekst”……

Dit zal alleen de kolommen op tekst zetten “die gegevens hebben in de eerste rij” (typisch een header rij). Deze wizard toont u niet de kolommen verder naar rechts die misschien gegevens hebben verder naar onder maar niet in de eerste rij. Bijvoorbeeld:

Row1Col1, Row1Col2, Row1Col3

Row2Col1, Row2Col2, Row2Col3, Row2Col4

U zult kol 4 nooit zien in de import wizard, dus u krijgt niet de optie om het te veranderen van algemeen naar tekst formaat voor het importeren!!!!

Hier is de alternatieve procedure voor de hierboven geposte code door Jean-François Corbett

Deze procedure zal csv bestand importeren in Excel met alle kolommen geformatteerd als Tekst

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

Als u altijd dezelfde gegevens importeert (constant record formaat, layout, enz…) zou u een Access macro kunnen schrijven met gebruikmaking van een import spec en dan de gegevens terug naar Excel dumpen. Ik heb dit al verschillende keren gedaan. De andere manier waarop ik het heb gedaan is VBA te gebruiken en de gegevens één voor één in te lezen in het werkblad en ze te ontleden terwijl ze worden gelezen. Voor zover ik weet is er geen manier om een standaard formaat in te stellen tijdens het importeren in Excel en zelfs als je dat zou kunnen zou het problemen veroorzaken met het volgende bestandstype dat je probeert te parsen.

Zoals gevraagd, mijn commentaar als antwoord (met een beetje meer info toegevoegd):

Hey Scripting Guy heeft een blog artikel over CSV importeren in Excel dat misschien een aantal nuttige weetjes voor u heeft. Door met het data object in powershell te spelen, kun je misschien doen wat je wilt.

Hoewel in het artikel specifiek wordt gesproken over het importeren van de gegevens in de cellen, waardoor de getalnotatie kan blijven staan, is het wellicht mogelijk om te spelen met enkele van de Excel ComObject eigenschappen en methoden om de gegevens als onbewerkte tekst in plaats daarvan de cellen in te laten gaan (of de opmaak van de cellen in tekst te forceren voor of na de import).

Om onbekende redenen, zelfs als u expliciet formaten opgeeft voor alle kolommen, zal Excel deze negeren als de bestandsextensie CSV is.

Enkele opties:

• Maak een query om de gegevens te importeren, zoals Wetmelon suggereert .
  Nadeel: je kunt CSV database drivers missen op een 64-bit machine.

• Gebruik Jean’s code , maar neem het kopiëren van het bestand naar een tijdelijke map en het veranderen van de extensie van de kopie op.
  Nadeel: Geen link naar het originele bestand (opslaan overschrijft de kopie); u zult de kopie naderhand handmatig moeten verwijderen. Toch kunt u handmatig Opslaan als over het oorspronkelijke CSV.

• Open de CSV in Notepad, Ctrl+A, Ctrl+C, plakken in Excel, dan Data - Tekst naar Kolommen, dan is het de gebruikelijke wizard waar je alle kolommen in één keer op Tekst kunt zetten. Het is een andere smaak van de vorige optie, omdat het ook de kostbare extensie van Excel verbergt.
  Nadeel: handmatig.

• Heb een heel eenvoudige VBA-lus die het hele bestand in het geheugen inleest en cel voor cel op het blad zet.
  Nadeel: trager, lelijk.

Als ik de vraag goed begrijp, is dit eenvoudig op te lossen met de Transpose optie in Paste-Special zoals hier beschreven.