• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1949
  • Last Modified:

using foxpro2.5, append text with pipe delimited records

I am using foxpro2.5 for dos. I want to append a dbf file with text file having pipe '|' as field delimiter. How to append text file into dbf file with pipe delimited records.
0
karunamoorthy
Asked:
karunamoorthy
  • 6
  • 5
  • 2
  • +1
3 Solutions
 
CaptainCyrilCommented:
APPEND FROM test.txt DELIMITED WITH CHARACTER |
0
 
karunamoorthyAuthor Commented:
My table is ename c(25), empno c(6)

My data is

PKM|123456
KSBKMM|234568
KMM|789456

I have tried APPEND FROM test.txt DELIMITED WITH CHARACTER |
but could not succeed!
0
 
pcelbaCommented:
Above command does not work in FoxPro 2.5

The right syntax should be
APPEND FROM test.txt DELIMITED WITH |
but it does not work...

The solution is to replace all pipe occurences by TAB characters and append it by
APPEND FROM test.txt DELIMITED WITH TAB

The replacement is easy in FoxPro editor TAB character is represented by \t  sequence
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
CaptainCyrilCommented:
Or you can read the file using a low level function:

FOPEN
FREAD
FCLOSE
0
 
Olaf DoschkeSoftware DeveloperCommented:
You could also import the file in excel and then export as something ou can append in fp 2.5

Bye, Olaf.
0
 
karunamoorthyAuthor Commented:
>>CaptainCyril,
You idea is ok but I am interested to know any easy single line command like append available so that I can avoid bulk coding.

>>Olaf_Doschke,
Your idea of uploading to excel file and then exporting to fp file is only process I am doing right now all along. Actually I am getting the file from some other sources which is to be processed further by me to get the correct format and also to check data validation and ensure correcness. Hence I am interested in automating the process without user intervention. Excel import and then export to fp 2.5, I feel manual intervention is required in that process.
0
 
karunamoorthyAuthor Commented:
>>pcelba,
Workaround given is ok for me to some extent. But now the problem is I have to search '|" and replace with \t is a problem for me.
0
 
CaptainCyrilCommented:
Unfortunately the 2.5 and 2.6 versions of FoxPro on my laptop do not run because my laptop is 64-bit. The command I gave you works perfectly with VFP 9.2.

Half of my imports in 2.5 and 2.6 were using FOPEN since my software used to read exports from many accounting software.
0
 
karunamoorthyAuthor Commented:
>>CaptainCyril,
Any sample coding to use FOPEN for this, may find helpful to me.
0
 
CaptainCyrilCommented:
hFile = FOPEN(cFileMap, 0)
IF hFile < 0
      = MESSAGEBOX("Could not open file!", 48, appname)
      lOK = .F.
      RETURN
ENDIF
nFileSize = FSEEK(hFile,0,2)
= FSEEK(hFile,0,0)
nBytesRead = 0
DO WHILE NOT FEOF(hFile)
      cLine = FGETS(hFile)
        ? cLine
      nBytesRead = nBytesRead + LEN(cLine) + 2
      oThermometer.update(nBytesRead/nFileSize*100) && this line calls my thermometer progress bar to display estimates to finish
ENDDO
= FCLOSE(hFile)
0
 
karunamoorthyAuthor Commented:
>>CaptainCyril,
Using the above code, I can able to read text file line by line. Till now, it is ok for me.
But again how to load the text file which is read already into dbf file.
The real problem I face is I have a quite large number of fields in the dbf file.
Right now I am using like this,


First I append the text file using
APPE FROM TEST1.TXT sdf
here the first field I am using is say F0 with 254 chrs and then  the real fields which I require (for example
empno, ename etc)
Now, I am filling the empno field like
REPL ALL empno WITH substr(f0,1,atc("|",f0)-1)
REPL ALL ename WITH substr(f0,atc("|",f0,1)+1,atc("|",f0,2)-atc("|",f0,1)-1)
...
...
...
etc

Any suggestion please.
0
 
pcelbaCommented:
To replace | character by TAB is very easy in FoxPro 2.5 Simply open the file by

MODIFY FILE YourFile.Ext

Then press CTRL+F enter the | character into the Find box and \t character into the Replace box. Then select Replace All and save the file into new location by Save As option from File menu.

Now you may import this new file by one single command as described in my previous answer.

I would strictly recommend to test the Find/Replace capability on some small test file. The Find/Replace dialog is also described in FoxPro help.
0
 
karunamoorthyAuthor Commented:
>>pcelba,
I have already tried find/replace option. It is working fine. It is ok for me.

The real problem is the text file is coming daily and I have to do the file manipulation on daily basis. My idea is to automate the process and no manual user intervention is required. After making prg, I convert the same into exe file and put it in a scheduler, so that automation can happen.
0
 
CaptainCyrilCommented:
Use the FOPEN, FREAD to change all the "|" to TABs.

hFile = FOPEN(cFileMap, 0)
hFile1 = FCREATE('tab.txt')
IF hFile < 0
      = MESSAGEBOX("Could not open file!", 48, appname)
      lOK = .F.
      RETURN
ENDIF
nFileSize = FSEEK(hFile,0,2)
= FSEEK(hFile,0,0)
nBytesRead = 0
DO WHILE NOT FEOF(hFile)
      = FPUTS(hFile1,STRTRAN(FGETS(hFile),'|',CHR(9)))
ENDDO
= FCLOSE(hFile)
= FCLOSE(hFile1)

Then read from the new file
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 5
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now