Comment ouvrir des CSV dans Excel

Bonne façon

• Excel → Données → Obtenir des données externes → Sélectionner toutes les colonnes avec Shift et choisir Text

Mauvaise façon

• Ouvrir un CSV avec Double Click ou le dialogue Open with d'Excel

Bonne façon (pour VBA)

• Utilisez les QueryTables (l'équivalent VBA de Get external data) → Exemple de code

Mauvaise manière (pour VBA)

• Utilisez la méthode Workbooks.OpenText → Exemple de code

Méthodes supplémentaires

• Si vous avez accès à la source qui crée votre CSV, vous pouvez modifier la syntaxe CSV.
  Mettez chaque valeur entre guillemets doubles et préfixez-la par un signe égal comme ="00001" ou ajoutez une tabulation à chaque valeur. Les deux méthodes forcent Excel à traiter la valeur comme du texte

• Ouvrir le CSV dans Bloc-notes et copier&coller toutes les valeurs dans Excel. Ensuite, utilisez Data - Texte aux colonnes en bas : Text in Columns pour changer le format des colonnes de l'arrière général au texte produit des résultats incohérents. Si une valeur contient un - entouré de caractères (par exemple “=E1-S1”), Excel essaie de diviser cette valeur en plusieurs colonnes. Les valeurs situées à droite de cette cellule peuvent être écrasées (Le comportement de Text to columns a été modifié quelque part entre Excel 2007 et 2013 et ne fonctionne donc plus)

Excel Add-In pour ouvrir les CSV et importer toutes les valeurs sous forme de texte

Il s'agit d'un Plug-in Excel pour simplifier les actions d'importation CSV.
Le principal avantage : c'est une solution en un clic et utilise QueryTables, la même méthode à l'épreuve des balles derrière Get external data

• Il ajoute une nouvelle commande de menu à Excel qui permet d'importer des fichiers CSV et TXT. Toutes les valeurs sont importées dans la feuille active à partir de la cellule actuellement sélectionnée
• Le complément Excel est disponible pour toutes les versions Office sur Windows et Mac
• L'ensemble du complément ne comporte que 35 lignes de code. Vérifiez le commentaire code source si vous êtes curieux
• Le séparateur de liste CSV utilisé (virgule ou point-virgule) est pris dans vos paramètres Excel locaux
• L'encodage est défini en UTF-8

Installation

1. Téléchargez le Add-In et enregistrez-le dans votre dossier Add-Ins : %appdata%\Microsoft\AddIns
2. Ouvrez Excel et activez l'Add-In : File tab → Options → Add-Ins → Go To et sélectionnez ImportCSV.xla
3. Activez les macros VBA : File tab → Options → Trust Center → Trust Center Settings → Macro Settings → Enable all macros
4. Redémarrez Excel

Vous remarquerez une nouvelle entrée dans la barre de menu appelée Add-Ins et vous utiliserez ce bouton pour ouvrir rapidement vos fichiers CSV sans passer par les tracas du dialogue d'importation

• *

Script PowerShell pour ouvrir les CSV directement depuis l'explorateur Windows

Vous pouvez utiliser un script PowerShell pour ouvrir les fichiers CSV et les passer automatiquement à Excel. Le script utilise silencieusement la méthode d'importation de texte d'Excel qui traite les valeurs toujours comme du texte et, en bonus, gère l'encodage UTF-8

1. Créez un nouveau fichier texte et collez le script ci-dessous. Une version commentée peut être trouvée ici

$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. Enregistrez-la quelque part comme C:\my\folder\myScript.ps1. (Notez l'extension .ps1)
2. Ouvrez votre dossier sendto via WinR “ shell:sendto ” Entrez
3. Créez un nouveau raccourci via le clic droit “ Nouveau ” Raccourci et collez cette ligne. N'oubliez pas de changer le chemin d'accès à votre propre dossier où vous avez mis votre script. Nommez le raccourci, par exemple, Excel

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

Vous pouvez maintenant sélectionner (plusieurs) CSV et les ouvrir dans Excel via Right-click » SendTo » Excel

Cela fonctionne :

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

Usage :

OpenCsvAsText "C:\MyDir\MyFile.txt"

Le fichier séparé par des virgules est maintenant ouvert sous forme de feuille Excel avec toutes les colonnes formatées en texte.

Notez que la solution de l'assistant de @Wetmelon fonctionne très bien, mais si vous ouvrez de nombreux fichiers, vous risquez, comme moi, de vous lasser, à chaque fois, de faire défiler la colonne 60 pour la mettre en Majuscule-Clic.

EDIT @GSerg déclare dans le commentaire ci-dessous que cela “ne fonctionne pas” et “mange les espaces et les zéros de tête”. Je vais simplement citer le commentaire de la question, qui est plus descriptif :

Pour des raisons inconnues, même si vous fournissez explicitement des formats pour toutes les colonnes en VBA, Excel l'ignorera si l'extension de fichier est CSV. Dès que vous changez l'extension, ce même code donnera les résultats corrects.

Donc le code ci-dessus “fonctionne”, mais il est tué par ce comportement ridicule d'Excel. Quelle que soit la façon dont vous le coupez, vous êtes obligé de changer l'extension pour autre chose que “.csv”, désolé ! Après cela, vous êtes libre.

La suggestion de Wetmelon fonctionne (même avec .CSV) si vous faites ce qui suit :

1. ouvrez Excel à un classeur ou une feuille de calcul vierge
2. Cliquez sur Données > [Obtenir des données externes] à partir du texte
3. Utilisez l’“Assistant d'importation de texte” comme le décrit Wetmelon (délimité par des virgules, sélectionnez la première colonne, SHIFT+CLICK la dernière colonne, mettez tout en Texte).

Je sais que c'est plus d'étapes, mais au moins cela me permet d'ouvrir des CSV de cette façon sans avoir à changer l'extension

Attention !

Lorsque vous utilisez la méthode manuelle “Excel → Données → Obtenez des données externes → Sélectionnez toutes les colonnes et choisissez Texte”……

Cela ne mettra en place que les colonnes de texte “qui ont des données dans la première ligne” (généralement une ligne d'en-tête). Cet assistant ne vous montre pas les colonnes les plus à droite qui pourraient contenir des données plus loin en dessous mais pas dans la première ligne. Par exemple :

Row1Col1, Row1Col2, Row1Col3

Row2Col1, Row2Col2, Row2Col3, Row2Col4

Vous ne verrez jamais Col 4 dans l'assistant d'importation, vous n'aurez donc pas la possibilité de la changer du format général au format texte avant l'importation !!!!

Voici la procédure alternative au code affiché ci-dessus par Jean-François Corbett

Cette procédure permettra d'importer un fichier csv dans Excel avec toutes les colonnes formatées en Texte

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 vous importez toujours les mêmes données (format d'enregistrement constant, mise en page, etc…), vous pouvez écrire une macro Access à l'aide d'une spécification d'importation et ensuite renvoyer les données vers Excel. C'est déjà fait plusieurs fois. L'autre façon de procéder est d'utiliser VBA et de lire les données dans la feuille de calcul, un enregistrement à la fois, et de les analyser au fur et à mesure de leur lecture. Pour autant que je sache, il n'y a aucun moyen de définir un format par défaut lors de l'importation dans Excel et même si vous le pouviez, cela poserait des problèmes avec le prochain type de fichier que vous essayez d'analyser.

A la demande, je vous envoie mon commentaire en réponse (avec un peu plus d'infos) :

Hey Scripting Guy a fait un article sur le blog Importing CSV into Excel qui pourrait vous apporter des informations utiles. Jouer avec l'objet de données à l'intérieur de powerhell peut vous permettre de faire ce que vous voulez.

Bien que l'article ne mentionne spécifiquement que l'importation des données dans les cellules qui peuvent laisser le format des nombres, il est possible de jouer avec certaines des propriétés et méthodes de ComObject Excel pour forcer les données à entrer les cellules en texte brut à la place (ou forcer le formatage des cellules en texte avant ou après l'importation).

Pour des raisons inconnues, même si vous fournissez explicitement des formats pour toutes les colonnes, Excel l'ignorera si l'extension de fichier est CSV.

Quelques options :

• Créer une requête pour importer les données, comme Wetmelon suggère .
  Inconvénient : il se peut qu'il vous manque des pilotes de base de données CSV sur une machine 64 bits.

• Utilisez le code de Jean , mais incorporez la copie du fichier dans un dossier temporaire et changez l'extension de la copie.
  Inconvénient : Pas de lien avec le fichier original (la sauvegarde écrase la copie) ; vous devrez ensuite supprimer manuellement la copie. Vous pouvez néanmoins enregistrer manuellement sous par-dessus le CSV original.

• Ouvrez le CSV dans le Bloc-notes, Ctrl+A, Ctrl+C, collez dans Excel, puis Données - Texte dans les colonnes, puis c'est l'assistant habituel où vous pouvez mettre toutes les colonnes en Texte en une seule fois. C'est une version différente de l'option précédente, car elle cache également la précieuse extension d'Excel.
  Inconvénient : manuel.

• Avoir une boucle VBA très simple qui lit tout le fichier en mémoire et le met sur la feuille, cellule par cellule.
  Inconvénient : plus lent, moche.

Si je comprends bien la question, elle peut être facilement résolue en utilisant l'option Transposition dans Paste-Special comme décrit ici .