Is there a way to open a *FILTERED* recordset on an excel file ?

[Radu]

Hi,

I have the following problem: I open a recordset based on excel/csv
files, but then I need to filter (in code) in order to extract only
data pertaining to a specific person. This code is slow....

I am thinking that there might be another way to do it... maybe faster.
Is there a way to filter it as I open it ?

I'm using the following simple code:

Private Function Util_ReadTextFile(strDir As String, strFileName As
String) As DAO.Recordset

On Error GoTo Util_ReadTextFile_Error

Dim db As DAO.Database
Dim rstOpened As DAO.Recordset

Set db = DBEngine.Workspaces(0).OpenDatabase(strDir, False, False,
"Text;")
Set rstOpened = db.OpenRecordset(strFileName, dbOpenDynaset,
dbReadOnly)
Set Util_ReadTextFile = rstOpened

Util_ReadTextFile_Exit:
Exit Function

Util_ReadTextFile_Error:
MsgBox Err & ", " & Error, vbInformation
Resume Util_ReadTextFile_Exit
End Function

..... and then I use smth like

Set rstData = Util_ReadTextFile(....)
rstData.Filter = "PIN = ...."
Set rstDataFiltered = rstData.OpenRecordset

If there is a way, would it be any faster ?

Thank you !
Alex

[Tom van Stiphout]

On 6 May 2006 18:34:39 -0700, "Radu" <cu*************@yahoo.com>
wrote:

Not sure why you're opening Excel files as Text. I'm sure they're not
Excel files. Just try opening them in Notepad.

Sometimes it is faster to import the text file into an Access table,
set an index, and use that index to find or filter records.

-Tom.

Hi,

I have the following problem: I open a recordset based on excel/csv
files, but then I need to filter (in code) in order to extract only
data pertaining to a specific person. This code is slow....

I am thinking that there might be another way to do it... maybe faster.
Is there a way to filter it as I open it ?

I'm using the following simple code:

Private Function Util_ReadTextFile(strDir As String, strFileName As
String) As DAO.Recordset

On Error GoTo Util_ReadTextFile_Error

Dim db As DAO.Database
Dim rstOpened As DAO.Recordset

Set db = DBEngine.Workspaces(0).OpenDatabase(strDir, False, False,
"Text;")
Set rstOpened = db.OpenRecordset(strFileName, dbOpenDynaset,
dbReadOnly)
Set Util_ReadTextFile = rstOpened

Util_ReadTextFile_Exit:
Exit Function

Util_ReadTextFile_Error:
MsgBox Err & ", " & Error, vbInformation
Resume Util_ReadTextFile_Exit
End Function

.... and then I use smth like

Set rstData = Util_ReadTextFile(....)
rstData.Filter = "PIN = ...."
Set rstDataFiltered = rstData.OpenRecordset

If there is a way, would it be any faster ?

Thank you !
Alex

[rkc]

Radu wrote:

Hi,

I have the following problem: I open a recordset based on excel/csv
files, but then I need to filter (in code) in order to extract only
data pertaining to a specific person. This code is slow....

I am thinking that there might be another way to do it... maybe faster.
Is there a way to filter it as I open it ?

I'm using the following simple code:

Private Function Util_ReadTextFile(strDir As String, strFileName As
String) As DAO.Recordset

On Error GoTo Util_ReadTextFile_Error

Dim db As DAO.Database
Dim rstOpened As DAO.Recordset

Set db = DBEngine.Workspaces(0).OpenDatabase(strDir, False, False,
"Text;")
Set rstOpened = db.OpenRecordset(strFileName, dbOpenDynaset,
dbReadOnly)
Set Util_ReadTextFile = rstOpened

Util_ReadTextFile_Exit:
Exit Function

Util_ReadTextFile_Error:
MsgBox Err & ", " & Error, vbInformation
Resume Util_ReadTextFile_Exit
End Function

.... and then I use smth like

Set rstData = Util_ReadTextFile(....)
rstData.Filter = "PIN = ...."
Set rstDataFiltered = rstData.OpenRecordset

If there is a way, would it be any faster ?

Use a SELECT statement with a WHERE clause when opening the recordset.

sSelect = "SELECT * FROM " & strFileName _
& " WHERE PIN = 007"

Set rstOpened = db.OpenRecordset(sSelect, dbOpenDynaset, bReadOnly)

[Radu]

Hi. Thanks for answering

1. Tom, No, they are txt, or csv, or xls files - reading them has to be
done programatically, Notepad won't help :-))
2. RKC, I'll try that right now - I'll time it to see if there is any
improvement.

[Lyle Fairfield]

DAO.Recordsets are time-consuming and awkward. Witness here, where you
created 3 Recordset pointers, 2 of them to the same data and one to a
subset of it.

Possibly a much faster way is not to open a Recordset.

Of course, there may be some special reason that you need a Recordset.
Could you tell us what you do with the data after "Set
rstDataFiltered = rstData.OpenRecordset"? Perhaps there is an efficient
way to move the data from the text file to wherever you need it without
using the Recordset.

[Bob Quintal]

"Radu" <cu*************@yahoo.com> wrote in
news:11**********************@i39g2000cwa.googlegr oups.com:

Hi. Thanks for answering

1. Tom, No, they are txt, or csv, or xls files - reading them
has to be done programatically, Notepad won't help :-))
..txt and .csv files open perfectly in notepad. .xls is different,
but your code won't open those correctly.
2. RKC, I'll try that right now - I'll time it to see if there
is any improvement.

RKC's method would be faster.
--
Bob Quintal

PA is y I've altered my email address.

[David W. Fenton]

"Radu" <cu*************@yahoo.com> wrote in
news:11**********************@e56g2000cwe.googlegr oups.com:

