Convert CSV to XML

re: Convert CSV to XML

Martin Spedding — Thu, 17 Apr 2003 18:32:00 GMT

Hiya,
have look at

http://www.develop.com/devresources/resourcedetail.aspx?type=t&id=864

It is an implementation of a custom xmlreader and xmlwriter. If I remember correctly there is an example for a csv file and for a zip file. This way the csv appears as though it is an XML file and it means that you can perform xpath queries or xsl transformations on the csv file without having to transform it directly to an xml file.

Have a good Easter

Martin

re: Convert CSV to XML

Bjørn Reppen — Thu, 24 Apr 2003 11:06:00 GMT

Why not just write it in IL to make sure nobody can read the code? :-D

re: Convert CSV to XML

Dimitre Novatchev — Sat, 03 May 2003 15:24:00 GMT

This is something trivial when using the FXSL functional programming library for XSLT.

Just use the "str-split-to-words" template.

This template is quite general. For example it allows to specify *a set of* different potential delimiters, each of which may be used at some place and more than one of them may be used in the same string.

To illustrate the power of "str-split-to-words", there's the solution of the problem to "re-allocate" a relative file path, when the template that contains the rel-path (e.g. in xsl:import) is moved into a different folder.

See it here:

http://groups.google.com/groups?dq=&hl=en&lr=&ie=UTF-8&oe=UTF-8&group=microsoft.public.xml&selm=b8ujil%24dpl9r%241%40ID-152440.news.dfncis.de

Cheers,

=====
Cheers,

Dimitre Novatchev.
http://fxsl.sourceforge.net/ -- the home of FXSL

Cool info : Bob.NET

TrackBack — Thu, 17 Apr 2003 21:01:00 GMT

Cool info : Bob.NET

The output is actually different

Paul Owen — Wed, 16 Jun 2004 13:49:00 GMT

The output is not actually as you've written it there. It would actually be:

<root>
<row>
<elem>1</elem>
<elem>5</elem>
<elem>main</elem>
<elem>st</elem>
<elem/>
<elem>Cumming</elem>
<elem> GA</elem>
<elem> 30040</elem>
<elem>Kirk Evans</elem>
</row>
<row>
<elem>2</elem>
<elem>13</elem>
<elem>elm</elem>
<elem>st</elem>
<elem/>
<elem>Anywhere</elem>
<elem> NJ</elem>
<elem> 07825</elem>
<elem>Bob</elem>
<elem>Smith</elem>
</row>
</root>

which is quite different to what was intended. The problem I think is that the CR and LF are not interpreted correctly according to how you've written this script. They are seen as whitespace and included in the string pattern.

I'm really shocked that I'm the only person who spotted this ...

Paul Owen — Wed, 16 Jun 2004 17:45:00 GMT

The correct way is to declare the LF entity properly at the top of the document. Like this:

<xsl:variable name="LF" select="'
'"/>

and then reference it in the document. So instead of this:

<xsl:with-param name ="StringToTransform" select ="substring-before($StringToTransform,' ')" />

you have this:

<xsl:with-param name ="StringToTransform" select ="substring-before($StringToTransform,$LF)" />

which will give the desired output as outlined in the article. I'd like to be on a Microsoft Corporation salary like the author, but obviously I'm too bothered about accuracy of code ... ;)

re: Convert CSV to XML

Kirk Allen Evans — Wed, 16 Jun 2004 21:44:00 GMT

This post and others containing XSLT have been problematic. The HTML WYSIWYG editor used for .Text at the time of this post's writing automatically converted character entities, and ScottW added code in the backend to strip and replace markup when he saw that people were adding markup that altered the presentation of the main feed on http://weblogs.asp.net. In fact, if you replace all instances of ' ' with 
 as the article originally included, the XSLT works as advertised (instead of creating a parameter, I referenced the character entity inline). I did not go back and alter the post to accomodate this change in .Text.

re: Convert CSV to XML

Piyush — Thu, 24 Jun 2004 09:06:00 GMT

How do I embed the "<root>" tags in the .csv file, using javascript or XSL?
Plus, how do I use the aforementioned stylesheet with the .csv file? Since there's no XML declartion in the .csv file (<?xml version...), I'm pretty confused about how to implement all this to convert my csv into xml.

re: Convert CSV to XML

Paul Owen — Tue, 06 Jul 2004 19:35:00 GMT

Yes, that fixed it Kirk - nice job.

It's a nice little script.

It's a small point I've found whilst using it, that if you're processing UNIX and Windows-generated CSV files, it's more reliable to delimit rows on the CR 013 character than the LF 010.

I have a variable at the top of the adapted XSLT as follows:

<xsl:variable name="CR" select"''"/>

...

<xsl:if test="contains($StringToTransform,$CR)">

...

which doesn't care if CR (UNIX) is used or CRLF (DOS/Windows).