Gordon wonders how he can import a subset of a text file into Excel, depending upon the value of a particular field. As an example, he may want to only import records that contain a "y" in column 5 of each record in the text file.
There are several ways you can approach this task. One is that you could simply import the whole text file, sort the records, and delete those you don't want. This is, perhaps, the simplest option if you only need to process a single file and the entire file can fit within a single worksheet.
Another approach is to use a macro. (This is the one that I find the fastest and easiest, particularly if you need to import the same type of file quite a bit.) The macro can open the text file, read each line, and then make a determination as to whether the information in that line should be added to the worksheet or not. Here's an example that will open a file named "MyCSVFile.txt" and then stick the data into a new worksheet starting at the first row.
Sub ReadMyFile() Dim R As Integer Dim C As Integer Dim sDelim As String Dim sRaw As String Dim ReadArray() As String sDelim = "," ' Set to vbTab if tab-delimited file Worksheets.Add Open "myCSVFile.txt" For Input As #1 R = 1 Do While Not EOF(1) Line Input #1, sRaw ReadArray() = Split(sRaw, sDelim, 20, vbTextCompare) If ReadArray(4) = "y" Then For C = 0 To UBound(ReadArray) Cells(R, C + 1).Value = ReadArray(C) Next C R = R + 1 End If Loop Close #1 End Sub
To use the macro, simply change the name of the file to match the file you want to process. You'll also want to modify the sDelim variable to make sure it matches whatever is being used as a delimiter in your records. As written, it assumes the delimiter is a comma (which it would be in a CSV file), but you could change it to vbTab if you are actually working with a tab-delimted file. After the macro is complete, only those records with a single, lowercase "y" character are in the new worksheet.
Another approach is to use the Power Query feature of Excel. This is a free add-in, from Microsoft, that is available for some variations of Excel 2010 and Excel 2013. You can download (and find out which variations are supported) at this location:
http://www.microsoft.com/en-us/download/details.aspx?id=39379
If you are using Excel 2016, then Power Query is built into the program. If you have Power Query installed or available in your version of Excel and that version of Excel happens to be Excel 2010 or Excel 2013, then follow these steps:
If you are using Excel 2016 or a later version, the steps are a bit different:
At this point—regardless of the version of Excel you are using—you can use the controls to specify a query (meaning, setting up a definition of which records should be imported). When you click Close and Load, the records are retrieved from the file, and the query can be saved for future use.
A fourth approach is to use Microsoft Query. To do so, you'll need to follow this very lengthly series of steps. (Nobody ever said that Microsoft wanted to make Microsoft Query easy to use, and you'll agree after you go through these steps.)
Figure 1. The Choose Data Source dialog box.
Figure 2. The Create New Data Source dialog box.
Figure 3. The Add Criteria dialog box.
Figure 4. The Import Data dialog box.
(Told you the steps were lengthly.) You can now work with the data in Excel and, if desired, use the tools on the Design tab of the ribbon to refresh the data from the CSV file.
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (10384) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Office 365.
Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!
Want to use the maximum space possible for displaying information on screen? You'll want to learn how to use the ...
Discover MoreNeed to concatenate the contents in a number of columns so that it appears in a single column? Excel has no intrinsic way ...
Discover MoreExcel includes a handy shortcut for entering data that is similar to whatever you entered in the cell above your entry ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2015-10-31 21:06:28
David Leyden
This is the path I first went down to import csv files. Unfortunately split is mostly useless for parsing csv files. Regular expressions aren't much better. These methods will fail if you have delimiters inside text qualifiers which is very common. You need to be using ADODB or something like it if you want a robust way of parsing csv.
2015-10-31 18:27:47
Jeff
This macro assumes there are at least 5 fields in each input record. You might want to check the upper bound of ReadArray() after the Split command to avoid a runtime error if the input is bad. You should also explain the third parameter passed to the Split command.
2015-10-31 10:22:36
Robert
This tip mentions how "convoluted" is the MS Query, which has the SQL power under its sleeves. Once you master it, you will appreciate the easy retrieval of records from any data using ODBC. Step 12 of this tip will open you the doors of SQL and it is just click and drag, no coding involved from your side...I love it.!!!!
You can use it to create relational tables within excel spreadsheets, retrieve from MS Access, etc.
The ODBC Connection will allow you to connect to any type of structured database and you will be the next guru at your work place.
This is the best descriptiive tip I have found to work with Query using SQL.
2015-10-31 07:27:23
Michael
A great tip which would be very useful to me, but... Most of the downloads I direct to Excel result from checking an "Open With" option offered by the web page after it presents the CSV data in a format of its choosing. I don't see offhand how to qualify that with a (specific) macro to be used during the download.
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.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2021 Sharon Parq Associates, Inc.
Comments