Comment diviser une énorme feuille de calcul CSV Excel en fichiers séparés

29 octobre 2020 Hibou Gourou

Microsoft Excel est excellent pour de nombreuses tâches quotidiennes. Mais de temps en temps, vous rencontrez l'une de ses lacunes: la taille d'une feuille de calcul. Lisez la suite pour savoir comment réduire la taille d'une feuille de calcul Excel ou diviser un gros fichier CSV en plusieurs fichiers.

Pourquoi diviser un grand CSV en plusieurs fichiers?

Vous pensez peut-être: «Pourquoi aurais-je besoin de diviser un gros fichier Excel en plusieurs fichiers plus petits?» C'est une question intéressante, d'autant plus qu'Excel a une limite de lignes de feuille de calcul de 1 048 576.

Plus d'un million de lignes semble phénoménal. Cependant, atteindre la limite de lignes est plus facile que vous ne le pensez, en particulier lors de certaines tâches.

Par exemple, si vous faites du marketing par e-mail, vous pouvez importer un fichier CSV contenant des millions d'adresses e-mail. Le seul problème est, comment gérez-vous une feuille de calcul avec autant d'adresses? De plus, que se passe-t-il si quelqu'un vous envoie un fichier CSV qui dépasse déjà la limite (d'un autre programme)?

Si cela ressemble à un problème auquel vous êtes susceptible de faire face, consultez les cinq façons suivantes de diviser un gros fichier CSV ou Excel en plusieurs fichiers plus petits.

Vous n'avez pas de gros fichier CSV à portée de main, mais vous souhaitez jouer à la maison? J'utilise l' ensemble de données de recherche ouverte COVID-19 dans les exemples, que vous pouvez également télécharger et utiliser.

1. Diviser les fichiers CSV à l'aide d'un programme

Il existe plusieurs programmes de fractionnement CSV utiles. Voici deux des meilleurs. Attention cependant, comme ces programmes fonctionnent, ils rencontrent parfois des problèmes de mémoire, ce qui est un problème courant pour les programmes de fractionnement CSV.

Énorme diviseur CSV gratuit

Free Huge CSV Splitter est un outil de fractionnement CSV de base. Vous entrez le fichier CSV que vous souhaitez fractionner, le nombre de lignes que vous souhaitez utiliser, puis sélectionnez Diviser le fichier . Le nombre de lignes détermine le nombre de fichiers de sortie avec lesquels vous vous retrouvez.

Séparateur CSV

CSV Splitter est le deuxième outil. Il offre à peu près les mêmes fonctionnalités que Free Huge CSV Splitter, mais avec un design légèrement plus élégant. Il divise rapidement votre CSV en petits morceaux, vous permettant de déterminer le nombre de lignes que vous souhaitez utiliser.

2. Utilisez un fichier batch

Ensuite, créez un fichier batch programmable . Vous pouvez utiliser un fichier de commandes pour traiter le CSV en morceaux plus petits, en personnalisant le fichier pour fournir différents morceaux.

Ouvrez un nouveau document texte, puis copiez et collez ce qui suit:

 @echo off
 setlocal ENABLEDELAYEDEXPANSION
 REM Edit this value to change the name of the file that needs splitting. Include the extension.
 SET BFN=HCAHPSHospital.csv
 REM Edit this value to change the number of lines per file.
 SET LPF=2500
 REM Edit this value to change the name of each short file. It will be followed by a number indicating where it is in the list.
 SET SFN=HosptialSplitFile
 REM Do not change beyond this line.
 SET SFX=%BFN:~-3%
 SET /A LineNum=0
 SET /A FileNum=1
 For /F "delims==" %%l in (%BFN%) Do (
 SET /A LineNum+=1
 echo %%l >> %SFN%!FileNum!.%SFX%
 if !LineNum! EQU !LPF! (
 SET /A LineNum=0
 SET /A FileNum+=1
 )
 )
 endlocal
 Pause

Vous devrez configurer le fichier de commandes avant de l'exécuter. Je vais vous dire ce que fait chaque commande et vous pouvez la modifier en fonction de la taille de votre fichier de commandes, ainsi que de la sortie requise.

"SET BFN =" doit pointer vers le CSV dont vous avez besoin pour décomposer
" SET LPF =" est le nombre de lignes auquel vous souhaitez limiter votre nouveau fichier
"SET SFN =" est le nouveau schéma de dénomination de vos fichiers fractionnés

