How to delete specific rows from an external file?

[jerry000]

Hi,

I have an external file that consists of XML tags. I want to extract only those tags that have a valid value and get rid of the rest.

Here's an example:

My raw data file has the following data:

<Row>
<Cell ss:StyleID="s77"><Data ss:Type="String">Cost of Goods Sold</Data></Cell>
<Cell ss:StyleID="s77"/>
<Cell ss:StyleID="s76" ss:Formula="=IF(R[608]C=&quot;&quot;,&quot;&quot;,R[608]C)"><Data
ss:Type="Number">2285.4549999999999</Data></Cell>
<Cell ss:StyleID="s76" ss:Formula="=IF(R[608]C=&quot;&quot;,&quot;&quot;,R[608]C)"><Data
ss:Type="Number">2087.239</Data></Cell>
<Cell ss:StyleID="s76" ss:Formula="=IF(R[608]C=&quot;&quot;,&quot;&quot;,R[608]C)"><Data
ss:Type="Number">2082.4839999999999</Data></Cell>
<Cell ss:StyleID="s76" ss:Formula="=IF(R[608]C=&quot;&quot;,&quot;&quot;,R[608]C)"><Data
ss:Type="Number">2537.913</Data></Cell>
<Cell ss:StyleID="s76" ss:Formula="=IF(R[608]C=&quot;&quot;,&quot;&quot;,R[608]C)"><Data
ss:Type="Number">4086.61</Data></Cell>
<Cell ss:StyleID="s76" ss:Formula="=IF(R[608]C=&quot;&quot;,&quot;&quot;,R[608]C)"><Data
ss:Type="String"></Data></Cell>
<Cell ss:StyleID="s76" ss:Formula="=IF(R[608]C=&quot;&quot;,&quot;&quot;,R[608]C)"><Data
ss:Type="String"></Data></Cell>
<Cell ss:StyleID="s76" ss:Formula="=IF(R[608]C=&quot;&quot;,&quot;&quot;,R[608]C)"><Data
ss:Type="String"></Data></Cell>
<Cell ss:StyleID="s76" ss:Formula="=IF(R[608]C=&quot;&quot;,&quot;&quot;,R[608]C)"><Data
ss:Type="String"></Data></Cell>
<Cell ss:StyleID="s76" ss:Formula="=IF(R[608]C=&quot;&quot;,&quot;&quot;,R[608]C)"><Data
ss:Type="String"></Data></Cell>
<Cell ss:StyleID="s76" ss:Formula="=IF(R[608]C=&quot;&quot;,&quot;&quot;,R[608]C)"><Data
ss:Type="String"></Data></Cell>
<Cell ss:StyleID="s76" ss:Formula="=IF(R[608]C=&quot;&quot;,&quot;&quot;,R[608]C)"><Data
ss:Type="String"></Data></Cell>
<Cell ss:StyleID="s76" ss:Formula="=IF(R[608]C=&quot;&quot;,&quot;&quot;,R[608]C)"><Data
ss:Type="String"></Data></Cell>
<Cell ss:StyleID="s76" ss:Formula="=IF(R[608]C=&quot;&quot;,&quot;&quot;,R[608]C)"><Data
ss:Type="String"></Data></Cell>
<Cell ss:StyleID="s76" ss:Formula="=IF(R[608]C=&quot;&quot;,&quot;&quot;,R[608]C)"><Data
ss:Type="String"></Data></Cell>
<Cell ss:StyleID="s76" ss:Formula="=IF(R[608]C=&quot;&quot;,&quot;&quot;,R[608]C)"><Data
ss:Type="String"></Data></Cell>
<Cell ss:StyleID="s76" ss:Formula="=IF(R[608]C=&quot;&quot;,&quot;&quot;,R[608]C)"><Data
ss:Type="String"></Data></Cell>
<Cell ss:StyleID="s76" ss:Formula="=IF(R[608]C=&quot;&quot;,&quot;&quot;,R[608]C)"><Data
ss:Type="String"></Data></Cell>
<Cell ss:StyleID="s76" ss:Formula="=IF(R[608]C=&quot;&quot;,&quot;&quot;,R[608]C)"><Data
ss:Type="String"></Data></Cell>
<Cell ss:StyleID="s76" ss:Formula="=IF(R[608]C=&quot;&quot;,&quot;&quot;,R[608]C)"><Data
ss:Type="String"></Data></Cell>
<Cell ss:StyleID="s76" ss:Formula="=IF(R[608]C=&quot;&quot;,&quot;&quot;,R[608]C)"><Data
ss:Type="String"></Data></Cell>
<Cell ss:StyleID="s76" ss:Formula="=IF(R[608]C=&quot;&quot;,&quot;&quot;,R[608]C)"><Data
ss:Type="String"></Data></Cell>
</Row>

I'm having a hard time figuring out how to get rid of unwanted rows. In the above case the final output should be:

<Row>
<Cell ss:StyleID="s77"><Data ss:Type="String">Cost of Goods Sold</Data></Cell>
<Cell ss:StyleID="s77"/>
<Cell ss:StyleID="s76" ss:Formula="=IF(R[608]C=&quot;&quot;,&quot;&quot;,R[608]C)"><Data
ss:Type="Number">2285.4549999999999</Data></Cell>
<Cell ss:StyleID="s76" ss:Formula="=IF(R[608]C=&quot;&quot;,&quot;&quot;,R[608]C)"><Data
ss:Type="Number">2087.239</Data></Cell>
<Cell ss:StyleID="s76" ss:Formula="=IF(R[608]C=&quot;&quot;,&quot;&quot;,R[608]C)"><Data
ss:Type="Number">2082.4839999999999</Data></Cell>
<Cell ss:StyleID="s76" ss:Formula="=IF(R[608]C=&quot;&quot;,&quot;&quot;,R[608]C)"><Data
ss:Type="Number">2537.913</Data></Cell>
<Cell ss:StyleID="s76" ss:Formula="=IF(R[608]C=&quot;&quot;,&quot;&quot;,R[608]C)"><Data
ss:Type="Number">4086.61</Data></Cell>
<Cell ss:StyleID="s76" ss:Formula="=IF(R[608]C=&quot;&quot;,&quot;&quot;,R[608]C)"><Data
ss:Type="String"></Data></Cell>
</Row>

Could someone please help me on this one?

Many Thanks,
Jerry

 

[klaz2002]

Why not 'read' the each set of tags as a record. Then logically 'decide' if it meets your needs. Keep only ones that meet your rules and then output it back to the same file. (overwrite the existing file).
Klaz

 

[asandy1234]

There are lot of options

1) Treat it as a text file use some shell script to parse the string ( If you need one I have a sample)

2) Save it in the database as CLOB and use xquery to get the results.

3) Use SAS XML Mapper to create the schema and import it into SAS.

Hope this helps.