2011-07-05 00:01:28 +0000 2011-07-05 00:01:28 +0000
69
69

Comment puis-je configurer Excel pour qu'il importe toujours toutes les colonnes des fichiers CSV sous forme de texte ?

Bien que j'essaie de l'éviter, je dois parfois ouvrir un fichier CSV dans Excel. Lorsque je le fais, il formate des colonnes contenant des chiffres, ce qui les rend inutiles pour mes besoins. Pour autant que je sache, la seule façon d'éviter cela à l'importation est de renommer le fichier pour que l'extension ne soit pas .csv et d'utiliser l'assistant d'importation pour spécifier le format de chaque colonne individuellement. Pour les fichiers de 50 à 60 colonnes, ce n'est pas pratique.

Comme chaque réponse à cette question souvent posée sur Internet suggère soit un moyen de reconvertir les nombres formatés une fois le fichier ouvert (ce qui ne me convient pas - je veux résoudre le problème général, et non quelques cas particuliers), soit de sélectionner manuellement le type de format de chaque colonne (ce que je ne veux pas faire), je cherche un moyen de définir une préférence ou un style global de telle sorte que toutes les colonnes de tous les fichiers CSV ouverts soient toujours formatées en texte. Je sais qu'il faut aussi “armer” les chiffres avec des guillemets, mais les fichiers que je reçois ne sont pas comme ça et j'espérais éviter de devoir prétraiter les fichiers pour qu'Excel ne les bousille pas.

Y a-t-il un moyen de faire spécifiquement cela : Toujours formater toutes les colonnes des fichiers CSV ouverts sous forme de texte, sans sélectionner manuellement chaque colonne à chaque fois lors de l'importation ?

J'utilise Excel 2003, mais je vais prendre les réponses pour 2007 si c'est ce que vous savez.

Réponses (9)

73
73
73
2013-01-04 01:38:39 +0000

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)

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

7
7
7
2011-07-06 19:13:20 +0000

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.

4
4
4
2013-01-03 22:42:44 +0000

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

2
2
2
2015-12-02 14:21:59 +0000

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 !!!!

1
1
1
2015-12-04 09:21:54 +0000

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
0
0
0
2011-07-05 01:20:26 +0000

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.

0
0
0
2011-07-05 17:29:47 +0000

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).

0
0
0
2011-07-06 19:45:53 +0000

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.

-2
-2
-2
2013-03-12 21:37:27 +0000

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