XML to Excel - need to skip first row 1

[kshick]

I am exporting from a FileMaker database and using an xsl style sheet to convert to Excel. Because of my database design I have two header rows before the data rows. I want to skip the first row. Is there a way to do that in the xsl? Below is my code.

<?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="xsl fmp">
<xslutput method="text"/>

<xsl:template match="/">
<xsl:variable name="record" select="fmp:FMPXMLRESULT/fmp:RESULTSET/fmp:ROW[1]"/>
<xsl:if test="boolean($record/fmp:COL[1]/fmpATA)"><xsl:value-of select="$record/fmp:COL[1]/fmpATA"/>
</xsl:if><xsl:text>,</xsl:text>
<xsl:if test="boolean($record/fmp:COL[2]/fmpATA)"><xsl:value-of select="$record/fmp:COL[2]/fmpATA"/>
</xsl:if><xsl:text>,</xsl:text>
<xsl:if test="boolean($record/fmp:COL[3]/fmpATA)"><xsl:value-of select="$record/fmp:COL[3]/fmpATA"/>
</xsl:if><xsl:text>,</xsl:text>
<xsl:if test="boolean($record/fmp:COL[4]/fmpATA)"><xsl:value-of select="$record/fmp:COL[4]/fmpATA"/>
</xsl:if><xsl:text>
</xsl:text>

<xsl:for-each select="fmp:FMPXMLRESULT/fmp:RESULTSET/fmp:ROW">
<xsl:variable name="therecord" select="current()"/> <xsl:text>"</xsl:text>
<xsl:if test="boolean($therecord/fmp:COL[5]/fmpATA)"><xsl:value-of select="$therecord/fmp:COL[5]/fmpATA"/></xsl:if><xsl:text>","</xsl:text>
<xsl:if test="boolean($therecord/fmp:COL[6]/fmpATA)"><xsl:value-of select="$therecord/fmp:COL[6]/fmpATA"/></xsl:if><xsl:text>","</xsl:text>
<xsl:if test="boolean($therecord/fmp:COL[7]/fmpATA)"><xsl:value-of select="$therecord/fmp:COL[7]/fmpATA"/></xsl:if><xsl:text>","</xsl:text>
<xsl:if test="boolean($therecord/fmp:COL[8]/fmpATA)"><xsl:value-of select="$therecord/fmp:COL[8]/fmpATA"/></xsl:if>
<xsl:text>"
</xsl:text>
</xsl:for-each>
</xsl:template>
</xsl:stylesheet>

 

[tsuji]

Would this do?
[tt] <xsl:for-each select="fmp:FMPXMLRESULT/fmp:RESULTSET/fmp:ROW[blue][position()>1][/blue]">
<!-- etc etc -->
</xsl:for-each>
[/tt]

 

[kshick]

tsuji, thank you for the solution. However I tried several combinations with no success even though it looks intuitively correct. I am an XML neophyte. Would you mind inserting your code into my code so I know exactly where you thought it should go. -Ken

 

[tsuji]

I simply meant this, reproducing your script wholesale.
[tt]
<?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="xsl fmp">
<xslutput method="text"/>

<xsl:template match="/">
<xsl:variable name="record" select="fmp:FMPXMLRESULT/fmp:RESULTSET/fmp:ROW[1]"/>
<xsl:if test="boolean($record/fmp:COL[1]/fmpATA)"><xsl:value-of select="$record/fmp:COL[1]/fmpATA"/>
</xsl:if><xsl:text>,</xsl:text>
<xsl:if test="boolean($record/fmp:COL[2]/fmpATA)"><xsl:value-of select="$record/fmp:COL[2]/fmpATA"/>
</xsl:if><xsl:text>,</xsl:text>
<xsl:if test="boolean($record/fmp:COL[3]/fmpATA)"><xsl:value-of select="$record/fmp:COL[3]/fmpATA"/>
</xsl:if><xsl:text>,</xsl:text>
<xsl:if test="boolean($record/fmp:COL[4]/fmpATA)"><xsl:value-of select="$record/fmp:COL[4]/fmpATA"/>
</xsl:if><xsl:text>
</xsl:text>