If there is a way, would it be any faster ?

Why not just create a table link with TransferText and then you
wouldn't have to care that it's an external file, as it would work
just like any table.

However, note that if there are many rows, filtering could be quite
slow, as a full table scan will be required, since there are no
indexes. If it's a lot of data and filtering is slow, I'd just
import the darned thing and create an index on the filtered field.
The time it takes to do that will probably be much less than the
time it takes to filter the unindexed recordset.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Radu]

Hi.
I have tried with filtering - and it's even slower ! :-(

Why do I need to filter ? Well, let me explain: the data in each file
contains a lot of info pertaining to a list of PINs - say I have to
gather info for 1000 PINS, contained in an "Master" Excel file, let's
call it Z, and in input file "A" I *might* have info for PINS 2, 4, 77,
82..., for a total of 224 PINS. In other input file, say "B", I might
have data for PINS 22, 34, 36, etc, for a total of 162 PINS, and so on.

I need to access info for each PIN in my master list (Z) of 1000 PINs,
to process that data according to the business rules, and to push it in
some word docs.

So, besides Z, I also have a table T containing the list of input files
(A, B, C, etc). These files (A, B, C...) might be CSV, txt, XLS, etc. I
loop thru all 1000 PINs in Z, and for each one I open in turn each of
those files from T, filter them for the current PIN and I extract the
data for that PIN, if found.

I cannot import/link those files manually, because the list of input
files must by dynamic for each run (as it is (configurable by the user)
in the table T containing the list of input files).

The other idea is this - I will try to link A, B, C.... by code (by
browsing in T) and then do some left joins (on the left being the
master PIN file, Z) so that in the end I obtain some kind of flat file,
ready to be used as datasource for the word documents.

I still have other issues here pertaining to the flexibility - nothing,
but nothing, can be hardcoded - all files, including T and Z, must be
specified by the user, and they don't even have set field names - the
only constraint I will be able to set is that the first column in all
tables has to be the PIN column, although the name of that column has
to be variable as well). I might (I don't know yet, but knowing the
user I have to be prepared for anything) to also provide for calculated
fields, whose formulas shouls also be configurable at run-time....

Anyway, I wonder if, in the end, I will obtain a significant speed
improvement by using this method and avoiding looping, as I currently
do.

For now, what I have done, though, is that on startup I run a "Select
distinct" on my table T and then I read all input files and put them in
a pool, so as to avoid opening them repeatedly. This helped. Other than
that....

Thanks all for the kindness of answering.
Alex.

[David W. Fenton]

"Radu" <cu*************@yahoo.com> wrote in
news:11**********************@i40g2000cwc.googlegr oups.com:

I have tried with filtering - and it's even slower ! :-(
Then import into a table and add an index on the fields you're
selecting on.
Why do I need to filter ? Well, let me explain: the data in each
file contains a lot of info pertaining to a list of PINs - say I
have to gather info for 1000 PINS, contained in an "Master" Excel
file, let's call it Z, and in input file "A" I *might* have info
for PINS 2, 4, 77, 82..., for a total of 224 PINS. In other input
file, say "B", I might have data for PINS 22, 34, 36, etc, for a
total of 162 PINS, and so on.

I need to access info for each PIN in my master list (Z) of 1000
PINs, to process that data according to the business rules, and to
push it in some word docs.

So, besides Z, I also have a table T containing the list of input
files (A, B, C, etc). These files (A, B, C...) might be CSV, txt,
XLS, etc. I loop thru all 1000 PINs in Z, and for each one I open
in turn each of those files from T, filter them for the current
PIN and I extract the data for that PIN, if found.
Are these external files static? Even if they aren't, it would still
be faster to clear the last import from an existing table and import
the current version than it would be to filter on a direct link.
I cannot import/link those files manually, . . .
I wasn't suggesting a manual import.
. . . because the list of input
files must by dynamic for each run (as it is (configurable by the
user) in the table T containing the list of input files).
Well, surely the structure of the files is fixed, no? If so, then
you can program it, and allow the user to choose the file with a
file open dialog.
The other idea is this - I will try to link A, B, C.... by code
(by browsing in T) and then do some left joins (on the left being
the master PIN file, Z) so that in the end I obtain some kind of
flat file, ready to be used as datasource for the word documents.
With external files, that will be as slow as your filtering, since
there is no indexing.

It sounds to me like importing into tables with indexed fields is
the way to go.
I still have other issues here pertaining to the flexibility -
nothing, but nothing, can be hardcoded - all files, including T
and Z, must be specified by the user, and they don't even have set
field names - the only constraint I will be able to set is that
the first column in all tables has to be the PIN column, although
the name of that column has to be variable as well). I might (I
don't know yet, but knowing the user I have to be prepared for
anything) to also provide for calculated fields, whose formulas
shouls also be configurable at run-time....
You're saying that the files *don't* have a fixed structure?

Well, then, you're stuck with slow performance.

Sounds like the system you're working with was designed by an idiot.
Anyway, I wonder if, in the end, I will obtain a significant speed
improvement by using this method and avoiding looping, as I
currently do.
You won't gain any speed by linking. The cause of the performance
slowdown is always the same -- you'd be joining or filtering on data
sources that have no indexes.
For now, what I have done, though, is that on startup I run a
"Select distinct" on my table T and then I read all input files
and put them in a pool, so as to avoid opening them repeatedly.
This helped. Other than that....

Why in the world is your Access app dependent on text data? Is there
no direct route for getting the information?

Of course, I'm assuming from what you've said that the external file
layouts are variable. If they aren't, then importing is the way to
go.

If they aren't, I'd look into trying to get someone to re-engineer
the external dependencies, since they seem to have not been designed
to make it possible to do what you need efficiently.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/