Excel text import filter

[mscallisto]

The Text import Wizard allows one to skip columns however I would like to skip (filter) by rows. Is this possible?

Example

My text file resembles the following:
Joe M
Jim M
Alice F

How does one import text data for only column 2 equal M?

 

[AidanEnos]

import the whole file, sort by column b and delete the rest - sorry but I Excel doesn't have the parsing capabilities you are looking for.

 

[mscallisto]

Thanks Kindly

That's what I was hoping not to hear.
My text file contains >65k rows yet I only need a few of these rows some which happenn to be beyond the 65k limit of Excel.

I can write some code to shorten the text file, but was hoping Excel had the filtering I needed.

 

[AidanEnos]

you can write a piece of code... check with the boys over at the visual C forum or the VB forum... anyone one of them should be able to write you a 5k executable in their sleep to accomplish this...

I gotta learn to program!

AidanEnos

 

[euskadi]

I had a thought that I can't make work, can anyone try this and run with it?

Create a new database query where your data type is Excel.
I created a file C:\data.xls with these contents:
Sex Name
F Brittany
M Sebastian
M Austin

and I named this range "Table1", closed the workbook, and tried creating a new query in a new workbook. I can get to the point where I add the table and see the field names, but I get a "Syntax error in FROM clause" when I try to add criteria. Can anyone make this work?

 

[euskadi]

OK, I resaved my .xls file as .csv, and used the "Text Driver" to set up my query and it worked. I could set the Criteria to sex = 'M' and I only imported my boys, not all three of my children. Good luck!

 

[mscallisto]

Hi euskadi

It certainly sounds like you're on to something, thanks for your effort!!

Could you, if you have time, explain in a bit more detail?

I'm not convinced that I know how to set up a query in Excel and I'm not familiar with "Text Driver"

Finally I don't know why you have to save you're excel file as a .csv. That's basically what I started with.

Please don't spend too much time on this as I have a "albeit cumbersome" workaround outside of Excel.

Thanks again very much

 

[euskadi]

What you're really doing is setting up a query, much like you would in Access.

Have you ever used Access to build your own queries?