Using 65,000+ line text files in Excel

fiarb · July 19th, 2006, 2:36 pm

looking for ideas or vba snippets to read a very large csv log file into either excel or into a vbayes, would prefer to use Access, etc ... but do not have itthe data has many multiples of Excels' 65,000ish row limit (300,000 lines last I looked), is sequential, and the rows are not numberedinitial thought is to read entire file into vba then place the clumps of 60,000 lines of data onto successive sheets in an Excel workbook for analysis... but how?

mutley · July 20th, 2006, 5:44 am

a mini example of how to I/O csv files [there are many ways of doing it].as you probably don't want to "see" all 300k lines on your sheet, i'd read it all in to memory, do whatever processing you want on it in VBA and output relevant results to the spreadsheet.

afoster · July 20th, 2006, 6:31 am

Using a combination of Excel & VBA it is quite easy to read and process this file, however with any dataset that large it is a little pointless trying to eyeball the data all at once. If your trying to perform some statistical analysis, you have many options.If you are limited to just using Excel/VBA, then you can of course write various functions to process the data, however, I always find using a dedicated statistics package like R is much better suited for this type of work. Also, just because you don't have Access - why not try MySQL? Both are free BTW.

mutley · July 20th, 2006, 7:17 am

R's great for reading in large csv files of data and calc'ing stats on them. read.csv/write.csv are probably some of the best bits of R.

fiarb · July 23rd, 2006, 2:30 am

thanks for the suggestions, will look at R and MySql again, thanks