Formatting CSV output

[Kurt Hansen]

I export data to a CSV file via a script in FM Pro 11. This must then be imported into a OsCommerce shop online.

In my daily work I always work in "Browse Mode"> "Table View" and I therefore assume that the formating in the "Inspector" only affect the display on the screen and is not reflected in the exported file?

It works, but the CSV file has two wrong formating:

Price is written "149,50", but should be "149.50"
Dates written as "01/09/2015", but must be "2015-09-01"

My system (Mac OS X Yosemite) are in Danish and therefore the following formats:
Currency: x.xxx,xx
Date: dd/mm/yyyy

My script is called "Butik" in the FMP and consists of the following steps:

1. Go to Layout [original layout]
2. Show All Records
3. Perform Find [Restore]
4. Export Records [No dialog; "Store gammelheder.txt"; "FMPXMLRESULT"; "Store-gammelh .." (Supposed to be "shop-gammelheder.xsl"?)

The contents of the "Butik-gammelheder.xsl" reproduced below, as I suspect that this is where the formatting is done (?). I got help to build the sript and therefore I do not excatly what is going on and where / what I need to change. Please note the COL[16]. I suppose that this should be COL[18] (EOREOR is not a data field)?

<?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" encoding="ISO-8859-1"/>

<xsl:variable name="CRLF">
<xsl:text>&#x000D;&#x000A;</xsl:text>
</xsl:variable>

<xsl:variable name="delimiter">
    <xsl:text>    </xsl:text>
</xsl:variable>

<xsl:template match="/">
    <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_manufacturers_name
    v_tax_class_title
    v_status
    EOREOR
    &#x000D;&#x000A;
</xsl:text>

<xsl:for-each select="fmp:FMPXMLRESULT/fmp:RESULTSET/fmp:ROW">
    <xsl:for-each select="fmp:COL">
        <xsl:value-of select="fmp:DATA"/>
        <xsl:choose>
            <xsl:when test="position()=last()">
                <xsl:value-of select="$CRLF"/>
            </xsl:when>
            <xsl:otherwise>
                <xsl:value-of select="$delimiter"/>
            </xsl:otherwise>
        </xsl:choose>
    </xsl:for-each>
</xsl:for-each>

<xsl:variable name="date" select="fmp:COL[16]/fmp:DATA"/>
<xsl:value-of select="substring-after(substring-after($date, '/'), '/')"/>
<xsl:text>-</xsl:text>
<xsl:value-of select="substring-before(substring-after($date, '/'), '/')"/>
<xsl:text>-</xsl:text>
<xsl:value-of select="substring-before($date, '/')"/>

</xsl:template>
</xsl:stylesheet>

[comment]

I therefore assume that the formating in the "Inspector" only affect the display on the screen and is not reflected in the exported file?

In the export dialog, there is an option "Apply current layout’s data formatting to exported data". If you check it, the formatting selected in the Inspector will be applied to the exported data. For this to work, you must first go to a layout where the fields are formatted this way.

Alternatively, you could handle this in the XSLT stylesheet. I am not sure how to fix it for you, because (a) this does not produce a CSV file, and (b) you say this works (apart from the 2 formatting flaws) - but I see an attempt has been to format the date, and this would cause a single date to be appended to the end of the document (while leaving all dates in their original formatting).

If you can provide the exact specifications for the output file, then this could be explored further.

Edited September 11, 2015 by comment

[OlgerDiekstra]

Alternatively, you could loop through all your found records, then output using something like Goya's Base Elements plugin, to a plain text file with a CSV formatting. You then have full control over formatting of any field.

[comment]

Alternatively, you could loop through all your found records, then output using something like Goya's Base Elements plugin, to a plain text file with a CSV formatting. You then have full control over formatting of any field.

Where exactly is this "full control over formatting" located?

[Kurt Hansen]

In the export dialog, there is an option "Apply current layout’s data formatting to exported data". If you check it, the formatting selected in the Inspector will be applied to the exported data. For this to work, you must first go to a layout where the fields are formatted this way.

As simple and THEN effectively. Thank you, consultant :-)

[OlgerDiekstra]

Where exactly is this "full control over formatting" located?

That can be located in the export script, building the line of text. Its a bit more work initially, building the script. I use a similar approach to export a payment text file (not csv) to be uploaded to the bank, where each line has to be a specific length. Any overflow of information has to be chopped off to ensure the length of the line does not increase.

[comment]

That can be located in the export script, building the line of text. Its a bit more work initially, building the script.

You mean you would have the script parse the date fields to their elements and put them back together (as text) in the requested format? And do a similar thing for the number fields, to substitute the separators? That, and especially having to assemble the entire file as a string, is a lot of work - and most of it can be avoided (along with the need for a plugin) if you export as XML with XSLT.

 

I use a similar approach to export a payment text file (not csv) to be uploaded to the bank, where each line has to be a specific length. Any overflow of information has to be chopped off to ensure the length of the line does not increase.

An example of fixed-width export is actually provided with the Filemaker application. A slightly more elaborate one (with individual length per field) can be found here:

 

