Excel excels at so many day-to-day tasks. But every now and then, you find one of its flaws:the size of a spreadsheet. If you need to know how to make an Excel spreadsheet smaller or how to split a large CSV, we've got you covered.
Reaching the spreadsheet row limit is quite a serious task in itself. The current Excel spreadsheet row limit is 1,048,576. That's right, over 1 million rows, and the same number of columns, too.
But reaching that row limit is actually incredibly easy, during certain tasks. If you're marketing via email, you can import a CSV file How to Import Data into Your Excel Spreadsheets the Easy and Easy Way How to Import Data into Your Excel Spreadsheets the Clean and Easy Way Have you ever struggled? import or export data to a spreadsheet? This tutorial will help you master the art of moving data between Microsoft Excel, CSV, HTML, and other file formats. Read More The only problem is how do you actually manage a spreadsheet with so many addresses? Also, what if someone sends you a CSV that is already over the limit (from another program)?
Well, I'm going to show you how to split that huge CSV into easily manageable chunks.
(Don't have a large CSV handy? There are some pretty large CSV datasets available. 15 Massive Online Databases You Should Know About 15 Massive Online Databases You Should Know About The Internet documents everything. Some of that data focus on massive knowledge bases encyclopedias or online databases - the question is do you know which ones are the best read more at data.gov I'm going to use the 260,000 row hospital comparison list, so you can use the same file.)
There are a number of useful CSV splitter programs out there. I'll show you two of the best.
CSV Chunker
CSV Chunker is an open source CSV splitter. It can handle massive files, quickly splitting them into chunks of your choice. It took about 3 seconds to split our Hospital Compare CSV into 106 chunks, each containing 2,500 rows.
CSV Splitter
CSV Splitter is our second tool. It offers the same functionality as CSV Chunker, but with a slightly more elegant design. The CSV splitter took about 4 seconds to split our hospital comparison CSV into 106, again with 2,500 rows each.
Here's a Programmable Batch File How to Write a Simple Batch File (BAT) How to Write a Simple Batch File (BAT) Batch files can automate everyday computer tasks. This is how batch files work and how to write your first batch script. Read more . We can use a batch file to process the CSV into smaller chunks, customizing the file to deliver different chunks.
Open a new text document. Copy and paste the following:
@echo off setlocal ENABLEDELAYEDEXPANSION REM Edite este valor para cambiar el nombre del archivo que necesita división. Incluye la extensión. SET BFN = HCAHPSHospital.csv REM Edite este valor para cambiar el número de líneas por archivo. SET LPF = 2500 REM Edite este valor para cambiar el nombre de cada archivo corto. Le seguirá un número que indica dónde se encuentra en la lista. SET SFN = HosptialSplitFile REM No cambie más allá de esta línea. SET SFX =% BFN: ~ -3% SET / A LineNum = 0 SET / A FileNum = 1 Para / F "delimita ==" %% 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
You will need to configure the batch file before running. I'll tell you what each command does and you can modify it to suit the size of your batch file as well as the required output.
Once you have entered your variables, go to File> Save As. Choose the file name and select Save. Then select your newly saved text file and press F2 to rename it Replace the TXT extension with BAT and press OK when the warning appears. Now you can split your big CSV!
However, be warned that this method takes some time. You're probably better off using one of the aforementioned dividers.
The original script and thread can be found here.
Batch files are useful. You can use them for a wide range of daily tasks. But PowerShell scripts are generally faster, especially for this kind of processing and splitting.
The following script will quickly cut your large CSV into smaller files. Once again, I have used the Hospital Compare CSV for my example. The command took about 3 seconds to execute. Open the Windows PowerShell ISE. If you're not sure how to do this, type powerhell ise in your Start menu search and select the corresponding option. When the PowerShell ISE is open, select the Show Script Arrow, located in the upper right corner (highlighted in red in the image below).
Copy and paste the following command into the script panel at the top of your screen.
$ i = 0; Get-Content C: \ Users \ Gavin \ Downloads \ Hospital_Revised_Flatfiles \ HCAHPSHospital.csv -ReadCount 2500 | % $ i ++; $ _ | Archivo de salida C: \ Users \ Gavin \ Downloads \ Hospital_Revised_Flatfiles \ split \ splitfile_ $ i.csv
First, you need to change the location of your input file. Second, you need to specify how many rows your output files should contain. Lastly, specify the location of the output file. I have left my file paths there to illustrate the situation. Also, your output filename must have the “_$i.csv” suffix matching mine. Otherwise your output files will not be numbered correctly.
When you have edited the file with your own data, press CTRL + S to save your script.
The original script can be found here.
Our final solution is not really to split the large CSV into smaller pieces. You see, José Barreto, a member of the OneDrive team, recently illustrated exactly how you can upload a large CSV file to Excel. In this case, large means over the row limit of 1,048,567. He explains in a tutorial that instead of uploading the file, Excel 2016 can create a data binding to the CSV file..
The data from the original CSV will be available to the user within a Data Model. Barreto created a pivot table using “Up to 8.5 million rows with no problem..” The image above is taken from Barreto's blog post (linked below), showing a total of 2 million rows in Excel. (Learn How to Use an Excel Pivot Table for Data Analysis How to Use an Excel Pivot Table for Data Analysis How to Use an Excel Pivot Table for Data Analysis) The pivot table is one of the most powerful tools in the repertoire of Excel 2013. It is frequently used for extensive data analysis. Follow our step-by-step demo to learn more about it. Read more.
If this is the solution that suits you best, follow José Barreto's detailed tutorial here. Remember, this is not splitting the CSV, but it does mean that you can manipulate your data within certain Excel tools. How to visualize your data analysis with Excel Power Tools. How to visualize your data analysis with Power Tools Excel Excel is killing you with its advanced. Data management functions. Once you've used one of the new tools, you'll want them all. Become a master of your data analysis with power tools! Read more . If you're accessing your data to interact with it directly, it might be worth a try.
We have listed four solutions to break down your bulk CSV files, including two different tools.
Of the four solutions, the batch file is probably the slowest. 5 Reasons Why You Should Use PowerShell Instead of Batches. 5 Reasons Why You Should Use PowerShell Instead of Batches. in some extra features, and kicked it all up several notches. Here are several reasons why you should give it a try. Read more . The PowerShell script is quickly and easily customizable, while the two apps are easy to use out of the box. Finally, you have the option to directly import your data into Excel, by the millions of rows.
Which method do you choose? And do you have any other advice? Please share with us in the comments!
