Solved

Converting Delimited Text File into XML

Posted on 2004-04-22
16
1,599 Views
Last Modified: 2013-11-19
Hi,

I want to convert a huge delimited text file (appr. 85MB) directly into XML in vb.net without looping through the text file as it is very large.  Is there any way, I can do that?

Thanks for all your help.

Rgds,
pmuk

0
Comment
Question by:pmuk
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
  • 3
16 Comments
 
LVL 10

Expert Comment

by:Yury_Delendik
ID: 10892060
0
 
LVL 15

Expert Comment

by:dualsoul
ID: 10892656
85MB - is not very large file.

you can use XSLT to do that, although this approach will build-up in-memory DOM-like tree structure, 85Mb won't grow too much may be ~4-5 times, i think you have enough memory? :)

also you can use some tool , like XMLSpy, which can do that. As i said 85Mb it's not a restriction.

For larger sized XML files (about 1Gb) i like the approach where you implement SAXReader interface which reads delimited file and fire SAX events according to your XML format. Then you just serialized SAX events to XML file. It's streaming. Very good for large sized XML.
Microsoft have SAX2 COM implementaion, so you can easily do it with VB.NET.

Hope it helps.
0
 
LVL 10

Expert Comment

by:Yury_Delendik
ID: 10894743
XMLSpy had (maybe has now) standard Windows RTF editor. So loading in XMLSpy even 5Mb of data could be a problem :-)
0
Are You Using the Best Web Development Editor?

The worlds of web hosting and web development are constantly evolving. Every year we see design trends change, coding standards adapt and new frameworks/CMS created. With such a quick pace of change it’s easy to get lost trying to keep up.

See if your editor made the list.

 
LVL 15

Expert Comment

by:dualsoul
ID: 10896506
just opened ~70Mb text file in XMLSpy - there were no problem :)
It tooks only 216Mb of memory ;) Opening time ~4min :)

Conversion of this file to XML tooks 412Mb and 30 seconds. So i think it's not bad for XMLSpy :)
0
 

Author Comment

by:pmuk
ID: 10900598
Thanks for your explanation....
I dont know any thing about XML Spy.  Can I work with XML spy in vb.net?  If so, then how to start with?  I mean, do I need to add any reference in vb.net?  Please Can you explain me how to work with XML Spy?

Thanks for all your help.

Rgds,
pmuk
0
 
LVL 15

Expert Comment

by:dualsoul
ID: 10900978
XMLSpy is a tool. Very good tool to work with XML related tasks. Check www.altova.com for evaluation and product info.

If you need to convert file once you can download it and just click 5 times to convert text file to xml.

If you need to convert such things in auto-mode (who knows may be you need it :)) you need another solution, using XSLT may be. Or write programm which parses your text file and writes XML to the output.
0
 

Author Comment

by:pmuk
ID: 10931064
There is one more way of converting into Xml files through XmlCsvReader in .Net Enviroment.  Have anyone familiar with this one?
0
 
LVL 10

Expert Comment

by:Yury_Delendik
ID: 10934015
Yes, my first link describes idea. Using next link you can download source: http://www.gotdotnet.com/Community/UserSamples/Details.aspx?SampleGuid=DDE579F1-836A-4562-A3CB-57A7DBBEBAE0

If you know how to you XmlReader (XPathDocument or XmlDocument) you can reuse it
0
 

Author Comment

by:pmuk
ID: 10939213
I have already gone through that article and downloaded the source and also pretty much familiar with XmlReader.  Do you know, how to compile XmlCsvReader into .dll ?

Thanks
0
 
LVL 10

Expert Comment

by:Yury_Delendik
ID: 10939440
Ways:
1. Open new C# class library and add files to project. Compile. (If you have no visual studio you can use command line compiler)
2. Open XmlCsvReader7.1.csproj change in its properties type of project to class library.
3. Load assembly "XmlCsvReader.exe" and create instance of "Microsoft.Xml.XmlCsvReader" with will be XmlReader
0
 

Author Comment

by:pmuk
ID: 10939561
I just did that and came to write the method to do, but found you too wrote the methods.  Anyways, Thanks Yury Delendik, for ur quick reply.  I will give a try using Xmlcsvreader class and let u know if any problem comes.

Also, I did not understand ur first link article.

0
 

Author Comment

by:pmuk
ID: 10943247
I have some questions regarding xmlcsvreader class :-

1. How to add attributes to the elements? Any
properties of xmlcsvreader to use? If so, what are
they?
2. How to add namespaceUri("http://www.w3.org/2001/XMLSchema", "http://www.w3.org/2001/XMLSchema-instance", e.t.c. )  & prefix(xsd,xsi...etc)
3. How to allow null values ?

