?
Solved

CSV to XML using XSLT

Posted on 2007-10-11
16
Medium Priority
?
1,507 Views
Last Modified: 2013-11-18
I have a file which is in the following format
1,2,3,4,5,6,7^a^b^c^d^e^f^g^h^i^j I need to convert it to an XML using XSLT. The csv file could change its format sometime later so need to use XSLT other than changing the code, changes in XSL should be enough

Could anyone help please. It is urgent.

YRKS

0
Comment
Question by:YRKS
  • 8
  • 8
16 Comments
 
LVL 60

Expert Comment

by:Geert Bormans
ID: 20061035
Since this is .net,
can you use XSLT2, with the .net version of saxon (www.saxonica.com)
of do you need xslt1 (which will be a lot trickier)

can you also show the result you expect from this?

can you wrap this in a root tag, like this?
<root>1,2,3,4,5,6,7^a^b^c^d^e^f^g^h^i^j</root>

XSLT requires wellformed XML as its source format
so you need to take precautions to escape occasional "&" into "&amp;", same with "<" and ">"
so preferably you use some dom methods to xml-ify the text string

I know I strongly advised against using XSLT for this task in your previous question
You must have good reasons to ignore that recommendation, so I hope you understand all the consequences
I really hope you can embed Saxon in your application and use XSLT2

cheers

Geert
0
 

Author Comment

by:YRKS
ID: 20061157
Thanks for your prompt reply.  I did use the other solution but just had a discussion with the group and they all want something that will run such that if the XML changes then we donot have to come back and recompile the program.

I did check the saxon site and realised that I need to purchase the products. There is no trial download. If the trial download works I could ask the company to go ahead and buy the software. As I cannot try out the software and see what it does cannot really recommend.

YRKS
0
 

Author Comment

by:YRKS
ID: 20061174
Did find an evaluation copy
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 60

Expert Comment

by:Geert Bormans
ID: 20061181
Hi YRKS,

you have misread the buying part
There are two Saxon Products
Saxon8B can be used free and is all you need
Saxon8SA is the schema aware alternative, you don't need it and even if you did, you can have an evaluation copy

look for Saxon8B, you can download and use that for free

cheers

Geert
0
 

Author Comment

by:YRKS
ID: 20061287
I have a further question before I invest to much time in this
my csv file 1,2,3,4,5,6,7^a^b^c^d^e^f^g^h^i^j

needs to be converted into an xml like
<a>
   <b>
         <c>1</c>
         <d>c</d>
         <e>2</e>
   </b>
  <f>
       <g>7</g>
     <h>i</h>
  </f>
</a>

So the order of the content in csv and xml output do not match. Is this possible to do with Saxon8B
Thanks for all your help
0
 
LVL 60

Expert Comment

by:Geert Bormans
ID: 20061499
Order is not important,
as long as there is a certain logic you can implement, you can do this
0
 

Author Comment

by:YRKS
ID: 20079093
I will be having mapping information ie  g element corresponds to the 7the position of the above csv(it is actually not even a csv first 7 positions are comma separated everything else is ^ separated.)  element h corresponds to the 18th position in the csv. Is that information enough. I will try out the saxon code sometime in the next few days.
0
 
LVL 60

Expert Comment

by:Geert Bormans
ID: 20079455
that would be enough
If you could handwrite an XML coming from the above strin, that would be better
0
 

Author Comment

by:YRKS
ID: 20079598
"If  you could handwrite an XML coming from the above strin, that would be better"

Are you suggesting that using the method mrihm: suggested is a better way to go.

 'Write the tag that represents the line item
   tw.WriteStartElement("element_name")

0
 
LVL 60

Expert Comment

by:Geert Bormans
ID: 20079680
no,no I meant given this string
1,2,3,4,5,6,7^a^b^c^d^e^f^g^h^i^j

show me the XML you want from it, plus why you did what,
All I need is some logic so I can develop the XSLT
I can't invent the rules myself
0
 

Author Comment

by:YRKS
ID: 20080164
Here is the xml I have it posted in the 3rd or 4th comment up here. You must have just missed it, thats the reason I got confused to. Thanks for all your help  and patience.


