Solved

Converting Delimited Text File into XML

Posted on 2004-04-22
16
1,586 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
  • 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
 
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
wordpress limitations 4 91
CSS (line height?) issue 2 56
Help with SimpleXML charts with PHP 4 63
Test ddwrt:UserLookup 1 20
Browsers only know CSS so your awesome SASS code needs to be translated into normal CSS. Here I'll try to explain what you should aim for in order to take full advantage of SASS.
JavaScript has plenty of pieces of code people often just copy/paste from somewhere but never quite fully understand. Self-Executing functions are just one good example that I'll try to demystify here.
Viewers will learn about the different types of variables in Java and how to declare them. Decide the type of variable desired: Put the keyword corresponding to the type of variable in front of the variable name: Use the equal sign to assign a v…
The viewer will learn how to dynamically set the form action using jQuery.

708 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now