Substitute function problem

[Robert Collins]

I have a field that sometimes has a " symbol in it.

Hi

I have a field that sometimes has a " symbol in it. - this field will be exported to a .csv file and imported into a web server.

My website people told me to replace the

"

with

"

so the import script their end understands the difference between field contents and the start and end of each field.

I've got this far , but am having trouble with the last part

Substitute (Product name ; """ ; " " ")

I can get it half working if i put this in - but when i remove the spaces , it doesn't accept it.

any thoughts welcome !

[Raybaudi]

Even backslashes have to be escaped:

Substitute ( Product name ; """ ; """ )

[comment]

My website people told me to replace the

"

with

"

so the import script their end understands the difference between field contents and the start and end of each field.

Before you go there, you need to know that when exporting as .csv, any quotation marks in a field will be exported as double quotation marks. If your field contains the text:

3" wide

Filemaker will export it as:

...,"3"" wide",...

[Robert Collins]

thanks for your replies.

Comment - so what you are saying is that:

Substitute ( Product name ; """ ; """)

won't achieve what I need?

[comment]

I am saying that Filemaker already recognizes quotes as a reserved character in the .csv format and escapes them by doubling them. Your website people should take advantage of that, instead of trying to reinvent the wheel. Or use another format as the intermediate. There is no way you can export as .csv and get what they ask for.

[qube99]

Also, csv can be problematic for online databases. I've built about 150 online stores and never had an issue using tab delimited text.

[comment]

Also, csv can be problematic for online databases.

Anything can be "problematic" if not used properly. I am not aware of any problems that are inherent to the format.

[qube99]

CSV is a big problem for website data that contains text with commas in it.

[comment]

CSV is a big problem for website data that contains text with commas in it.

Not at all. A comma contained in between quotes is part of the contents; otherwise it's a separator - see:

http://en.wikipedia.org/wiki/Comma-separated_values#Basic_rules_and_examples

[qube99]

I battled those quotation marks for years. We finally advised all of our clients to stop using Excel and switch to FMP or use our runtime. Problem solved. The problem is not limited to text fields using commas. Excel also inserts quotation marks in text fields containing HTML and URLs.

I don't want my data processor altering my field contents. I don't want my online stores displaying "extra" quotation marks. FMP is quite good here while Excel stinks.

Yeah, I know you can script them out but have you ever tried to get a customer to write a custom script in Excel? Sisyphean to the max!

[comment]

I thought we were discussing the CSV format. I am not sure how Excel comes into this.

[Pavlk]

Try this one:

Substitute ( inputField ; """ ; """ & "" & """)

[qube99]

I thought we were discussing the CSV format. I am not sure how Excel comes into this.

I kinda went through the logic in shorthand so I'll try to be more specific.

Your problem is one of a larger set of related problems for data driven websites.

CSV causes unwanted quotation marks in some website field content

Tab delimited text solves the problem in FMP

Tab delimited text does not solve the problem in Excel

There is no easy solution for Excel clients

A combination of FMP and tab delimited text is an excellent, robust solution.

It's extremely common for ecommerce customers to use Excel for spreadsheets. The developer must now convert them to FMP, perhaps by providing a runtime for their data management.

[comment]

CSV causes unwanted quotation marks in some website field content

This is the point I do not accept. I don't think it's necessary to extend this discussion to Excel, which has its own set of issues.