Excel is excellent at so many day-to-day tasks. But every now and then, you encounter one of its shortcomings: 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 have you covered.
Hitting the spreadsheet row limit is a pretty serious task in and of itself. The current Excel spreadsheet row limit is 1,048,576. That's right — over 1 million rows, and the same amount of columns, too.
But hitting that row limit is actually incredibly easy, during certain tasks. If you're marketing via email, you might import a CSV file How to Import Data Into Your Excel Spreadsheets the Neat & Easy Way How to Import Data Into Your Excel Spreadsheets the Neat & Easy Way Have you ever struggled with importing or exporting data into a spreadsheet? This tutorial will help you master the art of moving data between Microsoft Excel, CSV, HTML, and other file formats. Read More containing millions of email addresses. The only problem is how do you actually manage a spreadsheet with that many addresses in? Furthermore, 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 break down that enormous CSV into easily manageable chunks.
There are a number of useful CSV splitter programs out there. I'll show you two of the best.
CSV Chunker is an open source CSV splitter. It can handle massive files, rapidly splitting them into chunks of your choosing. It took about 3 seconds to split our Hospital Compare CSV into 106 chunks, containing 2,500 rows each.
CSV Splitter is our second tool. It offers much the same functionality of CSV Chunker, albeit with a slightly sleeker design. CSV Splitter took about 4 seconds to split our Hospital Compare CSV into 106, again containing 2,500 rows each.
Next up, a programmable batch file How to Write a Simple Batch (BAT) File How to Write a Simple Batch (BAT) File Batch files can automate everyday computer tasks. Here's 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.
REM Edit this value to change the name of the file that needs splitting. Include the extension.
REM Edit this value to change the number of lines per file.
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.
REM Do not change beyond this line.
You'll need to configure the batch file before running. I'll tell you what each command does, and you can alter it to suit the size of your batch file, as well as the required output.
Once you've entered your variables, head to File > Save As. Choose filename, 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 large CSV!
I warn you though, this method does take some time. You're probably better off using one of the aforementioned splitters.
The original script and thread can be found here.
Batch files are handy. You can use them for a wide range of day to day tasks. But PowerShell scripts are by-and-large faster, especially for this type of processing and division.
The following script will quickly cut your large CSV into smaller files. Again, I've used the Hospital Compare CSV for my example. It took about 3 seconds for the command to run. Open the Windows PowerShell ISE. If you're unsure on how to do this, type powershell ise into your Start menu search and select the corresponding option. When the PowerShell ISE is open, select the Show Script arrow, located in the top right corner (outlined in red in the below image).
Copy and paste the following command into the script panel, at the top of your screen.
First, you need to change your input file location. Second, you need to specify how many rows your output files should contain. Lastly, specify your output file location. I've left my file paths in there to illustrate the situation. Furthermore, your output filename must have the "_$i.csv"suffix matching mine. Otherwise, your output files won't number properly.
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 isn't actually splitting the large CSV into smaller parts. You see, Jose Barreto, a member of the OneDrive team, recently illustrated exactly how you can load a large CSV file in Excel. In this case, large means over the 1,048,567 row limit. He explains in a tutorial that instead of actually loading the file, Excel 2016 can create a data link to the CSV file.
If this is the solution that suits you, follow Jose Barreto's detailed tutorial right here. Remember, this isn't splitting the CSV, but it does mean you can manipulate your data within certain Excel tools How to Visualize Your Data Analysis with Excel's Power Tools How to Visualize Your Data Analysis with Excel's Power Tools Excel is killing it with its advanced data management features. Once you have used one of the new tools, you will want them all. Become a master of your data analysis with power tools! Read More . If accessing your data to interact with it directly, this could be worth a try.
We've listed four solutions to breaking down your massive CSV files, including two different tools.
Of the four solutions, the batch file is probably the slowest. The PowerShell script is fast and easily customizable, while the two applications are simple to use out of the box. Finally, you have the option of directly importing your data into Excel, by the millions of rows.
For more Excel tricks, check out how to separate first and last names in Excel How to Separate First and Last Name in Excel How to Separate First and Last Name in Excel Working with names in Microsoft Excel? Here's a helpful tutorial that shows you how to separate first and last names in Excel. Read More .
Thank you for the help. The Windows PowerShell ISE option worked great!
Click on the Data tab.
In the Get External Data group, click From Text.
Double-click the text file that you want to import in the Import Text File dialogue box.
Click Import. The Text Import Wizard will begin.
Uncheck Tab and select Comma.
Select to save the information in either an existing worksheet or a new worksheet.
Note: The contacts should now be in Excel, split into columns.
On the Excel toolbar, select File > Save As. A dialog box pops up.
Type a new name for the file in the File Name text field.
Under Save As Type, choose Excel Workbook (*xlsx), Excel 97-2003 Workbook (*.xls), or CSV (Comma Delimited)(*.csv) and click Save.
Click OK for the message "The selected file type does not support workbooks with multiple sheets".
Click Yes for the message that the file "may contain features that are not compatible with CSV".
thanks a lot. I could use your bat file method successfully.
This is really helpful, thanks! One question: can the powershell technique create a file in UTF-8 encoding and Unix line feed instead of windows CR LF?
Loved the PowerShell example, saved me so much time. Now to work out how to merge the files back again once they have been analysed. Stephan Solomonidis, kudos to you.
CSV files have no limit of rows you can add to them. Excel won't hold more that the 1 million lines of data if you import a CSV file having more lines. Excel will actually ask you whether you want to proceed when importing more than 1 million data rows.
That's not the point. The chap here is trying to help us split the files easily for our own reasons. Your rant is a technicality that nobody needs to know about. Go back in the basement!
I'm not sure that you understood this article. A re-read may be in order. The whole purpose was to break up the desired file into files that are less than the limit, before running Excel.
God bless you for this entry.
I'm working on 10 million records email addresses..I have to verify it and remove invalid emails, bounces, spam trap... then create 200 CSV files. Which is the best software for clean huge databases? I need more tools? I need help please.
Hi Diana, I have the same problem. I'm working on 10 million records email addresses..I have to verify it and remove invalid emails then create 200 CSV files. Which is the best software for clean huge databases? You can help me please?
Thank you! You saved me a lot of misery, since I was just tasked with the job of editing a mailing list database of over 9 million! It wouldn't fit into any program I have.
It may not be appropriate, but why not use Access? Better suited for large number records than Excel.
I actually ran into this issue a few days ago, and I used the same tool. One thing that also helped, in not needing to have it split it up into as many separate files, is that Excel 2007 can handle 2^20 rows (1,048,576) as opposed to 2003's 2^16 (65,536).
Also, once you have all these separate files, you can put them all in one XLS file by adding them each as separate worksheets to the same file (the row limit is per worksheet).