Adding first row with field names to exported text file?

[DaCo]

I wonder why the different variations of delimited text files does'nt contain a first row with field names (except for merge files).

I make several exports every day and it's quite anoying and time consuming to do it manually by copy/paste every time.

1. Why is it so?

2. How can I make Filemaker to include theese field names?

[comment]

I believe there are exactly two "different variations of delimited text files" - tab-delimited and comma-separated. The merge format adds a first row with field names to the comma-separated variation. You can also export as XML and use a custom XSLT stylesheet to create your own format - see the "Custom Delimiters" example in the XML Examples folder installed with the application.

[DaCo]

I've found it, but I do'nt understand much of it. Examining the custom_delim.xsl only shows to me, that it*s possible to define my own delimiters.

I am using the TAB-delimited several times every day, which suits my needs for upload to an osCommerce webshop - except that I need to add the first row with field names every time manually.

[comment]

I believe there was a thread with a sample that does exactly that, but I can't find it. Let me see if I kept a copy somewhere.

Are your field names always the same, or do you have different fields for different exports?

[DaCo]

Thanks for trying, Consultant. In the meanwhile I have tried myself. I found two examples "somewhere" and combined them. By completely luck I managed to get it work:

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

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:fmp="http://www.filemaker.com/fmpxmlresult">

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

<xsl:template match="fmp:FMPXMLRESULT">

		<xsl:text>Name One Two Date_Created TS_Created&#10;</xsl:text>   [separated with the TAB-key on the keyboard]

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

   <xsl:for-each select="fmp:COL">

	<xsl:choose>

	 <xsl:when test="position()=last()">

	  <xsl:value-of select="fmp:DATA"/>

	 </xsl:when>

	 <xsl:otherwise>

	  <xsl:value-of select="fmp:DATA"/>

	  <xsl:value-of select="$delimiter"/>

	 </xsl:otherwise>

	</xsl:choose>

   </xsl:for-each>

   <xsl:value-of select="$newrecord"/>

  </xsl:for-each>

</xsl:template>

<xsl:variable name="delimiter">

  <xsl:text> </xsl:text>										    [content  between the tags = TAB-key on the keyboard]

</xsl:variable>

<xsl:variable name="newrecord">

<xsl:text>

</xsl:text>

</xsl:variable>

</xsl:stylesheet>

I don' know why it works and maybe there is too much code, but ... it works!

The result is a TAB-delimited text file with custom defined field names in the first row.

Now I only need to find out how to save with Windows(CRLF) and Western (Windows latin 1) character set.

[comment]

The encoding of the result is determined in the <xsl:output> element.

To use CRLF as the record separator, change the definition of the 'newrecord' variable to:

<xsl:variable name="newrecord">

<xsl:text>&#x000D;&#x000A;</xsl:text>

</xsl:variable>

[DaCo]

The encoding of the result is determined in the <xsl:output> element.

Yes, latin-1 made the difference

To use CRLF as the record separator, change the definition of the 'newrecord' variable to:

<xsl:variable name="newrecord">

<xsl:text>&#x000D;&#x000A;</xsl:text>

</xsl:variable>

&#x000D;&#x000A; = Unix with extra line feed

&#x000D; = Unix

&#x000A; = Unix

- according to TextWrangler for Mac.

[comment]

That's not what I see:

[DaCo]

That'sw sppoky, is'nt it?

Here's what I see (in TextWrangler on a iMac running 10.6) when using <xsl:text>&#x000D;&#x000A;</xsl:text> :

[comment]

1. Post your exact stylesheet.

2. What do you get with this one (attached)?

CustomDelimit.xsl.zip

[DaCo]

1. Post your exact stylesheet.

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

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:fmp="http://www.filemaker.com/fmpxmlresult">

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

<xsl:template match="fmp:FMPXMLRESULT">

<xsl:text>v_products_model v_products_name_1 v_products_description_1 v_products_page_title_1 v_products_meta_keywords_1 v_products_meta_description_1 v_products_short_description_1 v_products_image v_products_price_per v_products_ean v_products_price v_products_quantity v_products_weight v_categories_name_1_1 v_categories_name_2_1 v_categories_name_3_1 v_manufacturers_name v_tax_class_title v_status EOREOR&#10;</xsl:text>

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

<xsl:for-each select="fmp:COL">

<xsl:choose>

<xsl:when test="position()=last()">

<xsl:value-of select="fmp:DATA"/>

</xsl:when>

<xsl:otherwise>

<xsl:value-of select="fmp:DATA"/>

<xsl:value-of select="$delimiter"/>

</xsl:otherwise>

</xsl:choose>

</xsl:for-each>

<xsl:value-of select="$newrecord"/>

</xsl:for-each>

</xsl:template>

<xsl:variable name="delimiter">

<xsl:text> </xsl:text>

</xsl:variable>

<xsl:variable name="newrecord">

<xsl:text>&#x000D;&#x000A;</xsl:text>

</xsl:variable>

</xsl:stylesheet>

2. What do you get with this one (attached)?

Yahoooo ... :

Wauw, I could'nt have made it better myself

It's clear that I would need to read a lot about XML before next time

Thank you very much for your help :waytogo:

[DaCo]

Hey, hey, hey ... where did the first line with the field names go?

[Filemaker User]

Great work.

Just a question

How to you create the  XSLT stylesheet .

Could it be done via Filemaker?

Or we must use another program? Like what? Does a notepad work?

Thank you

[comment]

I suggest you use a text editor. I would not recommend using Filemaker for this, though it is sort of possible. Any text editor should do: after all, the XSLT document is just a plain text file.

[Filemaker User]

Thank you for your help.

Can we export as excel file using XML export

Or we are stuck to export as txt

[comment]

2 hours ago, Filemaker User said:

Can we export as excel file using XML export

The short answer is no, but the full answer is more complicated:

The result of an XSL transformation can be a text, XML or HTML file. Excel has several file formats - one of these is the "Microsoft Office Excel 2002 and Excel 2003 XML Format" (also known as SpreadsheetML). This is an XML file that Excel will recognize and open natively - and it certainly can be produced by exporting from Filemaker.

A "real" Excel file (.xlsx) is a zipped collection of XML files. To produce such a file, you would have to export the data XML, then archive it together with the other files. This is possible using a plugin or OS-level scripting.

In addition, there are other formats (CSV, HTML) that Excel will open as its own under some conditions.

[Filemaker User]

Thank you

You were very helpful.