<xsl:for-each select="fmp:FMPXMLRESULT/fmp:RESULTSET/fmp:ROW[red][position()>1][/red]">
<xsl:variable name="therecord" select="current()"/> <xsl:text>"</xsl:text>
<xsl:if test="boolean($therecord/fmp:COL[5]/fmpATA)"><xsl:value-of select="$therecord/fmp:COL[5]/fmpATA"/></xsl:if><xsl:text>","</xsl:text>
<xsl:if test="boolean($therecord/fmp:COL[6]/fmpATA)"><xsl:value-of select="$therecord/fmp:COL[6]/fmpATA"/></xsl:if><xsl:text>","</xsl:text>
<xsl:if test="boolean($therecord/fmp:COL[7]/fmpATA)"><xsl:value-of select="$therecord/fmp:COL[7]/fmpATA"/></xsl:if><xsl:text>","</xsl:text>
<xsl:if test="boolean($therecord/fmp:COL[8]/fmpATA)"><xsl:value-of select="$therecord/fmp:COL[8]/fmpATA"/></xsl:if>
<xsl:text>"
</xsl:text>
</xsl:for-each>
</xsl:template>
</xsl:stylesheet>
[/tt]

 

[kshick]

tsuji,it works. Thanks. This is the right track but it creates a blank row every other row. You can see that there is only one header row so that problem is solved. -Ken

CampaignNumber FirstName LastName

4 Evelyn Kanabuch

4 Tony Cavallo

4 Van Guyen

4 Roger Weathers

4 Wanda Smith

4 Ira Fewer

4 Matt Herrell

 

[tsuji]

I am not sure why the output is of that form; I would expect more of something like this?
[tt]
"4","Evelyn","Kanabuch"
"4","Tony","Cavallo"
etc...[/tt]

 

[kshick]

tsuji, it is of that form. I copied this from the csv file that was created. Every other line was empty. Any ideas? -ken

 

[kshick]

tsuji, I opened the file in Notepad and this is what I got. It is comma separated (CSV) but no field quotes except for the last value. It is the double commas that are strange and probably the problem. Any clues? -Ken

FirstName,LastName,Company
,,
Wanda,Smith,Louisiana/Louisiana State Departments
,,
Ira,Fewer,Washington/Washington State Departments
,,
Matt,Herrell,"Tennessee/Clinton, City Of"

Here is the export before your change. Two headers but the data fields are normal.

FirstName,LastName,Company
FName,LName,CoName
Wanda,Smith,Louisiana/Louisiana State Departments
Ira,Fewer,Washington/Washington State Departments
Matt,Herrell,"Tennessee/Clinton, City Of"

 

[tsuji]

[0] The handling of whitespace seems necessary as the output seems to reflect the columns' text contains line-break etc non-significant whitespace.

[1] Also the quotes and commas should be set up more rigorously.

[2] I would suggest the sector for-each be re-written like this, at the same time simplify it a bit by sparing the node-test, it shouldn't be necessary.
[tt]
<xsl:for-each select="fmp:FMPXMLRESULT/fmp:RESULTSET/fmp:ROW[position()>1]">
<xsl:variable name="therecord" select="current()"/>
<xsl:value-of select="concat('[/tt]"[tt]',normalize-space($therecord/fmp:COL[5]/fmpATA),'[/tt]"[tt]',',')"/>
<xsl:value-of select="concat([/tt]"[tt],normalize-space($therecord/fmp:COL[6]/fmpATA),'[/tt]"[tt]',',')"/>
<xsl:value-of select="concat('[/tt]"[tt]',normalize-space($therecord/fmp:COL[7]/fmpATA),'[/tt]"[tt]',',')"/>
<xsl:value-of select="concat('[/tt]"[tt]',normalize-space($therecord/fmp:COL[8]/fmpATA),'[/tt]"[tt]','[/tt]&#x0d;&#0a;[tt]')"/>
</xsl:for-each>
[/tt]

 

