Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Selectively Importing Records.
by Allen Wyatt
(last updated December 31, 2021)
Ole ran into a problem importing information into an Excel workbook. It seems that the files he needs to import are text files that 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. This works great if your import data, like Ole's, is in a text file. 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 (8491) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365. You can find a version of this tip for the older menu interface of Excel here: Selectively Importing Records.
Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!
If you have a macro that selects different columns in a worksheet while processing information, you may get some ...Discover More
Want to run a macro when you first select a worksheet? You can do so by using one of the event handlers built into Excel, ...Discover More
When creating macros, you'll often have a need to select different cells in the worksheet. Here's how to select the first ...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 ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.