Excel spread sheet larger than 65536 records

[Chuck]

How can I import a delimited text file of 600,000 records in Excel?

 

[Joel]

this code will put data on multiple sheets


Sub GetCSVData()

Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0
Const Delimiter = ","
Set fsread = CreateObject("Scripting.FileSystemObject")

'default folder
Folder = "C:\temp\test"
ChDir (Folder)

FName = Application.GetOpenFilename("CSV (*.csv),*.csv")


RowCount = 1
If FName <> "" Then
'open files
Set fread = fsread.GetFile(FName)
Set tsread = fread.OpenAsTextStream(ForReading, TristateUseDefault)

Set newsht = Sheets.Add(after:=Sheets(Sheets.Count))

Do While tsread.atendofstream = False

InputLine = tsread.ReadLine

'extract comma seperated data
ColumnCount = 1
Do While InputLine <> ""
DelimiterPosition = InStr(InputLine, Delimiter)
If DelimiterPosition > 0 Then
Data = Trim(Left(InputLine, DelimiterPosition - 1))
InputLine = Mid(InputLine, DelimiterPosition + 1)
Else
Data = Trim(InputLine)
InputLine = ""
End If

newsht.Cells(RowCount, ColumnCount) = Data
ColumnCount = ColumnCount + 1
Loop
RowCount = RowCount + 1
If RowCount = Rows.Count Then
RowCount = 1
Set newsht = Sheets.Add(after:=Sheets(Sheets.Count))
End If
Loop
tsread.Close
End If
End Sub

 

[Cimjet]

Hi Chuck
In Excel 2007 Worksheet size 1,048,576 rows by 16,384 columns but you need
the memory to use it.
HTH
Cimjet

 

[Jim Thomlinson]

While it is technically feasable to import 600k records into XL it is not
very practical. You will bog down the system if you want to do any
calculations. A better solution might be to use a pivot table to analyze your
data. You can hook directly to the data file via MS Query and import the
records directly into the pivot cache. The pivot cache is not bound by 65,536
and the performance of calculations is remarkably good. Just something to
consider. If you need some help just ask...

 

[RayC]

I am also having difficulty with this. I'm trying to import a total of about
100,000 records from three tab-delimited files, each under 41,000 records. It
hits 65,536 records and shuts me down, despite the over one million row spec.
for Excel 2007.

I have also tried importing into three separate spreadsheets and
copy/pasting from them into a single spreadsheet, and get a copy and paste
area are not the same size and shape error, though when I select a smaller
number of records the same way (selecting whole rows, not individual cells),
it will perform the copy/paste fine. I've tried copy/pasting by selecting
cells, not rows, with the same results.

The spreadsheets are saved in the new Excel format, not compatibility mode.
I'm running Excel 2007 under Windows Vista, both up-to-date on patches, 1GB
RAM, plenty of disc space and a dual-core processor.

Any help would be greatly appreciated.