[tsuji]

re-take
[2:retake] Since the rendering of tgml of this site messes up terribly the entity, the quot entity has failed to disply literally. This is a retake (trying alternative, see if it renders better!!!)
[tt]
<xsl:for-each select="fmp:FMPXMLRESULT/fmp:RESULTSET/fmp:ROW[position()>1]">
<xsl:variable name="therecord" select="current()"/>
<xsl:value-of select="concat('[/tt]&#x22;[tt]',normalize-space($therecord/fmp:COL[5]/fmpATA),'[/tt]&#x22;[tt]',',')"/>
<xsl:value-of select="concat('[/tt]&#x22;[tt]',normalize-space($therecord/fmp:COL[6]/fmpATA),'[/tt]&#x22;[tt]',',')"/>
<xsl:value-of select="concat('[/tt]&#x22;[tt]',normalize-space($therecord/fmp:COL[7]/fmpATA),'[/tt]&#x22;[tt]',',')"/>
<xsl:value-of select="concat('[/tt]&#x22;[tt]',normalize-space($therecord/fmp:COL[8]/fmpATA),'[/tt]&#x22;[tt]','[/tt]&#x0d;&#0a;[tt]')"/>
</xsl:for-each>
[/tt]

 

[tsuji]

amendment
[2.1] there is a typo in the last line. It should be read like this.
[tt] <xsl:value-of select="concat('[/tt]&#x22;[tt]',normalize-space($therecord/fmp:COL[8]/fmpATA),'[/tt]&#x22;[tt]','[/tt]&#x0d;&#[highlight]x[/highlight]0a;[tt]')"/>[/tt]

 

[kshick]

thank you tsuji, got your correction. Tried many ways to get it to work. Let me show you where my confusion is.

Your code which works but has white space:
<xsl:for-each select="fmp:FMPXMLRESULT/fmp:RESULTSET/fmp:ROW[position()>1]">
<xsl:variable name="therecord" select="current()"/> <xsl:text>"</xsl:text>
<xsl:if test="boolean($therecord/fmp:COL[5]/fmpATA)"><xsl:value-of select="$therecord/fmp:COL[5]/fmpATA"/></xsl:if><xsl:text>","</xsl:text>
<xsl:if test="boolean($therecord/fmp:COL[6]/fmpATA)"><xsl:value-of select="$therecord/fmp:COL[6]/fmpATA"/></xsl:if><xsl:text>","</xsl:text>
<xsl:if test="boolean($therecord/fmp:COL[7]/fmpATA)"><xsl:value-of select="$therecord/fmp:COL[7]/fmpATA"/></xsl:if><xsl:text>","</xsl:text>
<xsl:if test="boolean($therecord/fmp:COL[8]/fmpATA)"><xsl:value-of select="$therecord/fmp:COL[8]/fmpATA"/></xsl:if>
<xsl:text>"
</xsl:text>
</xsl:for-each>
</xsl:template>
</xsl:stylesheet>

You end this code with:
<xsl:text>"
</xsl:text>
</xsl:for-each>
</xsl:template>
</xsl:stylesheet>

Your new code ends with:
<xsl:value-of select="concat('&#x22;',normalize-space($therecord/fmp:COL[8]/fmpATA),'&#x22;','&#x0d;&#x0a;')"/>

</xsl:for-each>

You took several tags out but are they needed?
<xsl:text>"
</xsl:text>

I get an exception error from FileMaker when exporting XML to the style sheet:
SAXParseException: whitespace expected (Occurrred in entity ‘C:\LeadCenter SupportFiles\Import_Export Files\Style Sheets\export_4.xsl’, at line 21, column 44.)

Any ideas? -Ken

 

[kshick]

tsuji, I got the following code to produce a a csv sheet but still has white space.

<?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="xsl fmp">
<xslutput method="text"/>

