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