Converting Carriage Returns

[Steve Ladd]

I have a calculation which contains carriage returns. I use a script which uses the export fields as a text file. When I open the file in Windows it does not recognize the carriage returns.

Is there a way to modify the calculation to account for the way Mac and Windows handle the carriage returns? (Using FM 8 and 9)

Edited January 19, 2008 by Guest

[comment]

The problem is not with the calculation. When exporting as tab/comma delimited, Filemaker converts all in-field carriage returns to vertical tabs - because carriage returns are reserved as record delimiters.

Using Export Field Contents... preserves carriage returns in the field (because only one record is exported), but the resulting file is UTF-16 encoded, which doesn't sit well with many target applications.

[Fenton]

Does it have to export on both Mac and Windows, or only export on Mac and be readable on Windows?

If it's only a single field in 1 record, you can do as comment says. If utf-16 is a problem, and you are only exporting on the Mac, you can use AppleScript and a command-line tool to write the file. This automatically converts the internal returns to regular returns. (Or you can Export Field Contents, then convert the file's encoding with command line; same result; you need the Unix syntax file path in either case.)

If you need the export script to work from Mac or Windows, then Export XML, with an XSL stylesheet will do it. The stylesheet can be fairly simple and generic, since there's only 1 record and 1 field; whichever you export.

Attached is an example file of xml export. It has an export 1 field script with an xsl stylesheet. You can see it's pretty simple. There are a few example text export files. They show the difference between utf-16 and utf-8, using the name "Mañuel" (on a Mac, type Option-n, then n).

Export_1_field.zip

[Steve Ladd]

Thanks for the file, it did the trick.

Speed Question:

The Filemaker database is running on Server 8 but I have the xsl file on the local machine and it can take several minutes for the text file (168K) to be generated.

Would it be faster to have the xsl file on the server? (the text file is written to the local machine)

Where does the file need to be stored? (example of path)

Thanks

[jimlongo]

If you need the export script to work from Mac or Windows, then Export XML, with an XSL stylesheet will do it. The stylesheet can be fairly simple and generic, since there's only 1 record and 1 field; whichever you export.

Attached is an example file of xml export. It has an export 1 field script with an xsl stylesheet. You can see it's pretty simple. There are a few example text export files. They show the difference between utf-16 and utf-8, using the name "Mañuel" (on a Mac, type Option-n, then n).

Sorry to resurrect this old thread, but it's the closest I've seen to what I am trying to do.

Basically I want to export a CSV file that has LF instead of CR (as is the case in Mac versions of Filmaker)

From what I've read I need to use an XML export and a XSL stylesheet to do this.

The XSL stylesheet in this example does insert LFs , but it puts them after every field, which is not what I am trying to do.

I'm sorry I don't understand the XSL stylesheet enough yet to modify it.

simply I have an XML output that looks like this

<FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="t_LastName" TYPE="TEXT"/>

<FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="t_FirstName" TYPE="TEXT"/>

<FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="t_phoneNumber_2" TYPE="TEXT"/>



<ROW MODID="285" RECORDID="4143"><COL><DATA>Smith</DATA></COL><COL><DATA>Joe</DATA></COL><COL><DATA>416-123-4567</DATA></COL></ROW>

<ROW MODID="80" RECORDID="5281"><COL><DATA>Brown</DATA></COL><COL><DATA>Jim</DATA></COL><COL><DATA>416-234-4567</DATA></COL></ROW>

I'd like to end up with a file that has the following



Smith, Joe, 416-123-4567  (LF)

Brown, Jim, 416-234-4567





If I use the stylesheet in the example

<?xml version="1.0" encoding="utf-8"?>

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"

xmlns:fm="http://www.filemaker.com/fmpxmlresult" exclude-result-prefixes="fm" >

<xsl:output method="text" version="1.0" encoding="utf-8" indent="no"/>

<!-- use utf-16 to handle special characters better; but utf-8 is more compatible with some applications -->

<xsl:template match="/">

<xsl:for-each select="fm:FMPXMLRESULT/fm:RESULTSET/fm:ROW">

<xsl:for-each select="fm:COL/fm:DATA">

<xsl:value-of select="." />

<xsl:if test="position()!=last()">

<xsl:text>&#10;</xsl:text>

</xsl:if>

</xsl:for-each>

<xsl:if test="position()!=last()">

<xsl:text>&#10;&#10;</xsl:text>

</xsl:if>

</xsl:for-each>

</xsl:template>

</xsl:stylesheet>

I end up with each field followed by a LF

Smith (LF)

Joe (LF)

416-123-4567 (LF)

(LF)

any guidance on writing the stylesheet would be greatly appreciated,

jim

[jimlongo]

After staring at this for a while, I figured this out. Change the text to be inserted after each COL DATA to a comma &#44 and then one LF for each last DATA in the row (or at least that's how I understand it).

hth

<?xml version="1.0" encoding="utf-8"?>

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"

xmlns:fm="http://www.filemaker.com/fmpxmlresult" exclude-result-prefixes="fm" >

<xsl:output method="text" version="1.0" encoding="utf-8" indent="no"/>

<!-- use utf-16 to handle special characters better; but utf-8 is more compatible with some applications -->

<xsl:template match="/">

  <xsl:for-each select="fm:FMPXMLRESULT/fm:RESULTSET/fm:ROW">

   <xsl:for-each select="fm:COL/fm:DATA">

	<xsl:value-of select="." />

	<xsl:if test="position()!=last()">

	 <xsl:text>&#44;</xsl:text>

	</xsl:if>

   </xsl:for-each>

   <xsl:if test="position()!=last()">

	<xsl:text>&#10;</xsl:text>

   </xsl:if>

  </xsl:for-each>

</xsl:template>

</xsl:stylesheet>