Subject Re: character string problem
From Charlie <tm@tc.com>
Date Sun, 17 Mar 2019 09:54:41 -0400
Newsgroups dbase.getting-started

Hi Mervyn.. It is possible that I have some single quotes but probably not as many.  Although sometimes they describe  passenger cars by feet (') also.  I was wondering if you could use ^ or |  to enclose.  I think I have seen them before.  Although I believe they replaced the comma.  Isn't the comma the delimiter and ( ") the enclosure?  I'm confused.  If you use copy to file with ^ it changes the comma.

I believe the program you attached is not complete.  It seems to end abruptly.

Thanks much!!!

Mervyn Bick Wrote:

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