If you are getting characters that will cause you to throw errors on import then you will need to run a filter on the export file to filter out any obscure characters, similar to how we filter for email validation. What is giving the smart quotes? When you export it from FMP?
Our products have an "item name" column and "item descriptions". Sometimes we use an apostrophe to indicate feet. EG: 8' long wood post. So, we use a script (with the "export records" function) to export a CSV file (from filemaker) and we then import that file into the feed-perfect system and feed-perfect then sends the product data to Google and elsewhere. After those conversions, the same description comes across as "8â€™ long wood post" and not "8' long wood post".
Any specifics on fixing this would be greatly appreciated. I tried a find and replace scrip to replace all smart quotes with straight quotes and it didn't help.
Only thing I could think of doing right now is installing some UNIX tools for windows so you can make a bash script or Perl script to strip out all the quotes in the file...will think about this one for you. Phil may chime in with a handy way to do it.
In searching online, I've found a possible explanation for this issue:
In short, the 3-byte unicode character ’ is being interpreted in the wrong encoding (Windows-1252). Does the CSV file appear correct if you open it in notepad? It may be that FeedPerfect doesn't properly support unicode.
The CSV export on my Windows XP system seems to properly display the character even if I don't choose UTF-8 in the Specify Field Order for Export dialog. If I choose to open the file in notepad explicitly using ANSI it does display â€™ instead of ’. If the CSV file is correctly encoded and you suspect the issue is with FeedPerfect, it may be possible to replace unicode characters with ASCII characters using a shell script as rjlevesque suggested.
> Does the CSV file appear correct if you open it in notepad?
Yes, it appears properly when opened in a basic text editor. We use mac, so i open it in bbEdit.
We had a field named "description". I added this calculation field (called "filtered description") to my filemaker database. The calculation is...
Substitute (description ; [Char ( 8216 ) ; Char ( 39 ) ];[Char ( 8217 ) ; Char ( 39 ) ]; [Char ( 8220 ) ; Char ( 34 ) ];[Char ( 8221 ) ; Char ( 34 ) ] )
I got these codes for each unicode character that was causing issues. So, I'm replacing left and right "smart" double-quotes with the generic double quotes and I'm replacing left and right "smart" single-quotes with the generic apostrophe.
So, we replace these two....
with this one….
Then, we're replace these two....
with this one….
I've done a new export using these filtered fields. I'm waiting for Google to pick-up the feed so I can see if it worked. Sometimes it takes an hour if their servers are busy.
What version of Mac do you work with? Is it one of the newer OS-X versions?
If so that is excellent news! You have Bash Shell on that system if it is and you can write a nice bash script that uses SED to strip out all the double quotes if need be.
Example using sed:
sed s/\"//g yourfile.csv
This of course would be done from a terminal window. If you decide to go this route and need a little more help with it just let me know.