Une fois que vous avez entré vos variables, dirigez-vous vers Fichier> Enregistrer sous . Choisissez un 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 gros fichier CSV en fichiers de sortie plus petits.

3. Utilisez un script PowerShell pour diviser un fichier CSV

Vous pouvez utiliser des fichiers de commandes 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 coupe rapidement votre gros CSV en fichiers plus petits.

Tout d'abord, appuyez sur CTRL + X pour ouvrir le menu d'alimentation de Windows, puis sélectionnez PowerShell . Si PowerShell n'est pas une option, saisissez powershell dans la barre de recherche du menu Démarrer et sélectionnez la meilleure correspondance.

Maintenant, copiez et collez le script suivant:

 $InputFilename = Get-Content 'C:filelocation'
 $OutputFilenamePattern = 'output_done_'
 $LineLimit = 50000
 $line = 0
 $i = 0
 $file = 0
 $start = 0
 while ($line -le $InputFilename.Length) {
 if ($i -eq $LineLimit -Or $line -eq $InputFilename.Length) {
 $file++
 $Filename = "$OutputFilenamePattern$file.csv"
 $InputFilename[$start..($line-1)] | Out-File $Filename -Force
 $start = $line;
 $i = 0
 Write-Host "$Filename"
 }
 $i++;
 $line++
 }

Remplacez l'emplacement du fichier sur la première ligne par votre fichier CSV, puis exécutez le script. Le script génère les plus petits fichiers CSV de votre répertoire utilisateur. Par exemple, mes fichiers CSV se trouvent dans C: Users Gavin avec le nom de fichier output_done_1.csv . Vous pouvez changer le nom de la sortie en modifiant la ligne $ OutputFilenamePattern = 'output_done_' .

Vous pouvez trouver le script original sur SPJeff .

4. Divisez un grand CSV à l'aide de Power Pivot

Votre avant-dernière solution pour diviser un gros fichier CSV en petits bits ne le décompose pas réellement. Au lieu de cela, il vous permet de charger votre fichier CSV massif dans Excel et d'utiliser l'outil Power Pivot pour l'ouvrir. C'est vrai; vous pouvez ignorer efficacement la limite de ligne Excel et gérer le fichier dans le programme.

Pour ce faire, créez une liaison de données vers le fichier CSV, puis utilisez Power Pivot pour gérer le contenu. Pour une explication complète et un tutoriel, lisez le blog de Jose Barreto détaillant le processus.

En bref, Barreto crée un tableau croisé dynamique en utilisant «jusqu'à 8,5 millions de lignes sans aucun problème». L'image ci-dessus provient du billet de blog, montrant un total de 2 millions de lignes utilisées dans Excel.

N'oubliez pas que ce processus ne divise pas le CSV en petits morceaux. Cependant, cela signifie que vous pouvez manipuler le CSV dans Excel, ce qui est une alternative très pratique. Si vous avez besoin de plus de conseils, découvrez comment utiliser un tableau croisé dynamique pour l'analyse des données .

5. Diviser le grand CSV en ligne à l'aide de Split CSV

Il existe également des services en ligne qui divisent votre gros fichier CSV en petits morceaux. Une de ces options est Split CSV , un séparateur CSV en ligne gratuit.

Split CSV a bien géré l'ensemble de données COVID-19, en le divisant en morceaux pratiques. Comme avec les autres outils, vous définissez le nombre de lignes pour chaque fichier et laissez-le diviser. Cependant, je n'avais pas de gros fichier CSV pour tester et, en tant que tel, votre expérience peut varier.

Split CSV comprend également des options premium. Pour un abonnement, vous pouvez utiliser un délimiteur personnalisé , choisir une sélection de types de fichiers de sortie, supprimer certains caractères des fichiers de sortie et supprimer les lignes en double.

Décomposez vos fichiers CSV en blocs facilement gérables

Vous disposez désormais de cinq solutions pour diviser vos fichiers CSV en petits morceaux, ce qui les rend faciles à gérer. Les solutions varient en termes de vitesse et de taille des fichiers CSV qu'elles peuvent gérer, vous devrez donc peut-être expérimenter pour trouver la solution qui vous convient le mieux.

Crédit d'image: lucadp / Depositphotos