I want to update a member data file from a .CSV and rather than reading in and comparing records in 2 .TPS files, I will backup the original files by renaming them to filename.BAK.
What’s more efficient?
and then refiling the 2 original files with the data from the CSV file
Hope that makes sense.
Mark
Yes I agree with what Sean said
Also if you have lots of records and the tps files have keys, it may be faster to APPEND then do a BUILD at the end rather than ADDing records which updates the keys after each record is added to the file. See the help for details.
BTW were you using the Basic driver for reading the CSV or using StringTheory instead?
Or perhaps 3. No copying, no emptying, just overwriting existing records? True, you’d need to take care of old records not being overwritten due to shorter csv compared to previous one.
I don’t know if this would be the fastest or most efficient though, it’s just an idea…
Also worth noting: If these are large data files you might get a noticeable speed improvement in your reports if the tables are built in the key order that your reports use, assuming there is one particular order used on the reports. Of course, if you are using SSD drives then it probably does not matter. I just know when we have re-created a child table of several hundred thousand records in the order used by reports that customers with traditional hard drives have noticed a big speed improvement for subsequent reports. P.S. We’ve been converting customers from “on-premise” to hosted (cloud) solutions under RDP. Reports that took 40 minutes to run on their LAN take about a minute to run on an Azure server using .TPS.
I would go with this as fastest and least disk work.
Since 2 files are involved I would want to insure they BOTH got done, or neither. AFAIK there is no way to put the Rename in a Transaction.
I would use 2 DOS files to Open Both these files Exclusively e.g. OPEN(file, DenyALL + ReadOnly).
If that fails then I can show a Message(‘Users are active, you cannot update’) and abort. I use a DOS file as a very simple driver to Lock the file with Deny All. The TPS driver is too complex with too many features, e.g. multi-thread exclusive access.
If it works then I can Close the DOS file locks and do the Rename()s.
There is a small window of time for a user to open the files and cause failure. If you want to be bullet proof you have to deal with it.
If the 1st file Rename() fails that’s easy. I can show a Message and Abort.
If the 2nd file Rename() fails that’s messy. Must rename File 1 back then show a Message and Abort. The user’s actions may have created an empty File 1 that will prevent rename.
Thanks all!!
Carl, I’m not sure what you mean when you say you use a DOS file. I assume this is the same as the CSV file that I am importing info from - CW uses a BASIC file driver for that file.
I should have mentioned that these files contain a very small number of records - right now it is at about 130 records (with 10 simple fields) and could grow to about 500 or so after a few years, but that is still small so far as CW is concerned.
I meant to define a FILE using the Clarion DOS File Driver. Use that DOS file to OPEN(,DenyAll+ReadOnly) your TPS file by setting the NAME() variable. You will have to have the TPS file closed.
OpenThisFile FILE,DRIVER('DOS'),NAME(OpenThisFileName),PRE(OpenA)
Record RECORD,PRE()
Block STRING(1024) !OpenA:Block
END
END
...
CLOSE(OpenThisFile)
OpenThisFileName = NAME(TpsFileofYours)
CLOSE(TpsFileofYours)
OPEN(OpenThisFile,ReadOnly + DenyALL)
IF ERRORCODE() THEN
Message('Cannot import the File XYZ is in use.','import')
...
I wrote this little utility that allows opening any File using any Modes so I could test my code error handling of locked files. This uses a DOS file to do the Open. Look at this source.
Thanks again Carl! I will have to digest this all.