<xsl:template match="/">
<xsl:variable name="record" select="fmp:FMPXMLRESULT/fmp:RESULTSET/fmp:ROW[1]"/>
<xsl:if test="boolean($record/fmp:COL[1]/fmpATA)"><xsl:value-of select="$record/fmp:COL[1]/fmpATA"/>
</xsl:if><xsl:text>,</xsl:text>
<xsl:if test="boolean($record/fmp:COL[2]/fmpATA)"><xsl:value-of select="$record/fmp:COL[2]/fmpATA"/>
</xsl:if><xsl:text>,</xsl:text>
<xsl:if test="boolean($record/fmp:COL[3]/fmpATA)"><xsl:value-of select="$record/fmp:COL[3]/fmpATA"/>
</xsl:if><xsl:text>,</xsl:text>
<xsl:if test="boolean($record/fmp:COL[4]/fmpATA)"><xsl:value-of select="$record/fmp:COL[4]/fmpATA"/>
</xsl:if><xsl:text>
</xsl:text>

<xsl:for-each select="fmp:FMPXMLRESULT/fmp:RESULTSET/fmp:ROW[position()>1]">
<xsl:variable name="therecord" select="current()"/>
<xsl:value-of select="concat('&#x22;',normalize-space($therecord/fmp:COL[5]/fmpATA),'&#x22;',',')"/>
<xsl:value-of select="concat('&#x22;',normalize-space($therecord/fmp:COL[6]/fmpATA),'&#x22;',',')"/>
<xsl:value-of select="concat('&#x22;',normalize-space($therecord/fmp:COL[7]/fmpATA),'&#x22;',',')"/>
<xsl:value-of select="concat('&#x22;',normalize-space($therecord/fmp:COL[8]/fmpATA),'&#x22;','&#x0d;&#x0a;')"/>
</xsl:for-each>

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

This is what is in the csv sheet:
Company,FirstName,LastName
,,
Washington/Washington State Departments,Ira,Fewer
,,
"Tennessee/Clinton, City Of",Matt,Herrell

Very similar to what we had. -Ken

 

[tsuji]

[3] kshick, I would like to response point by point. But, it won't be helpful. I only point out a couple of points.
[3.1] The listing I posted "4 Jun 10 0:39" contains a single line of mine, namely, the for-each line with position() predicat. That listing is for your knowing what exactly I meant in my posting "3 Jun 10 3:01".
[3.2] I understand thereafter, per your feedback, there are non-signicant whitespace in the text node you want to retrieve and put into the csv.
[3.3] The line:
[tt] <xsl:text>"
</xsl:text>[/tt]
appeared exactly as what you posted originally. But, by itself, it may be partly due to the bad rendering engine of this site. It should already be of the form:
[tt] <xsl:text>"&#x0d[/tt];[tt]&#x0a[/tt];[tt]</xsl:text>[/tt]
The entities are be rendered rather than being taken literally by the site. Hence, that is an artifact. The rendering as such is still fine practically, but it makes whitespace one level more obscurred and I do not like it. That is another matter.
[3.4] In any case, please use exactly what I posted (with correction/retake). Just replace the block xsl:for-each, in the same spirit of what I posted earlier. (I wonder if my post on specific focal point is not clear in its intention or you do not really understand the xsl technology making its purpose uncertained?)
[3.5] In any case, the result you posted is not acceptable. It is not what would be anticipated under any circumstances. If a column is absent (without xsl:if testing), the result would be "" (empty); and it must be there, otherwise the csv cannot be interpreted correctly.

[4] I make an effort to list _all_ the xsl again.
[tt]
<?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="xsl fmp">
<xslutput method="text"/>

