Tricky Records Export to CSV Question

[ideawire_bb]

When exporting records to a CSV file from Filemaker 6, it places quotes "" around each field. This is a problem for me, because I'm moving the data to MySQL and a decimal field interprets a rounded value.

ie. In filemaker, the record is 3.32. When exported to CSV, it becomes "3.32" - a string. Imported into a decimal(6,2) field in MySQL, the string is converted to a decimal, but rounded to 3.00. Very annoying.

Any ideas on how to export or extract unquoted records from Filemaker into a CSV file?

Thanks in advance for assistance.

Best,

Brenden B.

[andygaunt]

One option is to open the file in Excel and then you can strip out the " seperator and resave as a CSV file.

[ideawire_bb]

Thanks for the suggestion Andy.

Unfortunately, I have varchar fields that require the data be exported as strings, so I cant do a global replacement of "".

ie.

id - name - price

"1" - "Sample" - "3.32" -- how FM 6 exports them

I need the records exported as

1 - "Sample" - 3.32

[Fenton]

I had to do this recently, and used an XSL stylesheet (which is a text file) to produce a .csv text file. Since it has no separators by default, you just add the ones you want, after each field, than a line ending at the end of the record.

It doesn't take a great deal of XML expertise to produce a straightforward export (fortunately).

First export a couple records as XML, XMLRESULT choice as the type, no XSL stylesheet. You'll end up with an xml text file. At the top will be a Metadata section, with the field names and types. In the example below I manually added the number at the end; the position of each field (I needed to know; you'll see why)

<METADATA>

<FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="RecType" TYPE="NUMBER" /> 1

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

<FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="EventType" TYPE="NUMBER" /> 3

<FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Scheduled Start" TYPE="TEXT" /> 4

<FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="Scheduled End" TYPE="TEXT" /> 5

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

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

</METADATA>

You use the types and numbers above to know whether you need quotes and a comma, or just a comma, or a quote and a comma in your XSL stylesheet, which is a text file. When you do the real XML Export, you specify the XSL file (radio button choice). Looks like this:

<?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" exclude-result-prefixes="fmp">

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

<!-- These 1st matches are just to keep this stuff out of the text file -->

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

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

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

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

<!-- This is the beginning of the data -->

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

<xsl:apply-templates select="fmp:ROW" />

</xsl:template>

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

<xsl:value-of select="fmp:COL[1]/fmp:DATA" />

<xsl:text>,</xsl:text>

<xsl:value-of select="fmp:COL[2]/fmp:DATA" />

<xsl:text>,</xsl:text>

<xsl:value-of select="fmp:COL[3]/fmp:DATA" />

<xsl:text>,</xsl:text>

<xsl:value-of select="fmp:COL[4]/fmp:DATA" />

<xsl:text>,</xsl:text>

<xsl:value-of select="fmp:COL[5]/fmp:DATA" />

<xsl:text>,"</xsl:text>

<xsl:value-of select="fmp:COL[6]/fmp:DATA" />

<xsl:text>","</xsl:text>

<xsl:value-of select="fmp:COL[7]/fmp:DATA" />

<xsl:text>"</xsl:text>

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

<!-- Line feed (above) at the end of line. You could put &#13; in front, for Windows. But &#10; may be enough -->

</xsl:template>

</xsl:stylesheet>

( P.S. You could combine the last " with the &#10; )

[ideawire_bb]

Fenton,

Thank you for your detailed post. I've followed your instructions and created my own style.xsl file based on your example. When I go to export the records and specify the XSL stylesheet, my file is unavailable (greyed out). Do I have the correct file extension? Are there any additional steps to make it use the XSL stylesheet?

Your assistance is much appreciated.

[Fenton]

".xsl" (not ".xls", which is Excel) is the extension. But it seems you've got that right. It's a text file. You could post it and we could see if something's funny with it.

[ideawire_bb]

Well, at least part of the problem was my mistake. Though I had named the file .xsl, it had silently applied .xml to the end of the file. Once I corrected the name to style.xsl, Filemaker accepted the file.

However, it will not export any records due to a vague error. I went ahead and validated the XSL online, so I'm stuck again.

The filemaker error was:

Invalid Document Structure

Line 1 Col 1

(Style.xsl)

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

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

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

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

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

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

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

<xsl:apply-templates select="fmp:ROW" />

</xsl:template>

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

<xsl:value-of select="fmp:COL[1]/fmp:DATA" />

<xsl:text>","</xsl:text>

<xsl:value-of select="fmp:COL[2]/fmp:DATA" />

<xsl:text>,</xsl:text>

<xsl:value-of select="fmp:COL[3]/fmp:DATA" />

<xsl:text>","</xsl:text>

<xsl:value-of select="fmp:COL[4]/fmp:DATA" />

<xsl:text>","</xsl:text>

<xsl:value-of select="fmp:COL[5]/fmp:DATA" />

<xsl:text>,</xsl:text>

<xsl:value-of select="fmp:COL[6]/fmp:DATA" />

<xsl:text>,</xsl:text>

</xsl:template>

</xsl:stylesheet>

=============

If I understood your example correctly, fields 1, 3 & 4 should be strings (data enclosed by "") and everything else treated as a number.

[Fenton]

Well, it's not happy, that's for sure, as Line 1, Col 1 is probably the 1st thing it's trying. First, are you sure you're choosing the XMLRESULT format, not DSORESULT when you Export from FileMaker?

You don't have "(Style.xsl)" actually IN the file do you (it's just the name you're showing us)? There are no hidden characters? What application are you using to edit this file? Because it looks fine, and it works for me.

As far as whether there's quote comma quote or just a comma, or quote comma or comma quote, these match whether the fields are text or number. But they wouldn't cause an error no matter how you arranged them.

[ideawire_bb]

I was using TextEdit which placed a file descriptor at the top of the file which was hidden until I edited it with BBEdit.

Thanks -- everything works now.

I appreciate your help Fenton.