Filter large files without opening

[ChrisAch]

Hi All.

I have a file with approx. 400k rows of data.

Is there a way (code) or general ideas, to filter this file without opening it, and given the filter criteria, only open what has been asked of the filter?

Appreciate this is reasonably vague, but I have a blank canvas at the moment

[snb]

What kind of file ?

[ChrisAch]

Its a csv file within a zip

Tx

[snb]

Why would you like to read it without opening it ?

[ChrisAch]

ideally im wanting to filter by ''data type'' and open the file that only contains date type = ''54''

Thanks

[ChrisAch]

Why would you like to read it without opening it ?

Hi

Because the file generally holds around 400-600 rows of records and crashes my excel daily, and im unable to navigate through the workbook effectively.

If I could strip out and open the workbook with only date type 54, it would open appx 20k rows of data, and be more manageable

Tx

[ChrisAch]

400-600 k rows

[snb]

400k rows cant' be a problem.
If it is, you should upgrade your hardware.

[mdmackillop]

Something like this can create a new filtered data file, which you could then import

Sub test()
'http://stackoverflow.com/questions/427488/want-vba-in-excel-to-read-very-large-csv-and-create-output-file-of-a-small-subse
Set cN = CreateObject("ADODB.Connection")
'Note HDR=Yes, that is, first row contains field names '
'and FMT delimted, ie CSV '
strcon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\TestDir\;" _
& "Extended Properties=""text;HDR=Yes;FMT=Delimited"";"
cN.Open strcon
'You would not need delimiters ('') if last field is numeric: '
strSQL = "SELECT * INTO New.csv FROM DataFile.csv " _
& " WHERE Data_Type=54"
'Creates new csv file
cN.Execute strSQL
End Sub

[ChrisAch]

oh, thank you so much, ill try this and revert back.

Possibly the file contains more than 400k rows, that was a rough guess, however it crashes on every occasion, so unable to provide a definitive answer on that.

thanks.

[snb]

Did you ever try ?

Sub M_snb()
  c00=createobject("scripting.filesystemobject").opentextfile("G:\OF\example.csv").readall
  msgbox len(c00)
  msgbox ubound(split(c00,vbCrLf))
End Sub