Converting Delimited Text File into XML

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

pmukAsked:
Who is Participating?
 
Yury_DelendikCommented:
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
 
dualsoulCommented:
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
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
Yury_DelendikCommented:
XMLSpy had (maybe has now) standard Windows RTF editor. So loading in XMLSpy even 5Mb of data could be a problem :-)
0
 
dualsoulCommented:
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
 
pmukAuthor Commented:
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
 
dualsoulCommented:
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
 
pmukAuthor Commented:
There is one more way of converting into Xml files through XmlCsvReader in .Net Enviroment.  Have anyone familiar with this one?
0
 
Yury_DelendikCommented:
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
 
pmukAuthor Commented:
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
 
Yury_DelendikCommented:
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
 
pmukAuthor Commented:
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
 
pmukAuthor Commented:
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
 
Yury_DelendikCommented:
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
 
Yury_DelendikCommented:
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
 
pmukAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.