PDA

View Full Version : Filter large files without opening



ChrisAch
07-12-2016, 02:47 AM
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
07-12-2016, 03:19 AM
What kind of file ?

ChrisAch
07-12-2016, 03:26 AM
Its a csv file within a zip

Tx

snb
07-12-2016, 03:32 AM
Why would you like to read it without opening it ?

ChrisAch
07-12-2016, 03:33 AM
ideally im wanting to filter by ''data type'' and open the file that only contains date type = ''54''

Thanks

ChrisAch
07-12-2016, 03:43 AM
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
07-12-2016, 03:44 AM
400-600 k rows

snb
07-12-2016, 04:10 AM
400k rows cant' be a problem.
If it is, you should upgrade your hardware.

mdmackillop
07-12-2016, 04:27 AM
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
07-12-2016, 04:56 AM
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
07-12-2016, 06:47 AM
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