+ Reply to Thread
Results 1 to 9 of 9

Automatically split large CSV files to 2nd, 3rd etc sheets if row count exceeds a limit

  1. #1
    Registered User
    Join Date
    06-26-2014
    Location
    Queensland, Australia
    MS-Off Ver
    2016
    Posts
    35

    Automatically split large CSV files to 2nd, 3rd etc sheets if row count exceeds a limit

    Hi All

    I'm hoping someone can help me with some VBA to overcome the row limitation issue I'm having in excel.

    I am using a web-based system that exports data by automatically by opening a CSV file in Excel. There is no option to save the file first. The files I export are quite often too large and do not fully load due to the row limitation in Excel and need to remain in an Excel format.

    Would anyone know if there's a way to have Excel split a file as it's loading to the a 2nd sheet, 3rd sheet and so on, if the row count exceeds say 1,000,000?

    Any advice or help will be really appreciated.

    Many thanks

  2. #2
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,937

    Re: Automatically split large CSV files to 2nd, 3rd etc sheets if row count exceeds a limi

    Yes, I do have some code somewhere, will have to look it up.
    I used it once and the result was for 4 worksheets (new version Excel around 4 million row )
    But in these cases I read the csv into ms Access and use Excel to read a filtered set
    Last edited by Keebellah; 06-16-2020 at 02:06 AM. Reason: xtra info
    ---
    Hans
    "IT" Always crosses your path!
    May the (vba) code be with you... if it isn't; start debugging!
    If you like my answer, Click the * below to say thank-you

  3. #3
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,937

    Re: Automatically split large CSV files to 2nd, 3rd etc sheets if row count exceeds a limi

    Found the link which is better that the code itself, this helped me
    http://www.cpearson.com/excel/ImportBigFiles.aspx

  4. #4
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow Re: Automatically split large CSV files to 2nd, 3rd etc sheets if row count exceeds a limi


    Hi !

    Quote Originally Posted by darls15 View Post
    I am using a web-based system that exports data by automatically by opening a CSV file in Excel. There is no option to save the file first. The files I export are quite often too large and do not fully load due to the row limitation in Excel and need to remain in an Excel format.

    Would anyone know if there's a way to have Excel split a file as it's loading to the a 2nd sheet, 3rd sheet and so on, if the row count exceeds say 1,000,000?
    Nope, there is no option as Excel is very not a database software …

    But to be opened in Excel even from Web the file is stored locally on the hard disk
    - maybe in the Temp folder of current user, just check it out -
    so a beginner level short VBA procedure can read this text file and split it if necessary …

  5. #5
    Registered User
    Join Date
    06-26-2014
    Location
    Queensland, Australia
    MS-Off Ver
    2016
    Posts
    35

    Re: Automatically split large CSV files to 2nd, 3rd etc sheets if row count exceeds a limi

    Awesome, thanks Keebellah! I'll try this code out today

  6. #6
    Registered User
    Join Date
    06-26-2014
    Location
    Queensland, Australia
    MS-Off Ver
    2016
    Posts
    35

    Re: Automatically split large CSV files to 2nd, 3rd etc sheets if row count exceeds a limi

    Marc, thank you. As suggested, I found all the files I've been attempting to open in temp folder on my hard drive.

  7. #7
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,937

    Re: Automatically split large CSV files to 2nd, 3rd etc sheets if row count exceeds a limi

    Let me know how it goes.
    Happy coding

  8. #8
    Registered User
    Join Date
    06-26-2014
    Location
    Queensland, Australia
    MS-Off Ver
    2016
    Posts
    35

    Re: Automatically split large CSV files to 2nd, 3rd etc sheets if row count exceeds a limi

    Hi Keebellah, well I tried to make this work however Excel just kept on freezing. I won't give up trying to get it to work just yet, however think it's time to move to the database environment. Thanks again, I appreciate your help.

  9. #9
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,937

    Re: Automatically split large CSV files to 2nd, 3rd etc sheets if row count exceeds a limi

    Hi, keep in mind that if you get the message in Excel's caption 'Excel is not responding' this does NOT mean Excel stopped working. It's a glitch in the versions after 2007/2010. You see Excel works differently, it consists of a large number of items that work separately, the GUI is entirely xml oriented while the Table part is something else
    If you let it run (and are patient) the (if something really is wrong then it halts) but it might just finish.
    You could add all the extra things like disabling events and calculations and reset them after completion.
    Yo can also add a doevents in the code after some processing.
    You see Excel workbook is now a self extracting zip file, that's why the files after 2003 are smaller.
    Place an extra extension .xip after the .xlsx or .xlsm and open it with winzip or other extractor and you'll see the internal structure

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 3
    Last Post: 11-05-2019, 03:02 AM
  2. [SOLVED] Split Large Excel XLSX File Into Multiple XLS Files
    By EnormousRA in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 08-23-2016, 08:56 PM
  3. Split Large Excel XLSX File Into Multiple XLS Files
    By EnormousRA in forum Excel General
    Replies: 1
    Last Post: 10-05-2013, 09:18 AM
  4. When a Value exceeds limit
    By mills49 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-21-2013, 11:16 PM
  5. [SOLVED] Split Large Excel file to multiple excel files and possible save the files
    By EnzioL in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-20-2012, 03:28 AM
  6. Split large file into several smaller xls files
    By dirre in forum Excel General
    Replies: 4
    Last Post: 10-27-2011, 08:13 AM
  7. Split large spreadsheet into multiple files
    By jason102 in forum Excel General
    Replies: 8
    Last Post: 07-25-2008, 03:50 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1