Pinterest Stumbleupon Whatsapp

csvHeadI recently started working with  a company that does HUGE mailing lists. I deal with a lot of records everyday but this really is a lot of data.

They have an opt-in mailing list that they blast to once or twice a month. When I say huge I am talking about between 7 and 8 million email addresses.

Now if you have ever tried to open a file like this in excel you would see something that looks like this:

split excel spreadsheet into seperate files

The reason you get this error is simple.

Excel can only handle 65,536 lines per sheet. It cannot display the rest of it and will truncate it. This is not good if you need to cut your list into smaller pieces. In my case I need to have less than 1 million records in my csv file to be able to import it into my mailing list program.

I tried using Base from Open Office with the same results. Now I could open this as a text file and chop it up manually or import it into an access database and try and manipulate it there as well. But hey let’s check out a free and EASY alternative to split an Excel spreadsheet into seperate files. It is called FXFisherman’s CSV Splitter v1.1 by Sopheap LY.

We came across it here but I was able to trace it’s origins to the actual post on a forum it was created for and that is here. The original purpose of the app was to work with stock data in smaller chunks from a huge dump.

But hey I guess it will work on ANY csv file. Simply download the application and run it – no install required.

That will bring you to this screen:

split excel workbook into seperate files

Now this program has two simple options : you can split the csv file by the number of lines and specify the maximum number of files. Simply type in the number of lines you want to split each file into, and/or make the maximum pieces that you would want to see.

Next browse for your file by hitting the Browse file button and then hit the Split Now! Button.

When the program is complete you should see something that looks like this:

split excel workbook into seperate files

This tells me that my file of approximately 100,000 records was split successfully into a few even files. If they were more than 25 pieces with 50,000 lines in each it would have changed the algorithm to make each one slightly larger. Either which way this saved the day! I was able to import almost 4 million addresses at the end of the day into smaller chunks! (Thank god for interns!)

Do you know of a better, easier and / or faster way to accomplish the same thing?

Leave a Reply

Your email address will not be published. Required fields are marked *

  1. shams
    December 14, 2016 at 10:55 am

    That's not true anymore:

    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.

    • ee
      March 5, 2017 at 3:45 am

      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!

  2. Ramesh
    October 24, 2016 at 4:00 am

    Awesome Worked like a charm

  3. Ramesh
    October 24, 2016 at 4:00 am

    Awesome Worked like a charm

  4. Juan Alvarez
    October 4, 2016 at 12:40 am

    God bless you for this entry.

  5. dima
    April 1, 2016 at 11:32 am

    Thank u so much

  6. Anonymous
    September 3, 2015 at 8:39 am

    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.

  7. Anonymous
    September 2, 2015 at 6:05 pm

    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?

  8. Diana Ward
    May 24, 2015 at 8:08 pm

    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.

  9. Manish
    February 21, 2015 at 12:52 pm

    Is there any for iOS (Mac)

  10. Mark Murphy
    September 13, 2009 at 10:16 am

    Great time saver!!

    Thanks :)

  11. Colm
    August 17, 2009 at 7:58 pm

    Are there legitimate uses of CSV files containing millions of mail addresses? Maybe Excel is trying to tell you something! ;)

  12. CYMR0
    August 16, 2009 at 9:38 am

    It may not be appropriate, but why not use Access? Better suited for large number records than Excel.

  13. James Rintamaki
    August 14, 2009 at 9:59 am

    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).