Importing selected rows from a text file

I was recently asked if the routine in the Text-in2 spreadsheet to import and split data from text files could be modified to only import rows that met specified criteria.  A similar end result can be achieved by importing the whole file, then using the Excel data filtering routines, but applying the selection criteria at the text import stage does have some advantages:

  • It allows selection of data from very large files, without exceeding the row limit in Excel.
  • The process is quicker to set up.
  • The use of VBA allows the possibility of more flexible import criteria.

This feature has now been added to the spreadsheet, which can be downloaded from the link above.  The routine as written checks that the values in the specified columns are within a specified numerical range, but it could easily be amended to work on text.

The download file includes full open source code, and the new routines to read and check the criteria are shown below:

Modifications to the GetData Function:

ApplyCriteria = Range("ApplyCriteria").Value2
If ApplyCriteria(1, 1) > 0 Then
Criteria = Range("CriteriaRng").Value2
NumCrit = UBound(Criteria)
StartRow = ApplyCriteria(1, 1)
If ApplyCriteria(1, 2) > 0 Then EndRow = ApplyCriteria(1, 2)
NumCrit = 0
    End If
If NumCrit > 0 Then
If i >= StartRow And i <= EndRow Then CheckCriteria DataA2, Criteria, NumCrit, i, m
End If

CheckCriteria routine

Sub CheckCriteria(ByRef DataA2 As Variant, ByRef Criteria As Variant, ByVal NumCrit As Long, ByVal i As Long, ByRef m As Long)
Dim n As Long, Chkcol As Long, DatVal As Double
For n = 1 To NumCrit
Chkcol = Criteria(n, 1)
If Chkcol <> 0 Then
DatVal = Val(DataA2(m, Chkcol))
            If Criteria(n, 2) <> "" Then
If DatVal < Criteria(n, 2) Then
m = m - 1
                    Exit Sub
                End If
            End If
            If Criteria(n, 3) <> "" Then
If DatVal > Criteria(n, 3) Then
                    m = m - 1
                    Exit Sub
                End If
            End If
        End If
    Next n
End Sub

The spreadsheet needs two additional named ranges:

  • ApplyCriteria (B16:C16): the row number of the first and last line to be imported
  • CriteriaRng (A19:C24): Column number, minimum and maximum values to be imported.

Input and typical output are shown in the screenshot below (click for full size view)

Text import with Criteria ranges

This entry was posted in Excel, VBA and tagged , , . Bookmark the permalink.

8 Responses to Importing selected rows from a text file

  1. This is nice. Now when you write this, is this just for this site, for this demo, or was there another use for it? Do you put these out there for Do you put these out there for use elsewhere? This took several hours, right?



  2. dougaj4 says:

    Most of what I post is things I have done for my own work, education or entertainment. This has the dual benefit of forcing me to complete things to a stage where they are reasonably presentable, and to document and store them in a place where they will get indexed, so I can find them in the future. In this case it was in response to an e-mailed request for help. It was something that I could see being useful to me in the future, so I’m happy to spend a few hours on that sort of thing (if I have a few hours to spare).


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

  4. dougaj4 says:

    Question received by e-mail:

    “I am new to VBA was trying to write code similar to your which I found following a google search, I have tried running your code but the debugger gives a run time 1004 as I am not specifing the range in DestRange = Range(“destrange”).Value2

    Can you please explain with an example how in Text In the destrange is specified.”

    The easiest way to use the code in Text-in2.xls is to modify the original workbook, but if you want to start from scratch with a new file you will need to create the range names used in the code. In the case of “destrange” you will need a single cell with the name destrange, and another range (where you want the ouput to go) with the name entered in destrange (TextFileOut was used in my example, but it could be any valid name).


  5. NikF says:

    Thanks for the prompt reply….I’ll see what happens after work, did not realize you were using names.


  6. GowriShankar says:

    Hello Genius.. really impressed with ur work. One small doubt.. Im a nowise in VBA.. could you please tell me wat to change If I have to just apply a direct criteria..? More Like anything that matches the word “Academy”


    • dougaj4 says:

      The criteria have to be numerical at the moment, but it would be possible to amend it to search for text, either in specified columns, or any column. I’ll try and find some time to do that (or if anyone else can recommend a free download that does it already, please let us know).


  7. Pingback: Importing Text Files; Unix Format | Newton Excel Bach, not (just) an Excel Blog

Leave a Reply

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

You are commenting using your 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