Have you tried wrapping the entire field content in the Quote ( ) function? That should help within FM, but I'm not sure what it would produce in an export file. Can't hurt to try though.
CSV are typical. I import several CSVs that require substantial pre-processing. One of those CSV file has high-order ASCII characters crashing the import into MySQL, etc. Another file has 5 pre-import workflow steps outside of (in my case, MySQL) before it will import with no problems.
In any case, since it sounds like you got the file imported, but with formatting issues, would it be feasible to fix the import after the fact in FMP using a script?
Otherwise, I would document exactly what in the file needs to change and either:
1. Ask the originating party to make the file consistent (never happens in my experience, but worth a try), or
2. Pre-process the file using a language that can open read/write files directly.
HOPE THIS HELPS.
As I read it, the issue Chuck is having is with generating csv export files.
Yep, looks like you're right.
In that case, when you pre-process the data in FMP, why not just substitute the quote character with another character that wouldn't confuse the CSV export. Maybe use a pipe character. Then, later, change the pipe characters back to a single or double quote or whatever makes sense.
Chuck, if this is for Amazon, would converting quotes to ( & quot ; ) - remove the spaces, help?
or changing " to 'inches' ?
Its not the quotes it is what FileMaker does with a quote when it exports it as CSV, double the quote. See my notes above.
I read all the notes. I'm saying that regardless, if you convert your quote-in-field to something that is not the double-quote as FileMaker uses for text or for export to CSV, then you should have NO problem. FileMaker will NOT convert (or double) the 'smart' quotes like it does the plain quotes.
I'm also asking if you tried the
Substitute ( myField ; Char(34) ; Char(8221) )
to see if your CSV (comma-separated, double-quoted text) is OK when sent to Amazon.
I think you want a document that behaves like the enclosed file when it is opened with a spreadsheet application.
This is how I created it from your document:
1) Take your document ( Amazon Parents.csv ) insert into a container.
2) Export Field Content of the container to the Temp folder.
3) Insert from URL the content of the Temp folder to a Text field.
4) Edit the content of the Text field as follows, in this order:
a) Substitute the “comma and space” [ Char(44) & Char(32) ] with a “double asterisk” [ Char(42) & Char(42) ].
b) Substitute the “commas” [ Char(44) ] with a “tab” [ Char(9) ].
c) Substitute the “double asterisk” [ Char(42) & Char(42) ] back to “comma and space” [ Char(44) & Char(32) ].
5) Export Field Contents of the Text field. Will probably have a .txt extension.
6) After export, change the extension to .csv.
This file should open properly with Excel or Numbers (OS X). Though you do have lots of empty columns in your original document.
NOTE: It is likely from your FM file you can export records directly to the Temp folder. Then proceed from step 3) above.
Here is the solution to create a comma delimited file in a calculation.
Delimit each field with Quote(",")
Add a "\"" to the beginning and end of each record to close the quote around the first and last field.
Export as .tab and change the suffix to .csv and it opens in a spreadsheet delimited as CSV. Tada!
Thanks for everyone's input!
1 of 1 people found this helpful
One additional magic trick you have to do if you have quotes and commas in a text field is to have a .csv version of your field converting single quotes to double quotes using the substitution command. For example,
Description .csv = Substitute ( Description ; "\"" ; "\"\"" )
This step changes each " to "" so the single quote does not break the Description field into multiple fields or columns.