Hoe een enorme CSV Excel-spreadsheet in aparte bestanden te splitsen

Excel is uitstekend in zoveel dagelijkse taken. Maar af en toe kom je een van de tekortkomingen tegen: de grootte van een spreadsheet. Als u wilt weten hoe u een Excel-spreadsheet kleiner kunt maken of hoe u een grote CSV kunt splitsen, hebben wij het over u.

Het raken van de spreadsheet-rijlimiet is een behoorlijk serieuze taak op zich. De huidige rij met Excel-spreadsheetrijen is 1.048.576. Dat klopt - meer dan 1 miljoen rijen, en hetzelfde aantal kolommen ook.

Maar het raken van die rijlimiet is eigenlijk ongelooflijk gemakkelijk, tijdens bepaalde taken. Als u via e-mail marketingt, kunt u een CSV-bestand importeren Gegevens in uw Excel-spreadsheets importeren de nette en gemakkelijke manier Gegevens in uw Excel-spreadsheets importeren de nette en gemakkelijke manier Hebt u ooit moeite gehad met het importeren of exporteren van gegevens naar een spreadsheet? Deze zelfstudie helpt je de kunst van het verplaatsen van gegevens tussen Microsoft Excel, CSV, HTML en andere bestandsindelingen onder de knie te krijgen. Meer lezen met miljoenen e-mailadressen. Het enige probleem is hoe beheer je een spreadsheet met daarin zoveel adressen? Verder, wat als iemand je een CSV stuurt die al over de limiet is (vanuit een ander programma)?

Welnu, ik ga je laten zien hoe je dat enorme CSV kunt opsplitsen in gemakkelijk hanteerbare brokken.

(Heeft u geen grote CSV bij de hand? Er zijn enkele behoorlijk grote CSV-gegevensreeksen beschikbaar 15 Gigantische online databases die u moet weten over 15 gigantische online databases die u moet kennen Het internet documenteert alles, sommige van die gegevens worden geconcentreerd in enorme knowledgebases. deze online encyclopedieën of databases - de vraag is, weet jij wat de beste is? Lees meer dan op data.gov Ik ga de 260.000 rij ziekenhuisvergelijkingslijst gebruiken, zodat je hetzelfde bestand kunt gebruiken.)

1. Gebruik een programma

Er zijn een aantal nuttige CSV-splitterprogramma's die er zijn. Ik zal je twee van de beste laten zien.

CSV Chunker

CSV Chunker is een open source CSV-splitter. Het kan enorme bestanden aan, snel opsplitsen in brokken naar keuze. Het duurde ongeveer 3 seconden om onze Hospital Compare CSV te splitsen in 106 brokken, elk met 2.500 rijen.

CSV-splitter

CSV Splitter is onze tweede tool. Het biedt dezelfde functionaliteit van CSV Chunker, zij het met een iets sleeker ontwerp. CSV-splitter duurde ongeveer 4 seconden om ons Hospital Compare CSV op te delen in 106, die elk weer 2.500 rijen bevatten.

2. Gebruik een batchbestand

Vervolgens een programmeerbaar batchbestand Een eenvoudig batchbestand (BAT) schrijven Een eenvoudig batchbestand (BAT) schrijven Batchbestanden kunnen dagelijkse computertaken automatiseren. Hier leest u hoe batchbestanden werken en hoe u uw eerste batch-script schrijft. Lees verder . We kunnen een batchbestand gebruiken om de CSV in kleinere delen te verwerken en het bestand aan te passen om verschillende chunks te leveren.

Open een nieuw tekstdocument. Kopieer en plak het volgende:

@echo off setlocal ENABLEDELAYEDEXPANSION REM Bewerk deze waarde om de naam van het bestand te wijzigen dat moet worden opgesplitst. Neem de extensie op. SET BFN = HCAHPSHospital.csv REM Bewerk deze waarde om het aantal regels per bestand te wijzigen. SET LPF = 2500 REM Bewerk deze waarde om de naam van elk kort bestand te wijzigen. Het wordt gevolgd door een nummer dat aangeeft waar het in de lijst staat. SET SFN = HosptialSplitFile REM Verander niet verder dan deze regel. 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 Pauze

U moet het batchbestand configureren voordat u het uitvoert. Ik zal je vertellen wat elke opdracht doet, en je kunt het aanpassen aan de grootte van je batchbestand, evenals aan de vereiste uitvoer.

• “SET BFN =” moet verwijzen naar de CSV die u moet afbreken
• “SET LPF =” is het aantal rijen waarvoor u uw nieuwe bestand wilt beperken
• “SET SFN =” is het nieuwe naamgevingsschema voor uw gesplitste bestanden

Nadat u uw variabelen heeft ingevoerd, gaat u naar Bestand> Opslaan als. Kies bestandsnaam en selecteer Opslaan. Selecteer vervolgens uw zojuist opgeslagen tekstbestand en druk op F2 om het te hernoemen. Vervang de TXT-uitbreiding door BAT en druk op OK wanneer de waarschuwing verschijnt. Nu kunt u uw grote CSV splitsen!

Ik waarschuw u echter dat deze methode enige tijd kost. Je bent waarschijnlijk beter af met het gebruik van een van de bovengenoemde splitters.

