Importing txt file

[rgw]

I've always used the filetostr() function to convert txt files to a dbf file. Never had to convert more than a thousand records and it only takes a few seconds. Results have always been as expected. May now have to do daily imports of fixed length text files--30,000 records and 10mb in size. The filetostr() function still handles it but it takes over 10 minutes to import. I need to have the import process controlled from a menu for the users to control--any suggestions for speeding up the import function would be appreciated.

many thanks
rgw

 

[craigsboyd]

Look into VFP's low-level file functions. They'll do the job about as fast as can be done and also will allow you to read into memory a chunk of bytes at a time instead of the whole thing which will keep the footprint significantly smaller during the process.

FOPEN(), FREAD(), and FCLOSE() are the main ones you'll wanna look at. If you look up "FREAD( ) function" in the VFP Help file index you'll find a pretty good example of the code needed as well.

SweetPotato Software Website
My Blog

 

[craigsboyd]

Alsom depending on what kind of text file you are dealing with you might want to have a look at the APPEND FROM command in VFP.

SweetPotato Software Website
My Blog

 

[rgw]

Craig

Many thanks. Perhaps illogically, already use the low-level file functions creating the txt file going out to the bank and it has worked fine but use the filetostr() command the other way. I tried to see how long it would take the import wizard to do the job as a benchmark but I was ready to reach for the scissors trying to separate the string into the column breaks--easy enough when there are a few columns but 44 in a string of 226 characters was driving my not so young eyes crazy. That wizard could be improved if it would just prompt you for the column break numbers as an alternative to the presumably superior visual approach.

It may be just me but I always get an error when I cut and paste the fgets() help code into a little file. I change the name of the test.txt to my file name and it tells me I am returning an improper value etc.

Once again, many thanks
rgw

 

[DSummZZZ]

One thing that will speed up your process no matter whether you use FileToStr(), Fread() or even APPEND FROM is to open your tabel exclusively if your able. If not, maybe a temp table opened exclusive for the import, then append to the live table shared.

But, as Craig alluded with the APPEND FROM, I think you should take the time to create the correctly structured table. It would save you much grief and time. Especially if the record layout ever changes.


-Dave Summers-

Even more Fox stuff at:

http://www.davesummers.net/foxprolinks.htm

 

[rgw]

All

Rewrote it with just an fgets(). Imported the 28000+ records into the 44 fields in less than 4 seconds!

many thanks
rgw

 

[justamistere]

Cheers for all VFP's low level functions.

FCreate( ), FOpen( ), FSeek( ), FEof( ), FRead( ), FWrite( ), FGetS( ), FPutS( ), FClose( ), FFlush( ), FCHSize( )

I did the opposite, export a non-indexed file of almost a million records and 75 fields into 52 .csv text files all with header-lines, in just 2.35 minutes.

Maybe someone should start a "2006 Records in a FoxPro World" thread. This would inform newcomers to VFP's capabilities.