Importing data from other programs into an Excel spreadsheet, in the form of text files, is a frequent requirement in engineering and scientific applications. Often the data will have been formatted to suit printed output and will require processing before importing into the spreadheet. In addition to rather cumbersome procedures, the built-in Excel facilities for importing text files have several drawbacks; for instance where lines start with a ‘, “, or ^ character, these are treated as text alignment characters and are truncated. Also if numeric text is split into columns results can be unpredictable .
The link below provides routines to select files for import, and import the text to a specified range, optionaly inserting an initial ‘ to avoid truncation of any text alignment characters in the first column of the text.
Future posts will cover splitting the text into columns, and searching for rows containing data, discarding headers and footers etc.
Importing text file with VBA – 2
To use the routines “GetFileName” and “ReadTextSub” in a new file it is necessary to create the following named ranges:
TfileName
destrange
Inserta
and a range with the name specified in destrange.
The function ReadText is called by the subroutine ReadTextSub, but may also be used as a user defined function. In this case it should either be entered as an array function (press ctrl-shift-enter), or inside an INDEX() function. Examples are given in the file below.
Text-in.zip Right click to download
Screen shot:
Newton Excel Bach, not (just) an Excel Blog 
Pingback: Importing text files with VBA « Newton Excel Bach, not (just) an Excel Blog
This looks fantastic. Though I’m not sure I have the technological nous to get it to work as part of what I’m doing. Basically, I want to know the quickest way of getting a 44MB .csv file into excel. Or, better still, into an array which I can play around with it, since I’m not interested in the vast bulk of the data. Any help some wise soul can give me will be greatly appreciated. James.
LikeLike
James – there is some more information which you might find useful here: https://newtonexcelbach.wordpress.com/2008/06/15/importing-text-files-with-vba-2/
That post includes a download with a ReadText function that you can use either from the worksheet as a UDF or call it from a VBA routine.
A simplified version of the code is given below, that will read a text file into an array (and if you have XL 2007 you can probably get the whole file into one worksheet):
Public Function ReadText(FName) As Variant
Dim RowNdx As Long
Dim WholeLine As String, Texta() As String, NumRows As Long, ReadRow As Long
Dim i As Long, j As Long
Close #1
Open FName For Input Access Read As #1
RowNdx = 0
‘Count rows
While Not EOF(1)
Line Input #1, WholeLine
RowNdx = RowNdx + 1
Wend
ReDim Texta(1 To RowNdx, 1 To 1)
Close #1
Open FName For Input Access Read As #1
i = 1
While Not EOF(1)
Line Input #1, WholeLine
Texta(i, 1) = WholeLine
i = i + 1
Wend
ReadText = Texta
End Function
LikeLike
Many thanks. A couple of quick questions though: Could this easily be extended to handle a number of columns of data? And will it work just as well for csv files? James.
LikeLike
James – have you looked at the examples in Text-in2.wks?
The macro is specifically designed to read large delimited text files (such as csv) and split the data into columns.
LikeLike
Thanks again Doug. I struggled to download the Text-in2 stuff initially (I have a Mac here at home where I’m looking this stuff up), but I think I’ve managed it now. As a means of getting data from A to B, I find this reading and splitting of records marginally slower than simply refreshing a query (set up via excel’s “data import” option). But I’ve found that I can make it into a timesaver by filtering out the data I don’t want and performing my calculations at the same time as I’m reading the data into an array.
LikeLike
hi guys, this one looks a really nice post
for spanish native speakers, i’m recently running a new blog with a few entries
go to
http://www.macrosparatodo.wordpress.com
LikeLike
What part of the VBA/worksheet determines where the text will be inserted? I’d like to do the exact same thing but have the data populate a different sheet (already named) rather than Row 17 of the Text-In Sheet. Any help would be appreciated. Also, is there a way to delete certain rows based on a criteria. Ex: If the third column has a negative value, or a value over 50, delete the the entire row where that is true.
Thanks
LikeLike
Amanda – The text is inserted in the range with the name specified in “destrange” (Cell B9), so you just need to either create a new range name where you want the text to go, and enter that name in cell B9, or move the range named “TextFileOut”. To do that:
Press F5 and select TextFileOut
Cut the selected range (Ctrl-x)
Go to where you want it and paste it (Ctrl-v)
I’ll post something about deleting rows in the next few days.
LikeLike
Hi , is it also possible to define the way the text file is read? When a text file has a date column I want that in my output as a date column. Now it shows some dates as numbers.
LikeLike
Hi, I also tried to split a file which contains invoice numbers like 012354. After splitting the 0 is not there anymore. Is it possible to ad this as a parameter in the Criterearng? So when a cell in the Criterearng is formatted as text the outcome will also be text?
LikeLike
Pingback: Daily Download 32: Text functions | Newton Excel Bach, not (just) an Excel Blog