+ Reply to Thread
Results 1 to 7 of 7

how do I download a csv file with 100,000 records into excel?

  1. #1
    Lauren
    Guest

    how do I download a csv file with 100,000 records into excel?

    I have a csv file with 100,000 records on it, I know excel only downloads up
    to a little over 65,000, and I have tried the Wizard, but it won't let me
    export specific rows after about 32,000. I have also tried to change the csv
    file to a Word file and cut it into two seperate files, but then Excel won't
    recognise the format. There must be a way!
    PS I don't have Access

  2. #2
    Guest

    Re: how do I download a csv file with 100,000 records into excel?

    Hi

    I suggest you try 'cutting it in half' using Wordpad. Mind you, you still
    won;t be able to see it all in Excel: you'll have to deal with it in two
    bits.

    Andy.

    "Lauren" <[email protected]> wrote in message
    news:[email protected]...
    >I have a csv file with 100,000 records on it, I know excel only downloads
    >up
    > to a little over 65,000, and I have tried the Wizard, but it won't let me
    > export specific rows after about 32,000. I have also tried to change the
    > csv
    > file to a Word file and cut it into two seperate files, but then Excel
    > won't
    > recognise the format. There must be a way!
    > PS I don't have Access




  3. #3
    Dave Peterson
    Guest

    Re: how do I download a csv file with 100,000 records into excel?

    Use NotePad (or Wordpad and save as .txt) to split the large file into smaller
    pieces.

    If you use MSWord and save as .doc, you're gonna have trouble.

    Lauren wrote:
    >
    > I have a csv file with 100,000 records on it, I know excel only downloads up
    > to a little over 65,000, and I have tried the Wizard, but it won't let me
    > export specific rows after about 32,000. I have also tried to change the csv
    > file to a Word file and cut it into two seperate files, but then Excel won't
    > recognise the format. There must be a way!
    > PS I don't have Access


    --

    Dave Peterson

  4. #4
    Bill Martin
    Guest

    Re: how do I download a csv file with 100,000 records into excel?

    What I've done when faced with a similar problem is to use VBA to import the
    first 65,532 records on Sheet1, then the next 65,535 onto Sheet2 and so
    forth. I don't know how you can do it without using VBA, though perhaps
    someone else here can tell you.

    If you're comfortable with VBA, the example below reads a big file and
    stores it as multiple columns on one sheet. You'll get the drift:

    dim DataRecord as string
    dim RowCount as Long
    dim ColNbr as Long

    RowCount = 1
    ColNbr = 1

    Open "c:\DataFiles\Foobar.txt" for input as #1
    Do Until EOF(1)
    Line Input #1, DataRecord
    cells(RowCount,1) = DataRecord
    if RowCount >= 65535 then
    RowCount = 1
    ColNbr = ColNbr + 2
    else
    RowCount = RowCount + 1
    end if
    Loop
    Close #1

    Good luck...

    Bill
    -------------------------------------------------------

    "Lauren" <[email protected]> wrote in message
    news:[email protected]...
    >I have a csv file with 100,000 records on it, I know excel only downloads
    >up
    > to a little over 65,000, and I have tried the Wizard, but it won't let me
    > export specific rows after about 32,000. I have also tried to change the
    > csv
    > file to a Word file and cut it into two seperate files, but then Excel
    > won't
    > recognise the format. There must be a way!
    > PS I don't have Access



  5. #5
    Bill Martin
    Guest

    Re: how do I download a csv file with 100,000 records into excel?

    Oops. That should be: cells(RowCount, ColNbr) = DataRecord

    Bill
    -----------------------
    "Bill Martin" <[email protected]> wrote in message
    news:[email protected]...
    > What I've done when faced with a similar problem is to use VBA to import
    > the first 65,532 records on Sheet1, then the next 65,535 onto Sheet2 and
    > so forth. I don't know how you can do it without using VBA, though
    > perhaps someone else here can tell you.
    >
    > If you're comfortable with VBA, the example below reads a big file and
    > stores it as multiple columns on one sheet. You'll get the drift:
    >
    > dim DataRecord as string
    > dim RowCount as Long
    > dim ColNbr as Long
    >
    > RowCount = 1
    > ColNbr = 1
    >
    > Open "c:\DataFiles\Foobar.txt" for input as #1
    > Do Until EOF(1)
    > Line Input #1, DataRecord
    > cells(RowCount,1) = DataRecord
    > if RowCount >= 65535 then
    > RowCount = 1
    > ColNbr = ColNbr + 2
    > else
    > RowCount = RowCount + 1
    > end if
    > Loop
    > Close #1
    >
    > Good luck...
    >
    > Bill
    > -------------------------------------------------------
    >
    > "Lauren" <[email protected]> wrote in message
    > news:[email protected]...
    >>I have a csv file with 100,000 records on it, I know excel only downloads
    >>up
    >> to a little over 65,000, and I have tried the Wizard, but it won't let me
    >> export specific rows after about 32,000. I have also tried to change the
    >> csv
    >> file to a Word file and cut it into two seperate files, but then Excel
    >> won't
    >> recognise the format. There must be a way!
    >> PS I don't have Access

    >



  6. #6
    Doug Kanter
    Guest

    Re: how do I download a csv file with 100,000 records into excel?

    "Lauren" <[email protected]> wrote in message
    news:[email protected]...
    >I have a csv file with 100,000 records on it, I know excel only downloads
    >up
    > to a little over 65,000, and I have tried the Wizard, but it won't let me
    > export specific rows after about 32,000. I have also tried to change the
    > csv
    > file to a Word file and cut it into two seperate files, but then Excel
    > won't
    > recognise the format. There must be a way!
    > PS I don't have Access


    Good advice from all the others. If you're not comfortable with Wordpad, go
    to www.download.com and search for NoteTab (not to be confused with Notepad,
    which comes with Windows). There's a free version of NoteTab that works just
    fine for your purposes. Open your csv file, go to line 64999 or whatever's
    just shy of Excel's limit, and cut & paste the 2nd half of the file to a new
    document. Be sure to save as csv, or whatever extension you'll remember
    later.



  7. #7
    Forum Contributor
    Join Date
    12-14-2005
    Posts
    176
    If you use WORD
    Do not IMPORT the file.
    Start WORD - Then close the "Blank" template it gives you and then do a FILE OPEN Select your file and then split it and then do a SAVE AS and select the TXT mode.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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