The text import file has been modified to split large files into one or more ranges.
The maximum number of rows for each range is specified, together with a sufficient number of valid range names, which may be on the same sheet or different sheets. The modified file has been tested with a text file with about 1.4 million lines (43 MB). In Excel 2007 this is imported into two ranges in about 10 seconds, or about 75 seconds if it is split into 7 columns. In Excel 2000 the import stopped with a memory error after about 800,000 lines, so for very large files Excel 2007 does seem to have a clear advantage. On the down side, clearing large ranges in Excel 2007 is often excrutiatingly slow. Clearing the 1.4 million lines of data imported in 10 seconds took several minutes!
The ReadText UDF has also been modified to work with large files. It is now possible to specify a column with a list of line numbers, and only the specified lines will be imported. In this way the complete file may be imported into separate ranges, or parts of the file may be conveniently extracted. Note that if the UDF is used with a file exceeding the number of available lines in the spreadsheet the UDF will return an error if the line numbers have not been specified. For smaller files the line number list is optional.
The final refinement in this version is that the SplitText routines now allow the maximum number of columns to be specified. This is illustrated with a directory listing, with the file date and size details split into columns, but the name kept in one column, even if it includes spaces.
1.4 million line text file imported into Excel 2007 in two ranges (about 10 seconds)
Bottom of range 1 (just to prove it’s all there!)
1.4 million line text file split into columns (about 75 seconds)
ReadText function reading every 40th line from the 1.4 million line text file
Reading a directory listing into 5 columns
Filed under: Excel, UDFs | Tagged: Excel, Import large files, import text, split text, UDF, VBA
I found your site on technorati and read a few of your other posts. Keep up the good work. I just added your RSS feed to my Google News Reader. Looking forward to reading more from you down the road!
Your program is a fine effort I like the setup for the file read.
I am trying to read Gedcom files which are text files used to transfer genealogy files between programs.
There are several file types such as ansi, ansel, etf-8.
I believe that the ansi files are unreadable with your program. They are readable by notepad and indeed can be read directly by Excel. I need to extract data from these files and some are too large to read directly into Excel i.e. greater then 1048576 lines.
The problem appears to be that lines are indicated by Chr(10) rather then the normal Chr(13) + Chr(10). Any suggestions on how to get around this.
Dale
Dale – I have sent a reply by e-mail. Could you send a sample file please.
This is a very nice program. I have been looking for something like this for a while. Can it be modified to read in a tab delimited file? I tried changing the separator to chr(9) and just inserted a tab with the keyboard but neither changed the way the file was read in. Can you help me?
[...] files and clearing large ranges Posted on March 1, 2009 by dougaj4 Wray Sisk commented on Importing text files with VBA – 3, asking if it could be modified to read tab delimited [...]
I have to import 16 text files daily to EXCEL.
Daily I’ll get the same names with current dates.
I need the code in the VB to import the text files daily with out asking anything.
Or, I need the code to ask only the current date.
By giving the date, it has to select the entire file by including the date whcih I had given.
Can it be possible in the VB.
saravana – that would be fairly straightforward. I would suggest generating the names of the 16 files on the spreadsheet, then writing a routine with a loop to copy the file name and the associated output range name to the cells TFilename and Destrange,then calling ReadTextSub. Loop through that 16 times and it should do the job.
Let us know if you need any more help.
HOW TO AUTO REFRESH OR AUTO IMPORT THE TEXT FILE ONCE ADDRESS OR PATH IS GIVEN IN 5 SECONDS OR 10. IF TEXT FILE IS AUTO CHANGING THEN EXCEL WHICH IMPORTING SHOULD ALSO CHANGE ITS TEXT WHICH IT IMPORT SO PLZ GIVE ME ITS SAMPLE FILE