CSV->xml, how to eliminate columns

20 views

Skip to first unread message

Ali

unread,

Feb 4, 2014, 12:37:15 AM2/4/14

to xs...@googlegroups.com

I have this xml. It is actually a tab delimited file converted to xml. < R i="0" > contains the column heading and C=1 to C=8 are columns.

I need to eliminate for example "Size 1" and "Height 1" and create an xml output. I only want to eliminate Size 1 and Height 1 from each row and keep the rest.

Can you please help?

<Message>
<Header/>
<Data>
    <Data>
        <R i="0">
            <C i="0">Sample File</C>
            <C i="1">Sample Name</C>
            <C i="2">Dye</C>
            <C i="3">Size 1</C>
            <C i="4">Size 2</C>
            <C i="5">Height 1</C>
            <C i="6">Height 2</C>
            <C i="7">Peak Area 1</C>
            <C i="8">Peak Area 2</C>
        </R>
        <R i="1">
            <C i="0">100CTRL-NEW3-LOT_2014-01-21_D06.fsa</C>
            <C i="1">100CTRL-NEW3-LOT</C>
            <C i="2">B</C>
            <C i="3">143.84</C>
            <C i="4">148.3</C>
            <C i="5">3331</C>
            <C i="6">2024</C>
            <C i="7">30513</C>
            <C i="8">18020</C>
        </R>
        <R i="2">
            <C i="0">100CTRL-NEW3-LOT_2014-01-21_D06.fsa</C>
            <C i="1">100CTRL-NEW3-LOT</C>
            <C i="2">B</C>
            <C i="3">204.1</C>
            <C i="4"/>
            <C i="5">4461</C>
            <C i="6"/>
            <C i="7">40664</C>
            <C i="8"/>
        </R>
    </Data>
</Data>

</Message>

Ali

unread,

Feb 4, 2014, 1:09:00 AM2/4/14

to xs...@googlegroups.com

Also

I have the un-wanted columns defined in xsl:param. For example not needed columns are defined as –

<xsl:param name="notNeeded">'size 1', 'Height 1', 'and so on'</xsl:param>

I know I have to iterate over these params and eliminate columns based on these params. How would I go about doing that? Thanks.

dshcs

unread,

Feb 4, 2014, 8:05:26 AM2/4/14

to xs...@googlegroups.com

Ali: The below transform seems to work. It uses a Result Tree Fragment (RTF) to hold the list of columns to be removed. It also uses the Microsoft node-set function. You should be able to pass the RTF in as a parameter.

<?xml version="1.0" encoding="ISO-8859-1"?>
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:msxsl="urn:schemas-microsoft-com:xslt">
<xsl:output method="xml" encoding="UTF-8" indent="yes"/>

<xsl:variable name="rtfUnused1">
<value>Dye</value>
<value>Size 1</value>
</xsl:variable>
<xsl:variable name="nsUnused1" select="msxsl:node-set($rtfUnused1)"/>

<xsl:variable name="rtfUnused2">
    <xsl:for-each select="/Message/Data/Data/R[@i=0]/C[text() = $nsUnused1/value]">
      <value><xsl:value-of select="@i"/></value>
    </xsl:for-each>
</xsl:variable>
<xsl:variable name="nsUnused2" select="msxsl:node-set($rtfUnused2)"/>

<xsl:template match="/">
    <xsl:message><xsl:text>rtfUnused1=</xsl:text><xsl:copy-of select="$rtfUnused1"/></xsl:message>
    <xsl:message><xsl:text>rtfUnused2=</xsl:text><xsl:copy-of select="$rtfUnused2"/></xsl:message>
    <xsl:copy><xsl:apply-templates select="@* | node()"/></xsl:copy>
</xsl:template>

<xsl:template match="@* | node()"> 
    <xsl:copy>
      
      <xsl:apply-templates select="@* | *[name() != 'C' or (name() = 'C' and not(@i = $nsUnused2/value))]"/>
    </xsl:copy>
</xsl:template>

</xsl:stylesheet>

dshcs

unread,

Feb 4, 2014, 8:10:00 AM2/4/14

to xs...@googlegroups.com

Oops, forgot the to include the text and the results, so update one line in the XSL

<xsl:apply-templates select="@* | *[name() != 'C' or (name() = 'C' and not(@i = $nsUnused2/value))] | text()"/>

and the results are

<Data>
    <Data>
        <R i="0">
            <C i="0">Sample File</C>
            <C i="1">Sample Name</C>

            <C i="4">Size 2</C>
            <C i="5">Height 1</C>
            <C i="6">Height 2</C>
            <C i="7">Peak Area 1</C>
            <C i="8">Peak Area 2</C>
        </R>
        <R i="1">
            <C i="0">100CTRL-NEW3-LOT_2014-01-21_D06.fsa</C>
            <C i="1">100CTRL-NEW3-LOT</C>

