Creating XML structure 1

[gmoorthy]

I have an excel file with the data as follows: This file has about 10,000 rows


Column A Column B Column C

AB XYZ 12345
AB ABC 78900
AB ABC 23456


I want something which can read this file and create three structures as follows:

First Structure

<Data ID="1">
<Low><![CDATA[AB]]></Low>
</Data>
<Data ID="2">
<Low><![CDATA[AB]]></Low>
</Data>
<Data ID="3">
<Low><![CDATA[AB]]></Low>
</Data>


Second Structure :

<Data ID="1">
<Low><![CDATA[12345]]></Low>
</Data>
<Data ID="2">
<Low><![CDATA[78900]]></Low>
</Data>
<Data ID="3">
<Low><![CDATA[23456]]></Low>
</Data>



Third Structure

<Data ID="1">
<Low><![CDATA[XYZ]]></Low>
</Data>
<Data ID="2">
<Low><![CDATA[ABC]></Low>
</Data>
<Data ID="3">
<Low><![CDATA[ABC]></Low>
</Data>

 

[gmoorthy]

Either an excel macro which can easily automate the process for me.

Thanks,
Ganesh

 

[SkipVought]

Hi,

What code do you have so far?

Where are you stuck?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[gmoorthy]

Well i am not too savy with excel. So what i did was created three columns with the data and used concatenate to come up with the structure that i needed. But doing it for about 60,000 rows is painful and then manually i have to do the following

Ex :

/XML/ABC/TEG
Stucture 1 (will have thoousands of rows)
/XML/ABC/PEG
Structure 2 (will have thousands of rows)
/XML/ANC/QEP
Structure 3 (will have thousands of rows)

 

[SkipVought]

You output can be row for row, like this,
[tt]
<Data ID="1"><Low><![CDATA[AB]]></Low></Data>
<Data ID="2"><Low><![CDATA[AB]]></Low></Data>
<Data ID="3"><Low><![CDATA[AB]]></Low></Data>
[/tt]
That should be pretty simple right on your sheet, like...
[tt]
="'<Data ID="""&row()&"><Low><![CDATA["&A1&"]]></Low></Data>"
[/tt]
Here are my results in columns D:F
[tt]
<Data ID="1"><Low><![CDATA[AB]]></Low></Data> <Data ID="1"><Low><![CDATA[XYZ]]></Low></Data> <Data ID="1"><Low><![CDATA[12345]]></Low></Data>
<Data ID="2"><Low><![CDATA[AB]]></Low></Data> <Data ID="2"><Low><![CDATA[ABC]]></Low></Data> <Data ID="2"><Low><![CDATA[78900]]></Low></Data>
<Data ID="3"><Low><![CDATA[AB]]></Low></Data> <Data ID="3"><Low><![CDATA[ABC]]></Low></Data> <Data ID="3"><Low><![CDATA[23456]]></Low></Data>
[/tt]


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

I'd use this instead...
[tt]
="<Data ID="""&row()&"><Low><![CDATA["&A1&"]]></Low></Data>"
[/tt]


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[gmoorthy]

Skip,

So you are suggestion on my excel spreadsheet i just run this as a macro ? I have like 60,000 rows . Thanks a lot for your help.

="<Data ID="""&row()&"><Low><![CDATA["&A1&"]]></Low></Data>"

 

[SkipVought]

I said NOTHING about a macro!

This is a simple FORMULA on your sheet, copied across 2 columns an then down thru your 60,000 rows of data.

Ought to take less than ONE MINUTE!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[gmoorthy]

Skip ,

Thanks a lot . Is there a way once i write the formula you gave and run it the output comes in column D , E , F , I then need to do as follows:


/XML/Data1
Copy over 60,000 rows from column D
/XML/Data2
Copy over 60,000 rows from Column E
/XML/Data3
Copy over 60,000 rows from Column F


Is there an easy way to do this ?

 

[SkipVought]

COPY usually mean that you will then PASTE the rande somewhere.

Just relect a range and COPY!!!

It could not be simpler!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

If you make your source table a Structured Table WITH HEADINGS, when you add new data, for instance paste in a new range of 3 columns, the formulas will "automatically" adjust to the total number of rows of data you pasted in. Then you can record a macro that copies the range of your formulas, using the Structure Table range names and call that macro any time.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]