Subject Re: character string problem
From Charlie <tm@tc.com>
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()
 > 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