Foxpro 2.6 (ye olde DOS) APPEND FORM file TYPE SDF

[sunwindandsea]

I import a text file named myfile.dat that will always contains 1 line of text. These are EDI data file in th X12 format. The 1 line of text can be 256 characters long or more than 2000 charatcers in length.

When the file is imported into a db file in a current app it is parsed into separate lines by a “~”.

Sejksejefj*kmkfjfsf*dgkbfkjkj*jgkgjz~
fjjtjtjtjtjtj*sjsjg588839583w9*sk~
Sejksejefj*kmkfjfsf*dgkbfkjkj*jgkgjz~
fjjtjtjtjtjtj*sjsjg588839583w9*sk~
...~
...~
...~

I append these text files into a database called mydb.dbf with 1 field whose length is 175 characters.

I have been using the following code to create a database file

cSource = "myfile.dat"
SELE A
USE F:\mydb ALIAS mydb EXCLUSIVE
ZAP
APPEND FROM &cSource TYPE SDF

In other programs that I have used this code I get the desired dbf file, that is, a new record is created in mydb after each “~”

I copied this code to a new program file and complied the program file and the result was a database file with only 1 line.

Sejksejefj*kmkfjfsf*dgkbfkjkj*jgkgjz~fjjtjtjtjtjtj*sjsjg588839583w9*sk~Sejksejefj*kmkfjfsf*dgkbfkjkj*jgkgjz~fjjtjtjtjtjtj*sjsjg588839583w9*sk~...~ (Up to 175 in length)

I have forgotten something. Many of my other programs use the above code with no problem. It has been quite a while since I worked in Foxpro 2.6 for DOS (3-4 years.) I have examined, studied, reviewed and driven myself crazy looking at one of the shorter apps using the above code and I cannot comprehend why the code creates the database file with only 1 line.

Thanks, Ed

 

[tesar]

Ed,
to use "sdf" type,
each row must ended with CRLF characters.
If you want separate record with ~,
use low level command (fopen, fread, fclose)
Tesar

 

[cricket]

