Exporting Data to TSV with Custom Headers

[NCB]

I have several tables which I want to export to a TSV, but I wish to include a custom header at the top of the actual exported data. This custom header is comprised of the field comment of each field, NOT the field names themselves.

 

Currently in my export script, I am adding the custom header row to the actual data, sorting it to the top, exporting, then deleting it. However, there seems to be a problem when users run the exporter concurrently, and actual data rows are being deleted occasionally instead of the headers. Because I can't figure out how this is happening, I want to eliminate the need to delete records at all in the export script.

 

My new idea is to generate the custom header inside a global variable, use the standard "Export Records" script step, then somehow tack the two together inside filemaker, perhaps with ScriptMaster. There is a problem with the ordering of the columns, as that is determined by the export order when "Export Records" is run, but I am filling a value list with my standard export order and generating the header based on that. I could just concatenate all the rows into a variable and write that to a file as well, but that is much too slow.

 

Any ideas on how I can accomplish this? Is there a faster and/or simpler way to do this than my proposed method?

[No_access]

I do something very similar, however my solution was to  use a text field, set all the information to that field including my custom header information., then I export that field.

this is simple and works well for me. 

If you post a sample file I am sure we can figure out what going on in your world.

 

thanks

[NCB]

I do something very similar, however my solution was to  use a text field, set all the information to that field including my custom header information., then I export that field.

this is simple and works well for me. 

If you post a sample file I am sure we can figure out what going on in your world.

 

thanks

Oh, that is a good idea. How do you get the "Export Records" step to append? I imagine that you first export your custom header to the file using "Export Field Contents", then export the records to the same file with "Export Records?"

 

I imagine one could create a temporary table to populate, then just export that, but I'd like to stay away from creating new tables, if possible.

[NCB]

Actually, I just found another way to accomplish this. I export the data with "Export Records," create the header in a variable $header, open the exported file with Scriptmaster, then write the header and the exported data back into that file. It's a roundabout way to do it, but it works!

[No_access]

NCB

Oh, that is a good idea. How do you get the "Export Records" step to append? I imagine that you first export your custom header to the file using "Export Field Contents", then export the records to the same file with "Export Records?"

 

I imagine one could create a temporary table to populate, then just export that, but I'd like to stay away from creating new tables, if possible

I am using a variable to save in a text field, if I need to append I just leave the data in the text field instead of clearing it out and add the new data below or above... however my export is unique each month, so I do not need to append I do not use a temp table, I run my script, store everything in  variables them set my field to the sum of the variables., if you want I can send you a sample file to play with..

 

[Cabinetman]

I'm mainly adding this post because I struggled with the issue of exporting records and only including a needed field once in the header ......... feel free to rewrite the post so it makes sense to you.

 

This is how I export my records as TSV with a header (which includes the very first field of data in the header).

 

1. Created dummy .txt file to export to

2. Script step to export records with field1 field2 field3

2A. Export as "File Type : XML" but chose the dummy text file in the Output File Path List

2B. Use XSL Style sheet

 

This is my style sheet .........

<?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"/>
    <!--
File: custom_delim.xsl ..... Edited

Transforms data in FMPXMLRESULT grammar into a text file with the
specified characters as field and record delimiters.

Note that it is possible to choose field and record delimiters
that would result in an exported file that FileMaker Pro can no
longer import.

For example:

Database data
=============
White     Johnson  Red       33
Green     Mark     Red      100
Blue      Sam      Blue     201
Yellow    Susan    Green    839
Straight  Wendy    Orange    22

Output
======
White*Johnson*Red*33
Green*Mark*Red*100
Blue*Sam*Blue*201
Yellow*Susan*Green*839
Straight*Wendy*Orange*22
 
===============================================================

Copyright © 2002 FileMaker, Inc.
All rights reserved.

Redistribution and use in source and binary forms, with or
without modification, are permitted provided that the following
conditions are met:

* Redistributions of source code must retain the above copyright
  notice, this list of conditions and the following disclaimer.

* Redistributions in binary form must reproduce the above copyright
  notice, this list of conditions and the following disclaimer in
  the documentation and/or other materials provided with the
  distribution.

* Neither the name of the FileMaker, Inc. nor the names of its
  contributors may be used to endorse or promote products derived
  from this software without specific prior written
  permission.

THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND
CONTRIBUTORS "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES,
INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF
MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED.
IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR
ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR
CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF
SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR
BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY,
WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING
NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
    
===============================================================
-->

    <xsl:template match="/">    
        <!-- Header line with col 1 data only appearing once in the export -->
        <xsl:for-each select="fmp:FMPXMLRESULT/fmp:RESULTSET">
        <xsl:text>Field1    </xsl:text><xsl:value-of select="fmp:ROW/fmp:COL[1]/fmp:DATA"/><xsl:value-of select="$newrecord"/>
        <xsl:text>Field2    Field3</xsl:text><xsl:value-of select="$newrecord"/>
        </xsl:for-each>
        <!-- End of Header -->
        <!-- Rows of records -->
        <xsl:for-each select="fmp:FMPXMLRESULT/fmp:RESULTSET/fmp:ROW">
                <xsl:value-of select="fmp:COL[2]/fmp:DATA"/><xsl:value-of select="$delimiter"/><xsl:value-of select="fmp:COL[3]/fmp:DATA"/><xsl:value-of select="$newrecord"/>
        </xsl:for-each>
    </xsl:template>
    <!--  
Variable "delimiter" contains the character that will be inserted in the output
between each field in the exported data.
    -->
    <xsl:variable name="delimiter">
        <xsl:text>    </xsl:text>
    </xsl:variable>
    <!--  
Variable "newrecord" contains the character that will be inserted in the output as the
end of record character.
            
By default the new record delimitter is a carriage return. Other characters may be
inserted between the xsl:text tags to create different delimitters.
    -->
<xsl:variable name="newrecord"><xsl:text>
</xsl:text>
</xsl:variable>
</xsl:stylesheet>

[comment]

 

1. Created dummy .txt file to export to

2. Script step to export records with field1 field2 field3

2A. Export as "File Type : XML" but chose the dummy text file in the Output File Path List

2B. Use XSL Style sheet

 

Not sure why you need step #1. Other than that, exporting via XML/XLST is without a doubt the preferable method here.