Treating text files as database

[GFrantsen]

I need to know how to do this. I want to be able to use SQL to update a text file, for example changing every occurance of "Chuck" to "Charles". Is this possible?
How do you do it?
I know SQL VERY well, but I've never done the text file thing. I need to know specifics.

Thanks.
Greg.

 

[mmilan]

I suspect it's my posting that started this merry line of discovery...

What I was referring to was using ADO with a text driver, to play with comma seperated values. You can, however, get awway with treating each line as just one field, which is where I was steering the person I was talking too...

You might be more interested to learn about regular expressions... I've only touched them (lightly) whilst looking at Perl, but I imagine VB might support them. If not, you'll certainly be able to download a library from somewhere...

martin

 

[CCLINT]

If the file is in a delimited format, then you can use the ISAM Text driver to build a connection to the text file, and then either use an action query to update the file, or use a recordset object. With the latter, you would either set the criteria when opening the rs, or use the FIND/FILTER methods once the recordset is opened.

This method has been around a long, long time, and I have used it successfully. There are many threads concerning the subject. I have always found it best to use a Schema.INI to define what the text file structure is. This prevents errors when using it internationally, or when the format is different from the standard formats set in the Windows registry.

If you can, use DAO 3.51. It is most efficient with TEXT files, or any ISAM format.
The next best choice is DAO 3.6.
Then comes ADO.

Here are a few threads to get you started:

thread222-371928 thread709-334828 thread222-378000

However, there is another method, which is under ADO, which may be more effective if you want to build a recordset instead of using an action query, I have used this also for certain circumstances and it is also fast: Using a recordset object not coonected to a data source, but built from scratch (easier than what it sounds) and then using the traditional file OPEN to loop through the records and add them to the recordset.

You will find a recent discussion on the speed and different methods under this: thread222-381120

Of course, this thread also points out that if you do not need a recordset object, just using the traditional method for your purpose is probably the fastest. [/b][/i][/u][sub]*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!