Het originele script en de thread zijn hier te vinden.

3. Gebruik een PowerShell-script

Batchbestanden zijn handig. U kunt ze gebruiken voor een breed scala van dagelijkse taken. Maar PowerShell-scripts zijn over het algemeen sneller, vooral voor dit soort verwerking en deling.

Het volgende script snijdt uw grote CSV snel in kleinere bestanden. Nogmaals, ik heb de Hospital Compare CSV gebruikt voor mijn voorbeeld. Het duurde ongeveer 3 seconden voordat de opdracht werd uitgevoerd. Open de Windows PowerShell ISE. Als u niet zeker weet hoe u dit moet doen, typt u powershell ise in uw startmenu zoeken en selecteer de bijbehorende optie. Wanneer de PowerShell ISE is geopend, selecteert u de Toon script pijl, in de rechterbovenhoek (in rood weergegeven in de onderstaande afbeelding).

Kopieer en plak de volgende opdracht in het scriptpaneel, bovenaan je scherm.

$ I = 0; Get-Content C: \ Users \ Gavin \ Downloads \ Hospital_Revised_Flatfiles \ HCAHPSHospital.csv -ReadCount 2500 | % $ I ++; $ _ | Out-File C: \ Users \ Gavin \ Downloads \ Hospital_Revised_Flatfiles \ split \ splitfile_ $ i.csv

Eerst moet u de locatie van uw invoerbestand wijzigen. Ten tweede moet u opgeven hoeveel rijen uw uitvoerbestanden moeten bevatten. Geef ten slotte de locatie van uw uitvoerbestand op. Ik heb mijn bestandspaden daar achtergelaten om de situatie te illustreren. Verder, uw uitvoerbestandsnaam moet hebben de “_ $ I.csv”achtervoegsel dat bij de mijne past Anders zullen uw uitvoerbestanden niet correct nummeren.

Wanneer u het bestand met uw eigen gegevens hebt bewerkt, drukt u op CTRL + S om je script op te slaan.

Het originele script is hier te vinden.

4. Laad het in een datamodel

Onze uiteindelijke oplossing is niet echt het splitsen van de grote CSV in kleinere delen. U ziet, Jose Barreto, lid van het OneDrive-team, heeft recentelijk precies geïllustreerd hoe u een groot CSV-bestand in Excel kunt laden. In dit geval betekent groot over de rij van 1.048.567 rijen. Hij legt in een tutorial uit dat Excel 2016, in plaats van het bestand daadwerkelijk te laden, een datalink kan maken naar het CSV-bestand.

De gegevens van de oorspronkelijke CSV zijn binnen een gegevensmodel beschikbaar voor de gebruiker. Barreto heeft een draaitabel gemaakt met “tot 8,5 miljoen rijen zonder enig probleem.” De bovenstaande afbeelding is overgenomen van Barreto's blogpost (hieronder gelinkt), met een totaal van 2 miljoen rijen binnen Excel. (Meer informatie over het gebruik van een draaitabel voor gegevensanalyse. Een Excel-draaitabel gebruiken voor gegevensanalyse Een Excel-draaitabel gebruiken voor gegevensanalyse De draaitabel is een van de krachtigste hulpmiddelen in het Excel 2013-repertoire. vaak gebruikt voor grote data-analyse Volg onze stap-voor-stap demonstratie om er alles over te leren Lees meer.)

Als dit de oplossing is die bij je past, volg dan de gedetailleerde tutorial van Jose Barreto hier. Vergeet niet dat dit niet het CSV splitst, maar het betekent wel dat u uw gegevens binnen bepaalde Excel-tools kunt manipuleren. Hoe uw gegevensanalyse visualiseren met Excel Power Tools Hoe uw data-analyse te visualiseren met Excel Power Tools Excel vermoord het met zijn geavanceerde gegevensbeheerfuncties. Zodra u een van de nieuwe hulpmiddelen hebt gebruikt, wilt u ze allemaal. Word een meester in uw gegevensanalyse met elektrische gereedschappen! Lees verder . Als u uw gegevens gebruikt om rechtstreeks ermee te communiceren, kan dit het proberen waard zijn.

Break It Down!

We hebben vier oplossingen voor het afbreken van uw grote CSV-bestanden vermeld, waaronder twee verschillende hulpmiddelen.

Van de vier oplossingen is het batchbestand waarschijnlijk het langzaamste. 5 Redenen die u moet gebruiken PowerShell in plaats van Batch-scripting 5 Redenen die u moet gebruiken PowerShell in plaats van Batch-scripting PowerShell is wat u zou krijgen als u de opdrachtprompt met Batch-scripting zou overschrijden, gooide in sommige extra functies, en schopte het allemaal verschillende inkepingen. Hier zijn verschillende redenen waarom je het zou moeten proberen. Lees verder . Het PowerShell-script is snel en gemakkelijk aan te passen, terwijl de twee applicaties eenvoudig te gebruiken zijn. Ten slotte hebt u de mogelijkheid om uw gegevens direct in miljoenen rijen te importeren in Excel.

Welke methode kiest u? En heb je nog andere tips? Gelieve te delen met ons in de commentaren!

Beeldcredits: lucadp / Depositphotos

Ontdek meer over: Microsoft Excel, Spreadsheet.