Subject Re: character string problem
From Mervyn Bick <invalid@invalid.invald>
Date Sun, 17 Mar 2019 10:35:14 +0200
Newsgroups dbase.getting-started
Attachment(s) create_tmaster_csv1.prg

On 2019-03-17 9:38 AM, Charlie wrote:
> Oh boy.. I had meant to paste the line in but must have forgotten to.
>
> I am at the point where I was able to get the entire table imported after around a half hour of deleting double quotes.
>
> But  the code you gave me is working as there are fewer instances.
>
> It seems like the problem happened mainly at the very end of the row of the field.  It happened much more in the title field than the au_desc row.
>
> It made no sense to me as it looked like this (from memory):
>
> "blah blah blah\""
>
> I would have to delete one of the double quotes and it was OK.  Most of the rows were corrected and looked like this i think:
>
> "blah blah blah\"
>
> So  I'm not sure if i am supposed to be able to see the \.
>
> I do plan on updating the page fairly frequently so it would be nice to find a resolve.  Now that it works I'd like to leave it there for a while for testing but I may well go through the correction process again to try to figure out more of what is happening if needed.
>
> So if you would like an idea of what it looks like it is at traincity.com/guide.php and is working although isn't finished yet.

It depends on exactly how you are uploading the .csv file to MySql but,
speaking generally, when you upload a string literal to a SQL server any
quotes in the literal that are also used to delimit the literal must be
"escaped".   So if, say, you have   Lionel 18" Train set.   in a field
it must be written to the .csv file as  ...,"Lionel 18\" Train
set.",.... if you are using double quotes to delimit string literals.

If you have, say,   Lionel 18" Train set "Anniversary" model.   in a
field the .csv entry should look like  ...,"Lionel 18\" Train set
\"Anniversary\" model.",...


If you change the delimiter for character fields in the .csv file to
single quotes you shouldn't need to "escape" the " whether it is used to
indicate inches or not.  MySQL should be quite happy to accept a .csv
file delimited with single quotes i.e ...,'Lionel 18" Train set
"Anniversary" model.',....

Using a single quotes as delimiter will also automatically take care of
any part_no values which contain double quotes. It does mean, of course,
that any apostrophes in the text will need to be escaped.  You can't win
them all. :-)

Try the revised program attached if you don't have any apostrophe's in
any of your text fields.

Mervyn.






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

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

create table tmaster (;
id numeric(11), ;
part_no char(10), ;
title char(35), ;
qty numeric(6,0), ;
sell numeric(7), ;
grade char(16), ;
mfg char(35), ;
cuml numeric(5,0), ;
sdate date, ;
asell numeric(7), ;
au_desc char(254))

use tmaster
append from master for asell # 0 and title # null and part_no < "AF00760X"
go bottom
msgbox( "Count is " +id+ ".  The master.csv file must be ftped to the correct directory before importing into mysql." )
copy to tmaster.csv delim
use

cFileIn ='tmaster.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)