Subject Re: character string problem
From Charlie <>
Date Mon, 18 Mar 2019 15:52:31 -0400
Newsgroups dbase.getting-started

Hi Mervyn.. OK thanks very much for your help.  I really appreciate it.  I think this is now under control.

I am having one problem that I think I will have to figure out by myself.  If I go into mysql (mariadb) and import from there, I get around 3000 more records than if I update using the php program I developed.  No idea if it is additional records that aren't just perfect or possibly my code.

I'm just happy that it is now functional!  Three days ago I wasn't able to upload anything to the remote server, so I am happy.  I also won money today on the golf course.  Really good day! :)

Mervyn Bick Wrote:

> 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()
> 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