Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Selectively Importing Records.
by Allen Wyatt
(last updated March 1, 2014)
Ole ran into a problem importing information into an Excel workbook. It seems that the files he needs to import typically have thousands and thousands of records in them. Ole doesn't need most of the input rows, however, and normally gets rid of them once the records are imported into Excel. Ole is looking for a way to get rid of the unwanted records during the import process, so that he has less work to do when the data is in his workbook.
There are a couple of different ways that a solution to this problem can be approached. One solution is to use Access as your first importing step. Access will easily handle the thousands of records you want to import—even if there are more records than what you can import into Excel. You could import the file into Access, filter out the unwanted records, and then export the resulting table as an Excel workbook.
The best solution, however, may be to bypass Excel's import filters entirely. You can easily write an import routine in VBA, and allow it to process the import file. For instance, consider the following macro:
Sub Import() Dim sFile As String Dim sUnwanted As String Dim sDelim As String Dim iRow As Integer Dim iCol As Integer Dim bBadRecord As Boolean Dim iTemp As Integer sFile = "d:\data.txt" sUnwanted = "bad text" sDelim = "," Open sFile For Input As #1 iRow = 1 While Not EOF(1) 'Scan file line by line iCol = 1 Line Input #1, sBuffer ' Check to see if should ignore record bBadRecord = Instr(sBuffer, sUnwanted) If Not bBadRecord Then iTemp = Instr(sBuffer, sDelim) While iTemp > 0 With Application.Cells(iRow, iCol) .NumberFormat = "@" 'Text formatting .Value = Left(sBuffer, iTemp-1) End With iCol = iCol + 1 sBuffer = Mid(sBuffer, iTemp+1, Len(sBuffer)) iTemp = Instr(sBuffer, sDelim) Wend If Len(sBuffer) > 0 Then With Application.Cells(iRow, iCol) .NumberFormat = "@" 'Text formatting .Value = sBuffer End With End If iRow = iRow + 1 End If Wend Close #1 End Sub
This macro opens a data file and reads each record in the file. It checks the record to make sure it is OK to import, and then pulls the record apart, based on a delimiter, and stuffs the information into the current worksheet. You can change the name of the data file (the sFile variable), the text that indicates a bad record (sUnwanted variable) and the delimiter (sDelim variable).
As an example, let's assume that you have a data file named Customers.txt. This file contains all your customer records, but you don't want to import the records for customers with addresses inside the United States. Further, the records in the data file use a tab character between each field. In this case, you would only need to make the following changes to the variables at the beginning of the macro:
sFile = "d:\Customers.txt" sUnwanted = "United States" sDelim = Chr(9)
Once you run the macro, the current worksheet contains just the desired data.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2239) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Selectively Importing Records.
Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!
If you try to open a second workbook and you see an error message, it could be because of the way you are opening the ...Discover More
When you import a CSV file into an Excel worksheet, you may be surprised at how the program allocates the information among ...Discover More
Does your macro need to add information to the end of a text file? This is called appending, and is done using the technique ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.