Importing Lines from a Text File Which Meet Certain Criteria using VBA

forensic93

New Member
Joined
Jan 14, 2020
Messages
16
Office Version
2016
Platform
Windows
Hi all,

Looking to get some expert help as i'm a novice with VBA. I've currently tried searching online and i can't seem to find a macro that can help with what i am trying to do.

Essentially i have large data files with more then 1.6 million lines of data. I currently have a macro which will import this data over multiple pages, but i have found that a lot of my data rows i don't need to import because they have 0 values in them. Essentially only 300,000 of the 1.6 million lines i need to import based on certain criteria. I have previously tried to delete the rows in which certain columns have a 0 but i kept getting memory errors.

So I was wondering if it was possible to import the line of a text file if the data in columns 2, 3, 4, 6 and 7 are not 0. Therefore eliminating the import of all data lines that have a 0 value, and not having to split the data over multiple sheets.
There are 9 columns in each file in which i need all of them, but just using the columns above to specify which lines are of value.

Example of text file is:
1st row - heading, heading, heading, heading, heading, heading, heading, heading, heading
2nd row - 1,0,0,0,0,0,0,0,0
3rd row - 2,0,0,0,0,0,0,0,0
300000 row - 300001, -53,14,27,92,41,82,93,4
etc

Any help would be greatly appreciated.
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

ajetrumpet

Banned for being rude
Joined
Apr 12, 2008
Messages
569
Office Version
365, 2016, 2007
Platform
Windows
hmmmm....what method are you currently using to import the text file data? the import wizard in the interface? have you ever used I/O??

GET => Get statement (VBA)
PUT => Put statement (VBA)

Can't remember if you can do this by way of a .txt to an .xlsx or not. It's basically a low level operation because it transfers data by reading it in byte codes. there are many *modes* you can use to open these files too, but you can read about that stuff in those articles.
 

forensic93

New Member
Joined
Jan 14, 2020
Messages
16
Office Version
2016
Platform
Windows
hmmmm....what method are you currently using to import the text file data? the import wizard in the interface? have you ever used I/O??

GET => Get statement (VBA)
PUT => Put statement (VBA)

Can't remember if you can do this by way of a .txt to an .xlsx or not. It's basically a low level operation because it transfers data by reading it in byte codes. there are many *modes* you can use to open these files too, but you can read about that stuff in those articles.
I'm currently using the following code to import the data and then i have other processes to split over multiple sheets. But i'm trying to not import all the data, thats why i was wondering if it was possible to search for certain paremeters in a line before importing.
VBA Code:
FName = Application.GetOpenFilename(FileFilter:="Text Files (*.txt),*.txt," & _
                                                "CSV Files (*.csv),*.csv")
 

Watch MrExcel Video

Forum statistics

Threads
1,098,860
Messages
5,465,116
Members
406,414
Latest member
Discorz

This Week's Hot Topics

Top