-----------
my csv file is :
"customer_id","account_num","lname","fname","mi"
1,87462024688,"Nowmer","Sheri","A."
2,87470586299,"Whelply","Derrick","I."

the Xml file I got using XmlCsvReader is :
<customers>
  <customer>
    <customer_id>1</customer_id>
    <account_num>87462024688</account_num>
    <lname>Nowmer</lname>
    <fname>Sheri</fname>
    <mi>A.</mi>
  </customer>
  <customer>
    <customer_id>2</customer_id>
    <account_num>87470586299</account_num>
    <lname>Whelply</lname>
    <fname>Derrick</fname>
    <mi>I.</mi>
  </customer>
</customers>

I want to add namespaceUri & addributes to the element so that it allows nulls. So the final Xml output I want is like this:
<customers>
<customer xsd="http://www.w3.org/2001/XMLSchema" xsi="http://www.w3.org/2001/XMLSchema-instance" >
    <customer_id nil="true">1</customer_id>
    <account_num nil="true">87462024688</account_num>
    <lname nil="true">Nowmer</lname>
    <fname nil="true">Sheri</fname>
    <mi nil="true">A.</mi>
  </customer>
  <customer>
    <customer_id nil="true">2</customer_id>
    <account_num nil="true">87470586299</account_num>
    <lname nil="true">Whelply</lname>
    <fname nil="true">Derrick</fname>
    <mi nil="true">I.</mi>
  </customer>
</customers>
----------

As I am validating this Output Xml file against schema file & then using sqlxmlbulkload to bulk load into sqlserver 2000, the namespaceUri and nil attribute must be included in the xml file.

I appreciated any help on this.
0
 
LVL 10

Expert Comment

by:Yury_Delendik
ID: 10944135
Probably you wanted to say:

<customers xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" >
<customer >
    <customer_id>1</customer_id>
    <account_num>87462024688</account_num>
    <lname xsi:nil="true" />
    <fname>Sheri</fname>
    <mi xsi:nil="true" />
  </customer>
....

if data contains
...
1,87462024688,,"Sheri",
...
0
 
LVL 10

Expert Comment

by:Yury_Delendik
ID: 10944701
no. You have to use your own XmlReader-filter that will use XmlCsvReader except cases of root element and when element is empty (nil). It will be easy to write if you will understand how XmlCsvReader was written.
0
 

Author Comment

by:pmuk
ID: 10950856
sorry!! I wrote in parent element instead of root element.
-----------------------
what I want is :
<customers xsd="http://www.w3.org/2001/XMLSchema" xsi="http://www.w3.org/2001/XMLSchema-instance">
  <customer>
    <customer_id nil="true">1</customer_id>
    <account_num nil="true">87462024688</account_num>
    <lname nil="true">Nowmer</lname>
    <fname nil="true">Sheri</fname>
    <mi nil="true">A.</mi>
  </customer>
 </customers>

if the data file:
"customer_id","account_num","lname","fname","mi"
1,87462024688,"Nowmer","Sheri","A."
or
1,87462024688,"Nowmer","Sheri",""
-----------------------------------------------

Can u pls. explain how to write...using XmlReader - filter that uses XmlCsvReader for root and empty elements?
0
 
LVL 10

Accepted Solution

by:
Yury_Delendik earned 125 total points
ID: 10951085
Sorry but
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dndotnet/html/custxmlread.asp
explains it.

You can inherit from XmlCsvReader and override some functions like Read, XmlNodeType, GetAttribute, etc. It's hard but doable.

Because you need it only for create another xml file you can read XmlReader data and redirect it to XmlTextWriter for root and children elements you will append your custom attributes
0

Featured Post

Don't Cry: How Liquid Web is Ensuring Security

WannaCry is just the start. Read how Liquid Web is protecting itself and its customers against new threats.

Question has a verified solution.

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

Preface In the first article: A Better Website Login System (http://www.experts-exchange.com/A_2902.html) I introduced the EE Collaborative Login System and its intended purpose. In this article I will discuss some of the design consideratio…
I found this questions asking how to do this in many different forums, so I will describe here how to implement a solution using PHP and AJAX. The logical flow for the problem should be: Write an event handler for the first drop down box to get …
Viewers will learn about basic arrays, how to declare them, and how to use them. Introduction and definition: Declare an array and cover the syntax of declaring them: Initialize every index in the created array: Example/Features of a basic arr…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses

630 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