Exporting Data

[djc728]

I have 4 fields of data I want to export to a file. I tried to export as a .csv file BUT all of my field data contain quotes around them and I want to export the first 2 fields with quotes around them and the last two fields without quotes around them as the data needs to be in this format.

Is there a way to do this?

[Lee Smith]

Have you tried other formats?

[Jim Medema]

The interesting element of your question is that you want some fields to export with quotes and others without them.

Assuming that you don't want to assume your users will remember to add quotes to the desired fields ('cause they won't), you could easily create hidden utility fields that add them! When it's time to export, simply export the utility fields along with the regular fields.

Example

Your original field list:

Field01 - (desire to export with quotes)

Field02 - (desire to export with quotes)

Field03 - (desire to export without quotes)

Field04 - (desire to export without quotes)

Create 2 new fields:

Field01ForExport (calc field) = Char ( 34 ) & Field01 & Char ( 34 )

Field02ForExport (calc field) = Char ( 34 ) & Field02 & Char ( 34 )

Note that the calc engine cannot properly read """ (a quotation mark surrounded by quotation marks), we need to take advantage of the new Char function which allows us to enter the ASCII number for the quotation mark to get our desired result.

Fields to Export:

Field01ForExport

Field02ForExport

Field03

Field04

Hope this helps,

Jim

Edited November 23, 2009 by Guest

[comment]

Basically, you have two options here:

1.

Define a calculation field (result is Text) =

""" & Field1 & "","" & Field2 & ""," & Field3 & "," & Field4

Export ONLY the calculation field, using the tab-separated format.

2.

Export as XML, using a custom XSLT stylesheet - see here for something similar:

http://fmforums.com/forum/showpost.php?post/198404/

[comment]

Create 2 new fields:

Field01ForExport (calc field) = Char ( 34 ) & Field01 & Char ( 34 )

Field02ForExport (calc field) = Char ( 34 ) & Field02 & Char ( 34 )

Note that the calc engine cannot properly read """ (a quotation mark surrounded by quotation marks), we need to take advantage of the new Char function which allows us to enter the ASCII number for the quotation mark to get our desired result.

Fields to Export:

Field01ForExport

Field02ForExport

Field03

Field04

I don't think that's going to work: if you export as cvs, you will get triple quotes around the first two fields, and the last two fields will still be surrounded by quotes. If you export as tab-separated, you will get tabs where commas are expected.

[Jim Medema]

Thanks for the clarification. I neglected to specify exporting as a tab-delimited file. My unvoiced assumption (yikes!) was that since the original poster didn't seem tied to having a csv file at tab-delimited file would be fine.

Jim

[djc728]

I'd like to thank you and Jim for your responses. Coming from a 4D world I still think in terms of 4D and always seem to forget that you can accomplish a lot with the "calculation field".

Thanks for the insight and wonderful responses.

Dom