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
Newton Excel Bach, not (just) an Excel Blog 
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!
LikeLike
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
LikeLike
Dale – I have sent a reply by e-mail. Could you send a sample file please.
LikeLike
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?
LikeLike
Pingback: Importing tab delimited files and clearing large ranges « Newton Excel Bach, not (just) an Excel Blog
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.
LikeLike
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.
LikeLike
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
LikeLike
This is wonderfully quick to transfer a large amount of data, but I am finding that it does not allow for empty cells – it fills them in with the next data point instead of leaving an empty cell and moving on. I’m quite a novice, so reading through the code I can’t work out where in the code this is occurring – can you tell me how to fix it? If you can it will solve a big headache of an otherwise very slow cell-by-cell import code I’m using. If you have some code for a really quick array export as well, I’d love to have it – I have something that works, but it is a bit slow. Thanks!
LikeLike
Jane – thanks for the comments. I’m a bit snowed under at the moment, but I’m planning a post on importing very large text files into Excel in the near future, and I’ll look at your question on dealing with blank spaces then.
With the array export question, do you mean exporting a VBA array to the spreadsheet? If so there are examples in the Text-in2 code, a simplified version is:
With Range(MyRangeName)
.ClearContents
.Resize(NumRows, NumCols).Name = MyRangeName
End With
Range(MyRangeName).Value2 = ExportArray
MyRangeName is the name of a range with the top left corner where you want the exported data. Note that this will erase any data that was in MyRangeName, and if ExportArray is bigger than MyRangeName it will write over any data within its output range without warning.
LikeLike
Thanks for the response. Can you give me an idea of timing of when you might do your next post? I realise my priority is not your priority, but just so you know it will be appreciated when done, my priority is pretty urgent!! Basically I need to be able to export the data from a large excel file (1 page of 48 columns x 2500 rows and another of 33×40,000 rows – there are some columns I don’t want to use as they are just formulae, but I assume I can just do more than 1 array) into a txt file so that it can be zipped and email through a slow internet connection (in Africa) and then re-import that same data into a copy of the same template. If I do it using a cell-by-cell import it takes about 15 minutes on the slow computer they are using (with more time to then calculate all the formulae that rely on this data), which isn’t really acceptable when they will need to do a transfer of the data every day. I tried your array code for the 48X2500 and it was really quick, so I’m assuming I can cut the total time down if only I can deal with the blank cells issue. Any guidance you can offer to a novice would be greatly appreciated! 🙂
LikeLike
Jane – I’ll try and and look at it tomorrow. I’ll let you know if I don’t have time.
Could you e-mail a typical file (or something random in the same format, if it’s confidential). dougaj4@gmail.com
LikeLike
Very nice worksheet and vba code. If I want to automate it a little more by :
I have multiple .asc files I receive every day of different names. I would like to see how I import all at once.
Criteria :
MAX number of daily files : 10
MIN nr. of daily files : 1
Varied names
Each file contains a MAX nr. of columns of 5 but could be 4.
Any help would be appreciated. Thx.
LikeLike
hi,
is there a function to select a certain keyword in the text file? have a huge log-file a searching for a solution to select only one keyword…
regards
mikael
LikeLike
Pingback: Daily Download 32: Text functions | Newton Excel Bach, not (just) an Excel Blog
Hi I am getting run time error Run time Error ’13’…Type Mismatch…when I get into debug mode it highlights me at this part of the VBA……DatVal = Val(DataA2(m, Chkcol))…could you please let me know why is this error occuring
LikeLike
I would need to see exactly what you are doing, with the data file you want to import.
If you send your files to dougaj4 at gmail I will have a look.
LikeLike
I don’t understand the use of TextFileOut in the destrange. Can somebody explain it to me?
LikeLike
maw – if you press F5 on the spreadsheet, and select “TextFileOut” you will see this is a named range where the output goes.
You can just leave it as is, or if you want the output to go somewhere else just create your own named range (say “MyOutputRange”), then enter that name in the cell next to “destrange”.
LikeLike
Pingback: Importing Text Files; Unix Format | Newton Excel Bach, not (just) an Excel Blog