Ali

unread,

Feb 4, 2014, 11:00:40 AM2/4/14

to xs...@googlegroups.com

Thank you. I am using Altova and don't know how to perform node-set function. Please see my input and desire output. I tried zsl, but no working. I will also see what is the logic inyour transformation.

First I have to group based on "Sample File" in row @i="1" and then eliminate some column. Any more help is appreciated. Thanks.

input xml.

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

<Instance>DataMarshalerBC</Instance>

</Header>

<Data>

<C i="0">Sample File</C>

<C i="1">Sample Name</C>

<C i="5">Height 1</C>

<C i="6">Height 2</C>

</R>

</R>

</R>

</R>

</R>

</R>

</R>

</R>

</R>

</R>

</R>

</R>

</R>

</R>

</R>

</R>

</R>

</R>

</R>

</R>

</R>

</R>

</R>

</R>

</R>

</R>

</R>

</R>

</R>

</R>

</R>

</R>

</R>

</Data>

<Attachment Encoding="base64" Location="element" Name="Combined_GenotypesTable.txt">U2FtcGxlIEZpbGUJU2FtcGxlIE5hbWUJRHllCVNpemUgMQlTaXplIDI

JSGVpZ2h0IDEJSGVpJUgpOVENfMjAxNC0wMS0yMV9DMDEuZnNhCU5UQwlSCk5UQ18yMDE0LTAxLTIxX0MwMS5mc2EJTlRDCVIK</Attachment>

</Attachments>

</Message>

------------------------------------- Desire Output -------------------------------------

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

<Instance>DataMarshalerBC</Instance>

</Header>

<Data>

<C i="0">Sample File</C>

<C i="1">Sample Name</C>

<C i="5">Height 1</C>

<C i="6">Height 2</C>

</R>

</R>

</R>

</R>

</R>

</R>

</R>

</R>

</R>

</R>

</R>

</R>

</R>

</R>

</R>

</R>

</R>

</Data>

</Attachments>

</Message>

<Instance>DataMarshalerBC</Instance>

</Header>

<Data>

<C i="0">Sample File</C>

<C i="1">Sample Name</C>

<C i="5">Height 1</C>

<C i="6">Height 2</C>

</R>

</R>

</R>

</R>

</R>

</R>

</R>

</R>

</R>

</R>

</R>

</R>

</R>

</R>

</R>

</R>

</R>

</Data>

</Attachments>

</Message>

</Records>

Ali

unread,

Feb 4, 2014, 11:05:31 AM2/4/14

to xs...@googlegroups.com

My XSL

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

<xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:fn="http://www.w3.org/2005/xpath-functions">

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

<xsl:param name="notNeeded">'size 1', 'Height 1', 'Peak Area 1'</xsl:param>

<xsl:variable name="NotNeededColumns" select="tokenize($notNeeded, ',')"/>

<xsl:key name="header" match="C[parent::R/@i='0']" use="@i" />

<xsl:variable name="folder" select="substring-before(//Attachments/Attachment/@Name, '.')"/>

<xsl:variable name="d" select="current-dateTime()"/>

<xsl:template match="Message">

<xsl:variable name="columnHeader" select="Data/Data/R[@i = 0]"/>

<xsl:variable name="data" select="//Data/R[@i > 0]"/>

<xsl:for-each-group select="$data" group-by="C[@i = 0]">

<xsl:variable name="filename" select="C[@i = 0]"/>

<Instance>DataMarshalerBC</Instance>

</Header>

<Data>

<xsl:copy-of select="$columnHeader"/>

<xsl:for-each select="current-group()">

<xsl:element name="R">

<xsl:attribute name="i" select="position()"/>

<xsl:for-each select="$NotNeededColumns">

<xsl:copy-of select="R/C[key('header', @i) != $NotNeededColumns[1]]"/>

</xsl:for-each>

</xsl:element>

</xsl:for-each>

</Data>

<xsl:element name="Attachment">

<xsl:attribute name="Encoding" select="'base64'"/>

<xsl:attribute name="Location" select="'element'"/>

<xsl:attribute name="Name" select="concat($folder, '-', format-dateTime($d, '[M01]-[D01]-[Y0001]-[H01][m01]'), '_', $filename)"/>

</xsl:element>

</Attachments>

</Message>

</xsl:for-each-group>

</Records>

</xsl:template>

<xsl:template match="Header"/>

</xsl:stylesheet>

On Tuesday, February 4, 2014 12:37:15 AM UTC-5, Ali wrote:

Reply all

Reply to author

Forward

0 new messages