Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Importing large text files into Excel.

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I am trying to import a text file that is over 300,000 lines long. However, the Excel rows stop at 65,536 and import wizard will not let you select which line of the import file you would like to start with. Please post all help on importing text files into Excel.

    Thank you

  2. #2
    MrExcel MVP Damon Ostrander's Avatar
    Join Date
    Feb 2002
    Location
    Denver, Colorado USA
    Posts
    4,239
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Keith,

    As always, there are a number of options.

    If this is a one-time thing, then the best way to deal with it is to divide your input file into bite-size chunks, or extract just the chunk you want, using an editor. For example, you could use WordPad to just delete out all the records not of interest, and save the remaining records to a new file to be imported.

    On the other hand, if you plan on importing this file or similar files many times, it would be worth your while to write a macro to do the import. A macro could provide several ways to handle your problem:

    1. It could read all your data and wrap the data to additional worksheets;

    2. If you are only interested in reading certain records rather than all records from the input file, it could filter the records while reading them, thereby reducing the total number of records to fit on one sheet;

    3. If you want to skip a certain number of records, or perhaps just read a block of records from the file, it could do this based either on record number, or some value in the record fields that indicates when to start and stop writing records to Excel.

    A VBA macro can gain this flexibility via use of standard file I/O rather than using the Excel import or open capability. If you are interested in investigating this, see the VBA helps for file I/O statements like Open, Line Input #, Input #, Close #, and EOF. It is not hard to do, but if you need a simple example to get started, follow up with your email address and I will send you one.

    Keep Excelling.

    Damon

    VBAexpert Excel Consulting
    LinkedIn Profile http://www.linkedin.com/pub/damon-ostrander/7/79/a93
    AllExperts Profile http://www.allexperts.com/ep/1059-30...-Ostrander.htm

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Los Angeles, CA
    Posts
    752
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    If you want to do data analysis on the information while keeping it together you can importe it to access and run pivot tables from excel to the data in access. I have done this many times and works great for me.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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