My question was how can we transform a flat text file in xml, I known how we can use XSLT to transform XML in a text file, reading on Michael Kay book it's mentioned that you can build your own parser to be able to transform a any source file, including a flat text file. But this is not a task a newbie want to delve in I'm sure.
So I made this simple snippet, I think that simple solutions are great :-)
I've made this code using Access 97, but you can with some easy stuff pass it to VB or Access 2000, I'm also using ADO2.6 and Jet4 sp3.
What I'm doing? I taking advantage of ADO ability to connect to any source, you can build your own OLE DB to access any source, but then again, we fall on the parser problem, but Jet already has a Text ISAM driver. I'm used to use it in Access and DAO, but it's also available in ADO. When you use it on Access you must build a import schema, that is kept in the MSysIMEXSpecs and MSysIMEXColumns tables, but you can also use a external file Schema.ini, that is the thing we need.
So I've build up a schema.ini file, open a recordset based on the flat text file and the schema.ini, and just persist it to XML. We have converted it to XML, it use the XDR schema of ADO, but here you can use XSLT to transform this into anything you want.
The schema.ini file must be in the same directory as the text file.
BTW - I've made some test with a somehow large file, and it's as a fast operation, I was thinking that this could be somehow a resource drain, but then I've to make more test, if you find something that you want to share with me please do so.