Comment diviser une énorme feuille de calcul Excel CSV en plusieurs fichiers distincts ?

Microsoft Excel est excellent pour 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. Nous avons expliqué comment réduire la taille d’une feuille de calcul Excel ou diviser un gros fichier CSV en plusieurs fichiers, et nous vous proposons plusieurs méthodes.

Pourquoi diviser un gros fichier CSV en plusieurs fichiers ?

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

Plus d’un million de lignes, cela semble phénoménal. Cependant, il est plus facile qu’on ne le pense d’atteindre la limite de lignes, en particulier lors de certaines tâches. Par exemple, si vous faites du marketing par courrier électronique, vous pouvez importer un fichier CSV (Qu’est-ce qu’un fichier CSV ?) contenant des millions d’adresses électroniques. Le seul problème est de savoir comment gérer une feuille de calcul contenant 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 (provenant d’un autre programme) ?

Si vous êtes susceptible d’être confronté à ce problème, consultez les cinq méthodes suivantes pour diviser un fichier CSV ou Excel volumineux en plusieurs fichiers plus petits.

Vous n’avez pas de fichier CSV volumineux à portée de main, mais vous voulez vous amuser à 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. Décomposer des fichiers CSV à l’aide d’un programme

Il existe plusieurs programmes utiles pour diviser les fichiers CSV. Voici deux des meilleurs. Attention cependant, ces programmes ont parfois des problèmes de mémoire, un problème courant pour les programmes de séparation de fichiers CSV.

Free Huge CSV Splitter (Séparateur CSV énorme et gratuit)

Free Huge CSV Splitter est un outil de fractionnement CSV de base. Vous entrez le fichier CSV que vous voulez diviser, le nombre de lignes que vous voulez utiliser, puis vous sélectionnez Fractionner le fichier. Le nombre de lignes détermine le nombre de fichiers de sortie que vous obtiendrez.

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, bien qu’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. Utiliser un fichier batch

Ensuite, créez un fichier de traitement par lots programmable. Vous pouvez utiliser un fichier de traitement par lots pour traiter le fichier CSV en plus petits morceaux, 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 batch avant de l’exécuter. Je vous expliquerai ce que fait chaque commande, et vous pourrez la modifier en fonction de la taille de votre fichier batch et de la sortie souhaitée.

• « SET BFN= » devrait pointer vers le CSV que vous devez 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 saisi vos variables, rendez-vous à l’adresse suivante Fichier &gt ; Enregistrer sous. Choisissez un nom de fichier et sélectionnez Enregistrer. Sélectionnez ensuite le fichier texte que vous venez d’enregistrer et appuyez sur F2 pour le renommer. Remplacer le .txt extension avec .bat et appuyez sur OK lorsque l’avertissement apparaît. Vous pouvez maintenant diviser votre fichier CSV volumineux en fichiers de sortie plus petits.

3. Utiliser un script PowerShell pour découper un fichier CSV

Vous pouvez utiliser des fichiers batch pour un grand nombre de tâches quotidiennes. Mais les scripts PowerShell sont plus rapides, en particulier pour ce type de traitement et de division.

Le script suivant découpe rapidement votre grand fichier 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:\file\location'
$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 dans la première ligne par votre fichier CSV, puis exécutez le script. Le script affiche les petits fichiers CSV dans votre répertoire utilisateur. Par exemple, mes fichiers CSV se trouvent dans C:\Users\Gavin et portent le nom de fichier suivant output_done_1.csv. Vous pouvez changer le nom de la sortie en modifiant le paramètre $OutputFilenamePattern = ‘output_done_’ ligne.

Vous pouvez trouver le script original chez SPJeff.

4. Décomposer un grand CSV à l’aide de Power Pivot

L’avant-dernière solution pour décomposer un gros fichier CSV en petits morceaux ne le décompose pas réellement. Elle vous permet plutôt de charger votre énorme fichier CSV dans Excel et d’utiliser l’outil Power Pivot pour l’ouvrir. En effet, vous pouvez ignorer la limite de lignes d’Excel et gérer le fichier dans le programme.

Pour ce faire, vous créez un lien de données vers le fichier CSV, puis vous utilisez Power Pivot pour en gérer le contenu. Pour une explication complète et un tutoriel, lisez le blog de Jose Barreto qui détaille 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 est tirée de l’article de blog, montrant un total de 2 millions de lignes utilisées dans Excel.

N’oubliez pas que ce processus ne divise pas le fichier CSV en petits morceaux. Cependant, il vous permet de manipuler le fichier CSV dans Excel, ce qui constitue une alternative très pratique. Si vous avez besoin d’autres conseils, découvrez comment utiliser un tableau croisé dynamique pour l’analyse des données.

5. Décomposer de gros CSV en ligne à l’aide de Split CSV

Il existe également des services en ligne qui permettent de diviser votre gros fichier CSV en petits morceaux. L’une de ces options est Split CSV, un séparateur CSV gratuit en ligne.

Split CSV a bien géré le jeu de données COVID-19, en le divisant en morceaux pratiques. Comme avec d’autres outils, vous définissez le nombre de lignes pour chaque fichier et vous laissez l’outil le diviser. Cependant, je n’avais pas de gros fichier CSV sous la main pour le tester et, par conséquent, votre expérience peut varier.

Split CSV comprend également des options premium. Moyennant 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 morceaux faciles à gérer

Vous disposez à présent de cinq solutions pour diviser vos fichiers CSV en petits morceaux, afin de les rendre plus faciles à gérer. Les solutions varient en termes de vitesse et de taille des fichiers CSV qu’elles peuvent gérer, il se peut donc que vous deviez expérimenter pour trouver la solution qui vous convient le mieux. Et le meilleur dans tout ça ? Ces techniques de fractionnement de fichiers CSV fonctionnent sous Windows 10 et Windows 11. Vous pouvez même utiliser l’outil de fractionnement CSV en ligne sur macOS et Linux !