You could also have the destination database (mydest.dbf) set up with the same structure as the incoming data, or at least a large character field corresponding to each incoming field (each incoming field is the data between the ~.

The appropriate syntax is APPEND FROM mysource DELIM WITH '~'. The source file extention must be included if it is other than .TXT

I prefer to achieve results with this type of requirement by importing into Excel and writing the database file from Excel, especially for infequent items.

 

[sunwindandsea]

Thanks for the reply Cricket & tesar

Following up on Cricket's response, I created the following prg.

SELE A
USE PO_TEMP ALIAS POTEMP EXCLUSIVE
cSource = "MYFILE.DAT"

SELE A
APPEND FROM &cSource DELIM WITH '~'

The prg compiles with no errors but when run I receive the following error message when the last line:

APPEND FROM &cSource DELIM WITH '~'

is executed "Unrecognized phrase/keywrod in command."

what I am doing wrong? Don't forget the text files can contain 2000 or 3000 characters.


Thanks for the help

Ed

 

[TheRambler]

Try without the single quotes:

APPEND FROM &cSource DELIM WITH ~

 

[sunwindandsea]

Rambler thnak you for your suggstion, it works for some larger files but not other files. I have the code shown below is another app and it works perfectly everytime.

cSource = cSource + ".DAT"
SELE H
ZAP
APPEND FROM &cSource TYPE SDF

RETURN

A new record is created after each tilde. Remember the dbf file has one field whose length is 160 characters long. THe lenghth of the functional acknowledgement file is always 263 characters.

Thanks your for the suggestions but the problem remains,

It's weird.

Ed

 

[TheRambler]

As Tesar suggests, I guess the problem is caused by missing carriage returns and/or line feeds in your source file after each tilde.

Regardless of that, try the following code:

lnHandle = fopen("myFile.dat")
lcString = fgets(lnHandle)
= fclose(lnHandle)
DIMENSION myArray[1]
lnRows = Split(@myArray, lcString, '~' )

create dbf myDbf (myField c(175))
for i = 1 to lnRows - 1
    insert into myDbf values (myArray(i))
endfor

select myDbf
browse

FUNCTION Split
PARAMETERS paArr, pcLine, pcDiv
PRIVATE lnLen, lnWid, lnCnt, lnNum, lnStrt, lnPos, lcSub
  lnLen = aLen( paArr, 1 ) && Number of rows
  lnWid = aLen( paArr, 2 ) && Number of cols
  
  lnCnt = OCCURS( pcDiv, pcLine+pcDiv)
  if lnLen < lnCnt
    if lnWid > 0
      DIMENSION paArr[ lnCnt, lnWid ]
    else
      DIMENSION paArr[ lnCnt ]
    endif
  endif
  lnStrt = 1
  lnNum  = 0
  do While atc( pcDiv, pcLine+pcDiv, lnNum+1 ) > 0
    lnNum = lnNum + 1
    lnPos = atc( pcDiv, pcLine+pcDiv, lnNum )
    if lnStrt > len(pcLine) && 9/23/99 wgcs
      lcSub = ''
    else
      lcSub = Substr( pcLine, lnStrt, lnPos-lnStrt )
    endif
    lnStrt = lnPos + len(pcDiv)
    if lnWid > 0
      paArr[ lnNum, 1 ] = lcSub
    else
      paArr[ lnNum ] = lcSub
    endif
  enddo
    
RETURN lnCnt

Looks like a lot of work, but most of it was done by wgcs, see faq182-6039
How to SPLIT a String (ALINES) into an array?

 

[mmerlinn]

A variation of Rambler's code could be something like this:

[tt]
lnHandle = FOPEN("myFile.dat")
lcString = FGETS(lnHandle)
= FCLOSE(lnHandle)

cSeparator = "~"
lnRows = 0
DO WHILE NOT EMPTY(lcString)
lnRows = lnRows + 1
DIMENSION myArray(lnRows)
myArray(lnRows) = ATXLEFT(lcString, cSeparator)
lcString = ATXRIGHT(lcString, cSeparator)
ENDDO

CREATE DBF myDbf (myField c(175))
FOR i = 1 to lnRows
INSERT INTO myDbf VALUES (myArray(i))
ENDFOR

SELECT myDbf
BROWSE[/tt]

The code for ATXLEFT() and ATXRIGHT() can be found in faq184-5975, "ATXLEFT() & ATXRIGHT(): Two useful UDFs for parsing strings."

One thing to watch out for. One of the low level functions (don't remember which) defaults to around 700 characters per line unless a different value is specified. If you are importing 2000 characters, and you are not specifically specifying the incoming maximum character count, only part of your line will be loaded into your variable.


mmerlinn

"Political correctness is the BADGE of a COWARD!"

 

[sunwindandsea]

Thank you all very much for your solutions, th solution that implemented is below:

The files are always 263 characters long.
The five character string is located at position 184.
I used my code to import the text file into a database file and extracted the character sting to a variable and...

I'm explaining this to inform other users who might experience the same problem that if the character string that needs to be determined and if the end of the string is at or before position 254 is the above is the simplest solution that I know.

Otherwise TheRambler at 10 Nov 06 16:35 and mmerlinn at 11 Nov 06 2:17 responses will work

Rambler and MMerliino thank you very much for the time each of you gave to me and this site to solve the problem.

Thanks again,

Ed

 

[NasibKalsi]

Tha fact that in your append statement there is no DELIMI parameter. This shows that your original .dat file has proper CR/LF. So somethis is missing. May have been modified during transfer between operating systems, etc.


However, a modified code from above mmerlinn, TheRamble, plus may be usable.

lnHandle = FOPEN("myFile.dat",2)
lcString = FGETS(lnHandle)
lcNewString = CHRTRAN(lcString,"~", chr(10)+chr(13))
= FPUTS(lnHandle,lcNewString)
= FCLOSE(lnHandle)


At this point you can use your original code.


OR
you can change the structure of the table to have maximum number of fields(chk limit) such as F11, F12, ... F99

and address them as

i=11
ic = str(i,2)
? f&ic

Nasib Kalsi