Loading ...
Sorry, an error occurred while loading the content.

Re: Loading govtrack data into Filemaker

Expand Messages
  • cb299y
    So here is the text of the XSLT file I created (I can see that others have posted file attachments, but I can t see a button to do it here. But who cares, it s
    Message 1 of 3 , Nov 13, 2012
    • 0 Attachment
      So here is the text of the XSLT file I created (I can see that others have posted file attachments, but I can't see a button to do it here. But who cares, it's just text.) So here ya go:


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

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

      <xsl:output indent="yes" method="xml"/>

      <xsl:template match="people">

      <FMPXMLRESULT xmlns="http://www.filemaker.com/fmpxmlresult">

      <ERRORCODE>0</ERRORCODE>

      <PRODUCT BUILD="10-09-2012" NAME="FileMaker" VERSION="Pro 12.0v3"/>

      <DATABASE DATEFORMAT="M/d/yyyy" LAYOUT="" NAME="people.112" RECORDS="{@count}" TIMEFORMAT="h:mm:ss a"/>

      <METADATA>

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

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

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

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

      <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="birthday" TYPE="DATE"/>

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

      </METADATA>

      <RESULTSET FOUND="{@count}">

      <xsl:for-each select="person">

      <ROW MODID="0" RECORDID="1">

      <COL><DATA><xsl:value-of select="@id"/></DATA></COL>

      <COL><DATA><xsl:value-of select="@lastname"/></DATA></COL>

      <COL><DATA><xsl:value-of select="@firstname"/></DATA></COL>

      <COL><DATA><xsl:value-of select="@middlename"/></DATA></COL>

      <COL><DATA><xsl:value-of select="@birthday"/></DATA></COL>

      <COL><DATA><xsl:value-of select="@gender"/></DATA></COL>

      <COL><DATA><xsl:value-of select="@religion"/></DATA></COL>

      <COL><DATA><xsl:value-of select="@pvsid"/></DATA></COL>

      <COL><DATA><xsl:value-of select="@osid"/></DATA></COL>

      <COL><DATA><xsl:value-of select="@bioguideid"/></DATA></COL>

      <COL><DATA><xsl:value-of select="@metavidid"/></DATA></COL>

      <COL><DATA><xsl:value-of select="@youtubeid"/></DATA></COL>

      <COL><DATA><xsl:value-of select="@twitterid"/></DATA></COL>

      <COL><DATA><xsl:value-of select="@icpsrid"/></DATA></COL>

      <COL><DATA><xsl:value-of select="@facebookgraphid"/></DATA></COL>

      <COL><DATA><xsl:value-of select="@thomasid"/></DATA></COL>

      <COL><DATA><xsl:value-of select="@name"/></DATA></COL>

      <COL><DATA><xsl:value-of select="@title"/></DATA></COL>

      <xsl:for-each select="role">

      <COL><DATA><xsl:value-of select="@type"/></DATA></COL>

      <COL><DATA><xsl:value-of select="@startdate"/></DATA></COL>

      <COL><DATA><xsl:value-of select="@enddate"/></DATA></COL>

      <COL><DATA><xsl:value-of select="@party"/></DATA></COL>

      <COL><DATA><xsl:value-of select="@state"/></DATA></COL>

      <COL><DATA><xsl:value-of select="@district"/></DATA></COL>

      <COL><DATA><xsl:value-of select="@url"/></DATA></COL>

      <COL><DATA><xsl:value-of select="@address"/></DATA></COL>

      <COL><DATA><xsl:value-of select="@current"/></DATA></COL>

      </xsl:for-each>

      </ROW>

      </xsl:for-each>

      </RESULTSET>

      </FMPXMLRESULT>

      </xsl:template>

      </xsl:stylesheet>
    • cb299y
      Oops! The origianl XSLT file I posted did not transform the birthday as is necessary to load dates into Filemaker. So here s another XSLT file (this one is to
      Message 2 of 3 , Nov 16, 2012
      • 0 Attachment
        Oops! The origianl XSLT file I posted did not transform the birthday as is necessary to load dates into Filemaker. So here's another XSLT file (this one is to load the historical people and role data, so it's not for exactly the same destination database in Filemaker, but it should show how to transform the dates correctly.) I've checked and this approach produces valid dates in Filemaker. Cheers!

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

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

        <xsl:output indent="yes" method="xml"/>

        <xsl:template name="formatDate">
        <xsl:param name="FMPdate" />
        <xsl:variable name="year" select="substring-before($FMPdate, '-')" />
        <xsl:variable name="day" select="substring-before(substring-after($FMPdate, '-'), '-')" />
        <xsl:variable name="month" select="substring-after(substring-after($FMPdate, '-'), '-')" />
        <xsl:value-of select="concat($day, '/', $month, '/', $year)" />
        </xsl:template>

        <xsl:template match="people">

        <FMPXMLRESULT xmlns="http://www.filemaker.com/fmpxmlresult">

        <ERRORCODE>0</ERRORCODE>

        <PRODUCT BUILD="10-09-2012" NAME="FileMaker" VERSION="Pro 12.0v3"/>

        <DATABASE DATEFORMAT="M/d/yyyy" LAYOUT="" NAME="people.112" RECORDS="{@count}" TIMEFORMAT="h:mm:ss a"/>

        <METADATA>

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

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

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

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

        <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="birthday" TYPE="DATE"/>

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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


        </METADATA>

        <RESULTSET FOUND="{@count}">

        <xsl:for-each select="person">

        <ROW MODID="0" RECORDID="1">

        <COL><DATA><xsl:value-of select="@id"/></DATA></COL>

        <COL><DATA><xsl:value-of select="@lastname"/></DATA></COL>

        <COL><DATA><xsl:value-of select="@firstname"/></DATA></COL>

        <COL><DATA><xsl:value-of select="@middlename"/></DATA></COL>

        <COL><DATA>
        <xsl:call-template name="formatDate">
        <xsl:with-param name="FMPdate" select="@birthday" />
        </xsl:call-template>
        </DATA></COL>

        <COL><DATA><xsl:value-of select="@gender"/></DATA></COL>

        <COL><DATA><xsl:value-of select="@religion"/></DATA></COL>

        <COL><DATA><xsl:value-of select="@pvsid"/></DATA></COL>

        <COL><DATA><xsl:value-of select="@osid"/></DATA></COL>

        <COL><DATA><xsl:value-of select="@bioguideid"/></DATA></COL>

        <COL><DATA><xsl:value-of select="@metavidid"/></DATA></COL>

        <COL><DATA><xsl:value-of select="@youtubeid"/></DATA></COL>

        <COL><DATA><xsl:value-of select="@twitterid"/></DATA></COL>

        <COL><DATA><xsl:value-of select="@icpsrid"/></DATA></COL>

        <COL><DATA><xsl:value-of select="@facebookgraphid"/></DATA></COL>

        <COL><DATA><xsl:value-of select="@thomasid"/></DATA></COL>

        <COL><DATA><xsl:value-of select="@lismemberid"/></DATA></COL>

        <COL><DATA><xsl:value-of select="@name"/></DATA></COL>

        <COL><DATA><xsl:value-of select="@title"/></DATA></COL>

        <COL><DATA><xsl:value-of select="@state"/></DATA></COL>

        <COL><DATA><xsl:value-of select="@district"/></DATA></COL>

        <xsl:for-each select="role">



        </xsl:for-each>

        </ROW>

        </xsl:for-each>

        </RESULTSET>

        </FMPXMLRESULT>

        </xsl:template>

        </xsl:stylesheet>

        --- In govtrack@yahoogroups.com, "cb299y" <cb299y@...> wrote:
        >
        > I wanted to offer some lessons I learned in putting this data up in Filemaker, as I would think others probably use that platform. (Hopefully this is the appropriate forum for this).
        >
        > 1. It might be helpful to people to describe the general transformation that needs to take place to load the data into Filemaker:
        >
        > a. The bulk data comes from govtrack as field/value pairs. To load into Filemaker, it needs to get translated into a list of field declarations (at the beginning), followed by all the data values (without field labels) in the same order. The data values are delimited by Filemaker-specific XML code. (See http://www.filemaker.com/help/html/import_export.16.30.html, or Google FMPXMLRESULT).
        >
        > b. There is some Filemaker specific static text that has to go into the file; it's documented in the link above.
        >
        > c. The Role data is presented by govtrack as if there could be multiple roles per person (because in the full history file there are). But as there is just one role per person in the Session-specific file (people.112), I
        > flattened the role data into the same record as the person fields. There needs to be some extra XSLT code to accomplish this which you will see in my XSLT file.
        >
        > d. The following article, from a Filemaker book (QUE CORPORATION - Using Filemaker pro X, 2003, p.193), helped me understand the basics of what I needed to do, although the code I needed to write differed in important ways:
        > http://flylib.com/books/en/4.353.1.193/1/.
        >
        > The key difference is this example accesses data in XML elements. govtrack data is placed in XML attributes, which you access differently. See my XSLT file.
        >
        > e. Working with XSLT is massively easier if you use a test bench site to test the code. This one, unlike all the others I tried, works well for this application because it displays the resulting XML rather than trying to execute it as web display code:
        > http://xslttest.appspot.com
        >
        > That way you can compare it to what Filemaker says you need.
        >
        > 2. I'll post my XSLT file separately. I'm sure it could be massively improved upon but it works.
        >
        > I'd welcome any comments or suggestions. Next I want to figure out how to update a joined table of current and historical data with the full historical people file.
        >
      Your message has been successfully submitted and would be delivered to recipients shortly.