VBA to import multiple tab delimited .txt files and save smaller version

[SideburnsJim]

I have a slight strange request.
I have a tab delimited .txt file created daily "output_ddmmyyyy".
This file is huge - 1000 columns and about 50,000 rows.

I'd like to create a VBA program that allows you to select which .txt files to open, and then import them automatically, before saving back down as a .txt file with a limited number of variables kept.

I'd like the user to control which variables are kept in the saved output by name. For example - having a spreadsheet which individually lists which 50 variables they wanted to keep in the final output.

Does anyone have any ideas where to start for this?

 

[Logit]

One method :

Import the file to Sheet1.

Use TEXT TO COLUMNS to separate the data into individual columns.

Create a macro that deletes the unwanted columns.

Save workbook as .CSV file or ... copy SHEET1 / paste to Notepad.

 

[SideburnsJim]

Thanks.

I'd like to automate the whole process, as the files are so large they take a while to open up.

To that end, I was wondering if it was possible in VBA to restrict the variables being read in as part of the import. That way, the process wouldn't have to open 100% of the file only to keep 10% of it?

 

[Logit]

No matter what method you select, opening the TEXT file will be mandatory.

Can you provide a sample of one of the TEXT files ? Not the entire 50,000 rows ... say just 20 lines and all columns ?

 

[SideburnsJim]

Unfortunately, I can't. This is personal customer data in a bank, and I'd get fired for uploading any of it to the internet.

Many thanks for confirming that the .txt file will need to be fully opened though. This makes me think that an approach to take would be to read it fully in, put my list of variables to keep in an array and then go from one column to the next and if row 1 = any of the variables in the array then keep it, otherwise delete it. Then move on to the next column.

 

[Logit]

Best wishes on your project.

 

[John_w]

I disagree about having to open the .txt file. The Text Import Wizard or Power Query (Data tab -> From Text/CSV) imports the data into a sheet without opening the file. Both methods also allow you to specify the columns you don't want to import (by skipping or removing them), so you don't need to import all the columns, just the columns the user has specified.

Record a macro with either method as a starting point.

The user selecting multiple files to import can be done with Application.FileDialog(msoFileDialogFilePicker) with AllowMultiSelect = True