I have just realized that several thousand records that I imported from Q&A into Sesame have errors. The error was made on the Q&A ASCII export. The inch symbol " was replaced by the foot symbol ' so I have things such as 94' x 31'6'... instead of 94" x 31'6"...

If I delete the incorrect records and do the import again, I will lose any changes that have been made. Right now, I am trying to determine if I want to take this risk.
If I do decide to do this all over again, how do I get Q&A to export the " correctly?

Glenn

But, when Sesame exports, it exports numbers with extra zeros, so $150.00 comes out as $150.000000. That causes more problems than the " coming out as '.

Before the translation you MUST remove ALL double quotes or the CSV records will get screwed up.  That is a CSV "thing", not a QA or Sesame "thing". Just as you can't use slashes or colons in filenames, the double-quote character ASCII 34 is an "illegal" character in CSV files because it is used as the CSV field delimiter.

This can be done using a Mass Update for each field that contains the double-quote character.

For each QA field that needs to be sanitized use this as the update spec: #1=@Replace(#1,@chr(34),@chr(96))

The number after the number sign must be different for each field.  That will replace all double-quote characters with a back-apostrophe (the one under the tilde on a U.S. keyboard), which is normally unused.  If you really want to keep the double-quotes, you can run the same update in reverse in Sesame. It probably would be better, however, to replace the back-apostrophes with two regular apostrophes.

Then translate to Sesame.

Thanks for clarifying that.  I had copied (and modified it somewhat) from a thread about exporting text fields from QA and keeping blank lines (which isn't possible it seems).

The command is basically the same in Sesame except that you don't use # with a number, you use the field name.

Actually, on the Sesame side it's quite easy.  In the global spec for the Mass Update you set up the code for one field (use the field name instead of #1.)  Then you just copy and paste the code for each of the other fields and change the name of the field to be fixed each time.  Then one Mass Update will fix all the fields.

magicfish wrote on Sep 14^th, 2011 at 8:19pm:


Hello,

Well since you've gone this route, the Mass update in Sesame is the same syntax, as Rick R pointed out, with the exception of numbering. In the Mass Update spec choose any element other than Global Code(This event only runs once per Mass Update), and put all your programming into that element's Mass Update event. Such as

Width = @Replace(Width,@chr(96),@chr(34))
Length = @Replace(Length,@chr(96),@chr(34))
Height = @Replace(Height,@chr(96),@chr(34))  

-Ray