my csv file 1,2,3,4,5,6,7^a^b^c^d^e^f^g^h^i^j


needs to be converted into an xml like
<a>
   <b>
         <c>1</c>
         <d>c</d>
         <e>2</e>
   </b>
  <f>
       <g>7</g>
     <h>i</h>
  </f>
</a>
0
 
LVL 60

Expert Comment

by:Geert Bormans
ID: 20081110
no, no, I did not miss it, but what are the rules?
How do I know thatc,d,e need to be nested in b and g,h need to b enested in f
and why is c put as a value inside d?
0
 

Author Comment

by:YRKS
ID: 20083232
the xml and csv are files which are tranferred from one company to another. Each company has aformat it needs the data in company 1 needs it in an XML format and company 2 needs it in a csv format.  

This is a standard format of xml that is already defined like the one I showed and I need to construct the given xml from the csv,  the mapping is also predefined. There are no rules as such. both the csv and XML are messages which need to be send from one system to another in a particular format .

The client wants to have the flexibility of adding new feilds with minimum impact to the system and wants the entire process to be as generic as possible.
Thanks again for all your help and patience.
YRKS
0
 
LVL 60

Expert Comment

by:Geert Bormans
ID: 20083567
well, based on this information, I can only hardcode the XML
it won't be too flexible
0
 
LVL 60

Accepted Solution

by:
Geert Bormans earned 2000 total points
ID: 20083627
This would be the XSLT

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="2.0">
    <xsl:output indent="yes"/>
    <xsl:template match="root">
        <xsl:analyze-string select="translate(., ' ', '')" regex="^(\d+),(\d+),(\d+),(\d+),(\d+),(\d+),(\d+)\^([^\^]+)\^([^\^]+)\^([^\^]+)\^([^\^]+)\^([^\^]+)\^([^\^]+)\^([^\^]+)\^([^\^]+)\^([^\^]+)\^([^\^]+).*$">
            <xsl:matching-substring>
                <xsl:element name="{regex-group(8)}">
                    <xsl:element name="{regex-group(9)}">
                        <xsl:element name="{regex-group(10)}">
                            <xsl:value-of select="regex-group(1)"></xsl:value-of>
                        </xsl:element>
                        <xsl:element name="{regex-group(11)}">
                            <xsl:value-of select="regex-group(10)"></xsl:value-of>
                        </xsl:element>
                        <xsl:element name="{regex-group(12)}">
                            <xsl:value-of select="regex-group(2)"></xsl:value-of>
                        </xsl:element>
                    </xsl:element>
                    <xsl:element name="{regex-group(13)}">
                        <xsl:element name="{regex-group(14)}">
                            <xsl:value-of select="regex-group(7)"></xsl:value-of>
                        </xsl:element>
                        <xsl:element name="{regex-group(15)}">
                            <xsl:value-of select="regex-group(16)"></xsl:value-of>
                        </xsl:element>
                    </xsl:element>
                </xsl:element>
            </xsl:matching-substring>
            <xsl:non-matching-substring>
                <xsl:value-of select="."></xsl:value-of>
            </xsl:non-matching-substring>
        </xsl:analyze-string>
       
    </xsl:template>
</xsl:stylesheet>

If this were the XML to start from
<root>1,2,3,4,5,6,7^a^b^c^d^e^f^g^h^i^j</root>

cheers

Geert
0
 

Author Comment

by:YRKS
ID: 20087191
Hi Geert,

I will accept this as solution and then go create anohther question as it relates to setting up the environment correctly.
YRKS
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Originally, this post was published on Monitis Blog, you can check it here . In business circles, we sometimes hear that today is the “age of the customer.” And so it is. Thanks to the enormous advances over the past few years in consumer techno…
Simulator games are perfect for generating sample realistic data streams, especially for learning data analysis. It is even useful for demoing offerings such as Azure stream analytics, PowerBI etc.
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn the benefit of using external CSS files and the relationship between class and ID selectors. Create your external css file by saving it as style.css then set up your style tags: (CODE) Reference the nav tag and set your prop…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question