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

SideburnsJim

New Member
Joined
Nov 24, 2017
Messages
5
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?
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,973
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

New Member
Joined
Nov 24, 2017
Messages
5
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

Well-known Member
Joined
Aug 31, 2016
Messages
3,973
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

New Member
Joined
Nov 24, 2017
Messages
5

ADVERTISEMENT

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.
 

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,716
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,847
Messages
5,627,239
Members
416,232
Latest member
Ash1432

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top