Excel est excellent dans de nombreuses tâches quotidiennes. Mais de temps en temps, vous rencontrez l'un de ses défauts: la taille d'une feuille de calcul. Si vous avez besoin de savoir comment réduire la taille d'une feuille de calcul Excel ou comment diviser un fichier CSV de grande taille, nous vous avons couvert.
Atteindre la limite de la ligne de feuille de calcul est une tâche assez sérieuse en soi. La limite actuelle de la feuille de calcul Excel est de 1 048 576. C'est vrai - plus d'un million de lignes et la même quantité de colonnes.
Mais atteindre cette limite de ligne est en réalité incroyablement facile, au cours de certaines tâches. Si vous faites du marketing par e-mail, vous pouvez importer un fichier CSV Comment importer des données dans vos feuilles de calcul Excel La manière simple et facile Comment importer des données dans vos feuilles de calcul Excel La façon simple et facile Avez-vous déjà lutté avec l'importation ou l'exportation de données? une feuille de calcul? Ce tutoriel vous aidera à maîtriser l'art de déplacer des données entre Microsoft Excel, CSV, HTML et d'autres formats de fichiers. Lire la suite contenant des millions d'adresses email. Le seul problème est de savoir comment gérer réellement une feuille de calcul contenant autant d'adresses. De plus, que se passe-t-il si quelqu'un vous envoie un CSV qui dépasse déjà la limite (d'un autre programme)?
Eh bien, je vais vous montrer comment décomposer cet énorme CSV en morceaux facilement gérables.
(Il n'y a pas de gros CSV à portée de main Il existe de très gros jeux de données CSV disponibles 15 Bases de données massives en ligne 15 bases de données en ligne massives que vous devriez savoir: Internet documente tout, certaines de ces données sont concentrées dans d'énormes bases de connaissances. les encyclopédies en ligne ou les bases de données - la question est, savez-vous sur les meilleurs? Lire la suite sur data.gov.Je vais utiliser la liste de 260.000 hôpitaux Hospital Compare, de sorte que vous pouvez utiliser le même fichier.)
1. Utilisez un programme
Il existe un certain nombre de programmes de séparation CSV utiles. Je vais vous montrer deux des meilleurs.
CSV Chunker
CSV Chunker est un séparateur CSV open source. Il peut gérer des fichiers volumineux, les divisant rapidement en morceaux de votre choix. Il a fallu environ 3 secondes pour diviser notre CSV de l'hôpital en 106 blocs contenant chacun 2 500 lignes.
Séparateur CSV
CSV Splitter est notre deuxième outil. Il offre beaucoup les mêmes fonctionnalités de CSV Chunker, mais avec un design légèrement plus élégant. CSV Splitter a pris environ 4 secondes pour diviser notre CSV d'hôpital en 106, contenant à nouveau 2 500 lignes chacun.
2. Utilisez un fichier batch
Ensuite, un fichier batch programmable Comment écrire un fichier BAT (BAT) Comment écrire un fichier BAT (BAT) Les fichiers batch peuvent automatiser les tâches quotidiennes de l'ordinateur. Nous allons vous montrer comment fonctionnent les fichiers batch et vous apprendre les bases. À la fin de cet article, vous aurez écrit votre premier fichier batch. Lire la suite . Nous pouvons utiliser un fichier batch pour traiter le CSV en plus petits morceaux, en personnalisant le fichier pour livrer différents morceaux.
Ouvrez un nouveau document texte. Copiez et collez ce qui suit:
@echo off setlocal ENABLEDELAYEDEXPANSION REM Modifiez cette valeur pour changer le nom du fichier à fractionner. Incluez l'extension. SET BFN = HCAHPSHospital.csv REM Modifiez cette valeur pour modifier le nombre de lignes par fichier. SET LPF = 2500 REM Modifiez cette valeur pour changer le nom de chaque fichier court. Il sera suivi d'un nombre indiquant où il se trouve dans la liste. SET SFN = HosptialSplitFile REM Ne changez pas au-delà de cette ligne. SET SFX =% BFN: ~ -3% SET / A LineNum = 0 SET / A FileNum = 1 Pour / F "delims ==" %% l dans (% BFN%) Do (SET / A LineNum + = 1 echo %% l >>% SFN%! FileNum!.% SFX% si! LineNum! EQU! LPF! (SET / A LineNum = 0 SET / A N ° de fichier + = 1)) Pause endlocal
Vous devez configurer le fichier de commandes avant de l'exécuter. Je vais vous dire ce que chaque commande fait, et vous pouvez le modifier en fonction de la taille de votre fichier batch, ainsi que la sortie requise.
- "SET BFN =" devrait pointer vers le CSV dont vous avez besoin pour tomber en panne
- " SET LPF =" est le nombre de lignes que vous souhaitez limiter à votre nouveau fichier
- "SET SFN =" est le nouveau schéma de nommage pour vos fichiers fractionnés
Une fois que vous avez entré vos variables, dirigez-vous vers Fichier> Enregistrer sous. Choisissez le nom de fichier et sélectionnez Enregistrer. Ensuite, sélectionnez votre fichier texte nouvellement enregistré et appuyez sur F2 pour le renommer. Remplacez l'extension TXT par BAT et appuyez sur OK lorsque l'avertissement apparaît. Maintenant, vous pouvez diviser votre grand fichier CSV!
Je vous préviens cependant, cette méthode prend du temps. Vous êtes probablement mieux d'utiliser l'un des séparateurs mentionnés ci-dessus.
Le script et le fil d'origine peuvent être trouvés ici.
3. Utilisez un script PowerShell
Les fichiers batch sont pratiques. Vous pouvez les utiliser pour un large éventail de tâches quotidiennes. Mais les scripts PowerShell sont plus rapides, en particulier pour ce type de traitement et de division.
Le script suivant va rapidement couper votre gros fichier CSV en fichiers plus petits. Encore une fois, j'ai utilisé le CSV de l'hôpital pour mon exemple. Il a fallu environ 3 secondes pour que la commande s'exécute. Ouvrez l'ISE Windows PowerShell. Si vous ne savez pas comment procéder, tapez powershell ise dans votre recherche dans le menu Démarrer et sélectionnez l'option correspondante. Lorsque le PowerShell ISE est ouvert, sélectionnez la flèche Afficher le script, située dans le coin supérieur droit (indiquée en rouge dans l'image ci-dessous).
Copiez et collez la commande suivante dans le panneau de script, en haut de votre écran.
$ i = 0; Obtenir-Contenu C: \ Utilisateurs \ Gavin \ Téléchargements \ Hospital_Revised_Flatfiles \ HCAHPSHospital.csv -ReadCount 2500 | % {$ i ++; $ _ | Fichier externe C: \ Utilisateurs \ Gavin \ Téléchargements \ Hospital_Revised_Flatfiles \ split \ splitfile_ $ i.csv} D'abord, vous devez changer votre emplacement de fichier d'entrée. Deuxièmement, vous devez spécifier le nombre de lignes que vos fichiers de sortie doivent contenir. Enfin, spécifiez l'emplacement de votre fichier de sortie. J'ai laissé mes chemins de fichier là pour illustrer la situation. En outre, votre nom de fichier de sortie doit avoir le suffixe "_ $ i.csv" correspondant au mien. Sinon, vos fichiers de sortie ne seront pas numérotés correctement.
Lorsque vous avez modifié le fichier avec vos propres données, appuyez sur CTRL + S pour enregistrer votre script.
Le script original peut être trouvé ici.
4. Chargez-le dans un modèle de données
Notre solution finale ne divise pas réellement le grand CSV en plus petites parties. Vous voyez, Jose Barreto, un membre de l'équipe OneDrive, a récemment illustré exactement comment vous pouvez charger un gros fichier CSV dans Excel. Dans ce cas, un grand moyen dépasse la limite de 1 048 567 lignes. Il explique dans un didacticiel qu'au lieu de charger le fichier, Excel 2016 peut créer un lien de données vers le fichier CSV.
Les données du fichier CSV d'origine seront mises à la disposition de l'utilisateur dans un modèle de données. Barreto a créé une table pivotante en utilisant «jusqu'à 8, 5 millions de lignes sans aucun problème». L'image ci-dessus est tirée du blog de Barreto (ci-dessous), montrant un total de 2 millions de lignes dans Excel. (Apprendre à utiliser un tableau croisé dynamique pour l'analyse des données Comment utiliser un tableau croisé dynamique Excel pour l'analyse des données Comment utiliser un tableau croisé dynamique Excel pour l'analyse des données Le tableau croisé dynamique est l'un des outils les plus puissants du répertoire Excel 2013. fréquemment utilisé pour l'analyse de données volumineuses, suivez notre démonstration étape par étape pour en savoir plus à ce sujet.
Si c'est la solution qui vous convient, suivez le tutoriel détaillé de José Barreto ici. Rappelez-vous, ce n'est pas diviser le CSV, mais cela signifie que vous pouvez manipuler vos données dans certains outils Excel Comment visualiser votre analyse de données avec les outils électriques d'Excel Comment visualiser votre analyse de données avec Excel Power Tools Excel le tue avec son avancé caractéristiques de gestion de données. Une fois que vous avez utilisé l'un des nouveaux outils, vous les voulez tous. Devenez un maître de votre analyse de données avec des outils électriques! Lire la suite . Si vous accédez à vos données pour interagir directement avec lui, cela pourrait valoir la peine d'essayer.
Brisez-le!
Nous avons listé quatre solutions pour décomposer vos fichiers CSV massifs, y compris deux outils différents.
Parmi les quatre solutions, le fichier batch est probablement le plus lent. 5 raisons pour lesquelles vous devez utiliser PowerShell au lieu de script par lots 5 raisons pour lesquelles vous devez utiliser PowerShell PowerShell plutôt que des scripts batch PowerShell est ce que vous obtiendriez si vous traversiez l'invite de commandes avec Batch Scripting. dans certaines fonctionnalités supplémentaires, et a lancé tout cela plusieurs crans. Voici plusieurs raisons pour lesquelles vous devriez essayer. Lire la suite . Le script PowerShell est rapide et facilement personnalisable, tandis que les deux applications sont simples à utiliser dès la sortie de la boîte. Enfin, vous avez la possibilité d'importer directement vos données dans Excel, par millions de lignes.
Quelle méthode choisissez-vous? Et avez-vous d'autres conseils? S'il vous plaît partager avec nous dans les commentaires!
Crédit d'image: lucadp / Depositphotos