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

[forensic93]

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.

 

[ajetrumpet]

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]

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.

FName = Application.GetOpenFilename(FileFilter:="Text Files (*.txt),*.txt," & _
                                                "CSV Files (*.csv),*.csv")

 

[ajetrumpet]

Per this article, you can't do it. you have to use VBA after import, more than likely.