Importing text files with VBA

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:
Text-in screen shot

About these ads
This entry was posted in Excel, UDFs, Uncategorized and tagged , , , , . Bookmark the permalink.

12 Responses to Importing text files with VBA

  1. Pingback: Importing text files with VBA « Newton Excel Bach, not (just) an Excel Blog

  2. james says:

    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.

    Like

  3. dougaj4 says:

    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

    Like

  4. james says:

    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.

    Like

  5. dougaj4 says:

    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.

    Like

  6. james says:

    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.

    Like

  7. sparklight01 says:

    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

    Like

  8. Amanda says:

    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

    Like

  9. dougaj4 says:

    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.

    Like

  10. tonie says:

    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.

    Like

  11. tonie says:

    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?

    Like

  12. Pingback: Daily Download 32: Text functions | Newton Excel Bach, not (just) an Excel Blog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s