<xsl:template match="/">
<xsl:variable name="record" select="fmp:FMPXMLRESULT/fmp:RESULTSET/fmp:ROW[1]"/>
<xsl:if test="boolean($record/fmp:COL[1]/fmpATA)"><xsl:value-of select="$record/fmp:COL[1]/fmpATA"/>
</xsl:if><xsl:text>,</xsl:text>
<xsl:if test="boolean($record/fmp:COL[2]/fmpATA)"><xsl:value-of select="$record/fmp:COL[2]/fmpATA"/>
</xsl:if><xsl:text>,</xsl:text>
<xsl:if test="boolean($record/fmp:COL[3]/fmpATA)"><xsl:value-of select="$record/fmp:COL[3]/fmpATA"/>
</xsl:if><xsl:text>,</xsl:text>
<xsl:if test="boolean($record/fmp:COL[4]/fmpATA)"><xsl:value-of select="$record/fmp:COL[4]/fmpATA"/>
</xsl:if><xsl:text>&#x0d[/tt];[tt]&#x0a[/tt];[tt]</xsl:text>

<xsl:for-each select="fmp:FMPXMLRESULT/fmp:RESULTSET/fmp:ROW[position()>1]">
<xsl:variable name="therecord" select="current()"/>
<xsl:value-of select="concat('&#x22[/tt];[tt]',normalize-space($therecord/fmp:COL[5]/fmpATA),'&#x22[/tt];[tt]',',')"/>
<xsl:value-of select="concat('&#x22[/tt];[tt]',normalize-space($therecord/fmp:COL[6]/fmpATA),'&#x22[/tt];[tt]',',')"/>
<xsl:value-of select="concat('&#x22[/tt];[tt]',normalize-space($therecord/fmp:COL[7]/fmpATA),'&#x22[/tt];[tt]',',')"/>
<xsl:value-of select="concat('&#x22[/tt];[tt]',normalize-space($therecord/fmp:COL[8]/fmpATA),'&#x22[/tt];[tt]','&#x0d[/tt];[tt]&#x0a[/tt];[tt]')"/>
</xsl:for-each>
</xsl:template>
</xsl:stylesheet>[/tt]

 

[kshick]

tsuji, you have gone the extra mile with me. I am very grateful. I can't expect you to do any more. If I were knowledgeable with XML I might have given you some help but as you guessed I am a complete neophyte. I am doing this project because the architecture of my database design demands an XML export, not a simple csv export. However, I will have XML interface requirements in the future which will demand that I understand the basics of the technology. If you want to point me in the direction of resources I would appreciate that. For instance where can I find a resource to get elements like [position()>1]? Maybe you know a getting started resource.

Finally, I pasted your entire code into my xsl and still got white space. See below.

Company,FirstName,LastName
,,
Aerospace,Marvin,Simon
,,
Naval Aviation Depot,Fred,Stepp
,,
MITRE,phu-gui,feng
,,
Town of Westborough,Mark,Stockman

Unless you have another idea I want to thank you again for your help.

Ken from Georgia

 

[tsuji]

[5] You've to post one or two sets of columns exactly in the xml document working on, namely, those nodes in blue.
[tt]
<fmp:FMPXMLRESULT>
<fmp:RESULTSET>
<fmp:ROW>
...
</fmp:ROW>
...
<fmp:ROW>
<fmp:COL>...</fmp:COL>
<fmp:COL>...</fmp:COL>
<fmp:COL>...</fmp:COL>
<fmp:COL>...</fmp:COL>
[blue]<fmp:COL>
<fmpATA>...</fmpATA>
</fmp:COL>
<fmp:COL>
<fmpATA>...</fmpATA>
</fmp:COL>
<fmp:COL>
<fmpATA>...</fmpATA>
</fmp:COL>
<fmp:COL>
<fmpATA>...</fmpATA>
</fmp:COL>[/blue]
...
</fmp:ROW>
...
</fmp:RESULTSET>
</fml:FMPXMLRESULT>
[/tt]
Without seeing it, it is hopeless to draw further precise conclusion. The result is in no way that would be anticipated as a correct outcome even with fmpATA being of some complex structure rather than some simple text. And you've not tools or visually through gui to access or see the error messages (that you did not mention once)?

 

[kshick]

tsuji, when one doesn't have the knowledge or skills to accomplish something it is better to find another way to do it which I am doing now. I am going to fix this on the Server side and not use XML. It is clear that I have to come up to speed on XML before using it.

I can't thank you enough for sticking with me. Even though I am not using XML a solution came from this.

-Ken