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:
Filed under: Excel, UDFs, Uncategorized | Tagged: Excel, import, text, UDF, VBA
[...] Importing text files with VBA Filed under: Excel, UDFs — Tags: Excel, import text, UDF, VBA — dougaj4 @ 3:04 pm Previous post [...]
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.
James – there is some more information which you might find useful here: http://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
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.
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.
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.