Subject Re: character string problem
From Mervyn Bick <invalid@invalid.invald>
Date Mon, 18 Mar 2019 10:11:56 +0200
Newsgroups dbase.getting-started
Attachment(s) create_tmaster_csv2.prg

On 2019-03-17 9:59 PM, Charlie wrote:
> Hi Mervyn... I do see the new file escaping.  I saw only one double (\"\").  But there are two problems probably unexpected by you.  The first is preventing me from doing much with this.
>
> The result is supposed to be indexed on part_no.  I don\'t know if you can append from another file by index?  Or am I wrong?  That can probably get worked around.
>
> The other problem is that tmaster.dbf even though it is dealt with at the beginning of the file is a problem.  Before I can run the program again I have to maually delete tmaster.dbf every time I run it.
>
> BTW you might have sent an old file in one of your posts as the escape code was  not in the one I originally looked at.

Oops. Sorry about that. I missed the fact that you had set the index on
master.dbf before you started moving data into tmaster.dbf

The attached program creates a temporary file tmaster.dbf ordered on
part_no with data extracted from master.dbf where asell <> 0 and title
is not null and part_no < 'AF00760X'   Any double quotes in the part_no
and au_desc fields are "escaped" with \ characters.

tmaster.dbf is deleted when the program is executed to make way for the
new file. tmaster.dbf is, however, available for inspection after
master.csv has been created.

The data in tmaster.dbf is written out to master.csv as a comma
separated file with the text fields delimited with double quotes.

The program does not take care of any missing or corrupt au_desc fields.
  These will need to be corrected in master.dbf.

Mervyn.




if file('master.csv')
   new file().delete('master.csv')
endif

if file('tmaster.dbf')
  drop table tmaster
endif

select cast(0 as int) as id,part_no,title,qty,cast(sell as int),;
   grade,mfg,cuml,sdate, cast(asell as int),au_desc from master ;
   where asell <> 0 and title is not null and part_no < 'AF00760X' ;
   order by part_no save to tmaster

use tmaster
scan
  replace part_no with escape_quote(part_no)
  replace au_desc with escape_quote(au_desc)
  //add any other character fields here if necessary
  replace id with recno()
endscan
go bottom
msgbox( "Count is " +id+ ".  The master.csv file must be ftped to the correct directory before importing into mysql." )
copy to master.csv delim
use

cFileIn ='master.csv'
cFileOut = 'tempworkfile.txt'
fIn = new file()
fIn.open(cFileIn)
fOut = new file()
fOut.create(cFileOut)
do while not fIn.eof()
   cRead = fIn.gets()
   If  not chr(26)$cRead
      fOut.Puts(cRead)
   endif
enddo
fIn.close()
fOut.close()
new file().delete(cFileIn)
new file().rename(cFileOut,cFileIn)
msgbox('Done. master.csv has been created.')

function escape_quote(cStr)
   for n = len(cStr) to 1 step -1
      if substr(cStr,n,1) = ["]
         cStr = stuff(cStr,n,1,[\"])
      endif
   next
   return cStr