Importing Specific Columns From A Text File

Those of you who are dealing with text files (and judging by the blog stats you are many) pay a closer look at this application. The previous days I faced the following problem: I had a large text file with data from various measurements. The data consisted of 125 columns (separated by tabs) and around 30.000 rows. I had to import some specific columns into an excel spreadsheet and make some calculations. While I could just import all the text file and search for each column, I decided to fast things up using VBA. So, I ended up with this workbook that can help you import only the data you need from a text file. Requirements: the text file should have the headings in the first row, and the columns must be tab-separated.

Description of the VBA procedures

This workbook contains two main macros plus one for cleaning. Below I enumerated the actions that the two main macros perform when they are executed.

ReadTextHeadings macro:

1. Show the file dialog and wait to select a text file.
2.  Open the text file and read the headings (first line).
3. Add data validation and conditional formatting for the column selection (mainsheet).
4. Format the main sheet.

LoadData macro:

1. Check the file path.
2. Check which columns are being selected.
3. Get the name of the text file.
4. Add a new temporary sheet.
5. Import the text file into the temporary sheet.
6. Create a new sheet based on the text file’s name.
7. Copy the selected columns to the new sheet.
8. Format the new sheet and delete the temporary sheet.

The above approach might not be straightforward but is quite fast. I have also tried an alternative method to import the data. The LoadDataAlternatively macro reads the data line by line (using Line Input command) and writes only the selected columns to the spreadsheet. However, it was considerably slower than the approach I finally followed (LoadData), so I did not include it in this workbook.