[OlgerDiekstra]

You mean you would have the script parse the date fields to their elements and put them back together (as text) in the requested format? And do a similar thing for the number fields, to substitute the separators? That, and especially having to assemble the entire file as a string, is a lot of work - and most of it can be avoided (along with the need for a plugin) if you export as XML with XSLT.

I didn't say store the entire file in a string. BaseElements can write line by line. In addition, our bank doesn't like XML. And, every now and then there's an account that's not quite correct, a plain text file is then much easier to review than an xml file.

On top of that, the bank requires the first line to contain specific information related to the our company, and the last line is also non standard, containing the total twice and a line count of only the payments, excluding first and last line.

Base Elements further allows me to save the file to any location the server has access to, so I can save it very easily to a NAS, as opposed to a FM document area.

The entire payment process isn't possible with a simple export due to all sorts of business rules being applied. The export itself (using BE) isn't all that involved.

I didn't say using BE (or any other plugin for that matter) to write plaintext files is the best solution. It's simply another approach.

[comment]

I didn't say store the entire file in a string. BaseElements can write line by line.

Makes no difference to the point I was making.

 

In addition, our bank doesn't like XML.

I am afraid you're missing the point: the result of exporting as XML can be XML, HTML or any (other) plain text format - standard or custom.

 

The entire payment process isn't possible with a simple export due to all sorts of business rules being applied.

I am quite sure you're wrong about that. XSLT itself is a Turing-complete language; if it can be derived from the input, it can be produced using XSLT.

[OlgerDiekstra]

I am quite sure you're wrong about that. XSLT itself is a Turing-complete language; if it can be derived from the input, it can be produced using XSLT.

I looked on W3Schools website to educate myself a bit on XML and XSLT, and this morning played around a bit with exporting payments via XML/XSLT. I agree that I should be able to create a suitable export file using XSLT, and while it has some benefits in that I wouldn't have to touch the FM script if the format changed, the existing DB would requires too much work to make it work properly, and the current process functions well at the moment. However, it's something to consider in the future if and when I have to redo it. 

[ggt667]

In the export dialog, there is an option "Apply current layout’s data formatting to exported data". If you check it, the formatting selected in the Inspector will be applied to the exported data. For this to work, you must first go to a layout where the fields are formatted this way.

Exporting to CSV is simple if you do these steps

1) Create a dedicated layout "csvExportForOsCommerceButik" for the purpose of the formatting,

2) Put all the files that needs to be in the export in one color, any other fields on involved in the actual field in another color

3) Format those fields that are needed for the export

4) Check the checkbox that @comment mentioned

5) Do the export

Edited September 14, 2015 by ggt667

[comment]

Exporting to CSV is simple

Yes, it is (even simpler than what you describe). But if you had read the original question, you would have noticed that the export required here is to a custom format, not CSV.

[ggt667]

It works, but the CSV file has two wrong formating:

Price is written "149,50", but should be "149.50"
Dates written as "01/09/2015", but must be "2015-09-01"

@comment: This is the part I answered.

[comment]

the existing DB would requires too much work to make it work properly

Why so? As you yourself had noted, the entire idea here is to farm out all the export logic to the XSLT stylesheet. The only work required within the solution is a simple export script: pick the fields you need, specify a name and a path for the resulting file, and point to the stylesheet.

[OlgerDiekstra]

Why so? As you yourself had noted, the entire idea here is to farm out all the export logic to the XSLT stylesheet. The only work required within the solution is a simple export script: pick the fields you need, specify a name and a path for the resulting file, and point to the stylesheet.

Yeah, but I also need additional data from other tables that are currently not related. It's not impossible, and it would be something I would be looking at if I had the time. At the moment my time goes into maintenance of that DB, creating a new DB to be used on iPads at about 10 mobile locations, and managing the IT side of things for the company I work for. My focus at the moment is creating a FMGo app that is designed as best as I can (currently using the Connector-Selector model), and considering I have only a year and a half real experience with FM, I'm not rushing that. I do as much research as I can. The TO structures is only part of that, data security (how to handle theft/breakage of the iPad holding the data, syncing with the main DB, etc, etc. Most is new territory for me (in FileMaker).

Yes, it'd be nice, but for the moment I have to shelf that until I have the time. My ultimate goal is to extend the FMGo app and replace the current DB with it. That is then an opportunity to also redo the exports. I'm already experiencing problems working on tables (ie adding new fields) while users are also entering data. I know, developing in a life DB isn't ideal, but unfortunately, unless I start doing nightshifts, that's how it is. From what I've heard and read, the issues we get (if I add a field in the invoice table for instance, this often results in invoices being entered ending with different customers), are due to bad data/TO design.

[comment]

Yeah, but I also need additional data from other tables that are currently not related.

That is a rare case - but even here, the real work would not be done within Filemaker.  Your script would first export the data from the "other" tables  (as XML, with no stylesheet) to a known location. Only the last export would use a stylesheet, and that stylesheet would read the previous exports (by using the document() function) and incorporate them in the final document.