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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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.
 
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.
 
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
 
Oops. That should be: cells(RowCount, ColNbr) = DataRecord

Bill
-----------------------
 
Lauren said:
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
Click to expand...

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.
 
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.
 
Back
Top