Export use ~ as a Field terminator.

[chuckcou]

I am trying to import my table into mysql. The problem is I am using " for the enclosed deliminator. The problem is my data has " in it. Thus, I am getting an errors.

[color:red]How can I export a table using a special character(eg. |) as the terminator and ~ as the fields enclosed.

[color:green]Thus it would look something like this:

~field1~|~field2~|~field3~

The only way I can think of doing this is to use a calculation. There must be a better way.

Edited February 20, 2009 by Guest

[chuckcou]

Ok, I made a calculation and the format is correct. Now the problem is how to I save it to just a txt file without any formatting. If I do an export then I would get formatting.

Here is my calculation-->

[color:red]"~" & "~" & "|" &

"~" & mysqlDate & "~" & "|" &

"~" & name2 & "~" & "|~" &

html_Data & "~"

[comment]

Why do you need to reinvent the wheel? The csv format knows how to handle quotes and commas in fields.

[chuckcou]

I totally agree with you. I did try csv because I figured that would be the easiest way, BUT....

[color:blue]

Homeowner-general contractor dispute SETTLED:

"A difficult case was

[comment]

As I said, the csv format is well equipped to deal with both quotes and commas in fields, and Filemaker exports the data correctly. I'd suggest you look for answers on the side of MySQL. It's not simple to export data with custom delimiters (though it is possible - e.g. by using exporting a single calculation field as tab-delimited file, or when exporting as XML with a custom stylesheet).

[chuckcou]

by using exporting a single calculation field as tab-delimited file

This is how I have done it and it seemed to work fine.

The only quesiton I have is that I believe there was a limitation when using a calculation. If so, what is the limitation?

[comment]

I am not aware of anything that isn't obvious, like having no separators within field data. I would still look for a way to import a valid .cvs into MySQL - I am sure you are not the first one wanting to do that.

BTW, it could be more convenient to do the calculation as =

Let (

separator = "~"

;

Substitute ( List (

Field1 ;

Field2 ;

Field3

) ; ¶ ; separator )

)