Flat File To XML Roundtrip

[Glenn Owens]

OK I'm on a steep learning curve with XML et.al. and need some advice.

I'm writing a B2B front door for a new application. I have multiple
data suppliers all sending various formats of flat files. Most of the
inbound files are CSV (but a couple are tab-delimitted and one is an
Excel spreadsheet). The goal is to read/process/validate each file and
to "transform" the data into a common, internal, file format which
will by sent on to a downstream application for final processing and
consumption. I'm using VB.Net 2003 as my development platform.

My novice approach to do this will be to:

1) Convert each inbound file to XML (hopfully using some
template-driven parser). I'd like to maintain x number of
file-descriptor templates but use common code to convert the flat
files.

2) Use XSLT to transform the new inbound XML file into the output
target file format

3) Load the XSLT-transformed file (now having the desired outbound XML
format) into a DataSet (for validation and processing)

4) Write/Convert the validated contents of the DataSet to the output
flat file.

My Question is...

Does this sound like a good approach? and are there any good how-to
examples which address these steps? I need to get the job done but I'm
also wanting to use this as a good training exercise in .Net and XML.

Thanks in advance for your input!

[Chris Lovett]

In there user samples on www.gotdotnet.com you will find an XmlCsvReader
which may be of some help to you for step (1).

Converting the DataSet back to XML is easy, then from XML back to CSV can be
done with an XSL transform, which I think is included in teh XmlCsvReader
download.

So yes, this approach makes sense. A lot of folks are standardizing on XML
for this kind of processing. Hopefully eventually, you'll be able to
convince your business partner to just send you XML at some point in the
future, and then you're processing will get a bit more streamlined.
"Glenn Owens" <go****@nixonpeabody.com> wrote in message
news:bc**************************@posting.google.c om...

OK I'm on a steep learning curve with XML et.al. and need some advice.

I'm writing a B2B front door for a new application. I have multiple
data suppliers all sending various formats of flat files. Most of the
inbound files are CSV (but a couple are tab-delimitted and one is an
Excel spreadsheet). The goal is to read/process/validate each file and
to "transform" the data into a common, internal, file format which
will by sent on to a downstream application for final processing and
consumption. I'm using VB.Net 2003 as my development platform.

My novice approach to do this will be to:

1) Convert each inbound file to XML (hopfully using some
template-driven parser). I'd like to maintain x number of
file-descriptor templates but use common code to convert the flat
files.

2) Use XSLT to transform the new inbound XML file into the output
target file format

3) Load the XSLT-transformed file (now having the desired outbound XML
format) into a DataSet (for validation and processing)

4) Write/Convert the validated contents of the DataSet to the output
flat file.

My Question is...

Does this sound like a good approach? and are there any good how-to
examples which address these steps? I need to get the job done but I'm
also wanting to use this as a good training exercise in .Net and XML.

Thanks in advance for your input!

[Chris Lovett]

In there user samples on www.gotdotnet.com you will find an XmlCsvReader
which may be of some help to you for step (1).

Converting the DataSet back to XML is easy, then from XML back to CSV can be
done with an XSL transform, which I think is included in teh XmlCsvReader
download.

So yes, this approach makes sense. A lot of folks are standardizing on XML
for this kind of processing. Hopefully eventually, you'll be able to
convince your business partner to just send you XML at some point in the
future, and then you're processing will get a bit more streamlined.
"Glenn Owens" <go****@nixonpeabody.com> wrote in message
news:bc**************************@posting.google.c om...

OK I'm on a steep learning curve with XML et.al. and need some advice.

I'm writing a B2B front door for a new application. I have multiple
data suppliers all sending various formats of flat files. Most of the
inbound files are CSV (but a couple are tab-delimitted and one is an
Excel spreadsheet). The goal is to read/process/validate each file and
to "transform" the data into a common, internal, file format which
will by sent on to a downstream application for final processing and
consumption. I'm using VB.Net 2003 as my development platform.

My novice approach to do this will be to:

1) Convert each inbound file to XML (hopfully using some
template-driven parser). I'd like to maintain x number of
file-descriptor templates but use common code to convert the flat
files.

2) Use XSLT to transform the new inbound XML file into the output
target file format

3) Load the XSLT-transformed file (now having the desired outbound XML
format) into a DataSet (for validation and processing)

4) Write/Convert the validated contents of the DataSet to the output
flat file.

My Question is...

Does this sound like a good approach? and are there any good how-to
examples which address these steps? I need to get the job done but I'm
also wanting to use this as a good training exercise in .Net and XML.

Thanks in advance for your input!

[Glenn Owens]

"Chris Lovett" <chris@!nospam!.net> wrote in message news:<10*************@corp.supernews.com>...

In there user samples on www.gotdotnet.com you will find an XmlCsvReader
which may be of some help to you for step (1).

Converting the DataSet back to XML is easy, then from XML back to CSV can be
done with an XSL transform, which I think is included in teh XmlCsvReader
download.

So yes, this approach makes sense. A lot of folks are standardizing on XML
for this kind of processing. Hopefully eventually, you'll be able to
convince your business partner to just send you XML at some point in the
future, and then you're processing will get a bit more streamlined.

Chris, thanks for the response. I am currently looking at the
XmlCsvReader - it definitely looks like something that I could use.
Thanks for your hard work and pointing me in this direction.

Can it be modified? (it does appear to be copywritten)...

Is there a way to use an .XSD file to describe/interpret the column
tags instead of using the first line of the file to define the column
names (tags)? This would allow me to fully describe any of the input
files, using separate schemas, without needing to modify them before
the initial XML conversion. The added bonus would be to use the .XSD
files to validate the structure, datatype, cardinality, etc. - could
the XmlCsvReader descend from XmlValidatingReader?

Also, I came across a really good MSDN link article on customizing the
XmlReader:

http://msdn.microsoft.com/webservice...ustxmlread.asp

You are correct about the furture of our B2B. This is just the first
step before we "potentially" begin requesting each of our data vendors
to supply well-formed XML input streams (preferably via a web
service). But, we needed to get this part done reasonably quickly.

Thanks again,
Glenn

[Glenn Owens]

"Chris Lovett" <chris@!nospam!.net> wrote in message news:<10*************@corp.supernews.com>...

In there user samples on www.gotdotnet.com you will find an XmlCsvReader
which may be of some help to you for step (1).

Converting the DataSet back to XML is easy, then from XML back to CSV can be
done with an XSL transform, which I think is included in teh XmlCsvReader
download.

So yes, this approach makes sense. A lot of folks are standardizing on XML
for this kind of processing. Hopefully eventually, you'll be able to
convince your business partner to just send you XML at some point in the
future, and then you're processing will get a bit more streamlined.

Chris, thanks for the response. I am currently looking at the
XmlCsvReader - it definitely looks like something that I could use.
Thanks for your hard work and pointing me in this direction.

Can it be modified? (it does appear to be copywritten)...

Is there a way to use an .XSD file to describe/interpret the column
tags instead of using the first line of the file to define the column
names (tags)? This would allow me to fully describe any of the input
files, using separate schemas, without needing to modify them before
the initial XML conversion. The added bonus would be to use the .XSD
files to validate the structure, datatype, cardinality, etc. - could
the XmlCsvReader descend from XmlValidatingReader?

Also, I came across a really good MSDN link article on customizing the
XmlReader:

http://msdn.microsoft.com/webservice...ustxmlread.asp

You are correct about the furture of our B2B. This is just the first
step before we "potentially" begin requesting each of our data vendors
to supply well-formed XML input streams (preferably via a web
service). But, we needed to get this part done reasonably quickly.

Thanks again,
Glenn

[Chris Lovett]

The eula is very open. Feel free to use it in your product and make
whatever changes necessary.

You could read the XSD and then pass in the column names:

XmlCsvReader reader = new XmlCsvReader(uri, nameTable);
reader.ColumnNames = new string[] { "foo", "bar", "etc" };
....

The problem is in .NET v1.1 you can't wrap this reader to the
XmlValidatingReader. But you could write out the XML, then read it using
XmlTextReader, and then the XmlValidatingReader can work over that.

"Glenn Owens" <go****@nixonpeabody.com> wrote in message
news:bc**************************@posting.google.c om...

"Chris Lovett" <chris@!nospam!.net> wrote in message news:<10*************@corp.supernews.com>...

In there user samples on www.gotdotnet.com you will find an XmlCsvReader
which may be of some help to you for step (1).

Converting the DataSet back to XML is easy, then from XML back to CSV can be done with an XSL transform, which I think is included in teh XmlCsvReader download.

So yes, this approach makes sense. A lot of folks are standardizing on XML for this kind of processing. Hopefully eventually, you'll be able to
convince your business partner to just send you XML at some point in the
future, and then you're processing will get a bit more streamlined.

Chris, thanks for the response. I am currently looking at the
XmlCsvReader - it definitely looks like something that I could use.
Thanks for your hard work and pointing me in this direction.

Can it be modified? (it does appear to be copywritten)...

Is there a way to use an .XSD file to describe/interpret the column
tags instead of using the first line of the file to define the column
names (tags)? This would allow me to fully describe any of the input
files, using separate schemas, without needing to modify them before
the initial XML conversion. The added bonus would be to use the .XSD
files to validate the structure, datatype, cardinality, etc. - could
the XmlCsvReader descend from XmlValidatingReader?

Also, I came across a really good MSDN link article on customizing the
XmlReader:

http://msdn.microsoft.com/webservice...ustxmlread.asp
You are correct about the furture of our B2B. This is just the first
step before we "potentially" begin requesting each of our data vendors
to supply well-formed XML input streams (preferably via a web
service). But, we needed to get this part done reasonably quickly.

Thanks again,
Glenn

[Chris Lovett]

The eula is very open. Feel free to use it in your product and make
whatever changes necessary.

You could read the XSD and then pass in the column names:

XmlCsvReader reader = new XmlCsvReader(uri, nameTable);
reader.ColumnNames = new string[] { "foo", "bar", "etc" };
....

The problem is in .NET v1.1 you can't wrap this reader to the
XmlValidatingReader. But you could write out the XML, then read it using
XmlTextReader, and then the XmlValidatingReader can work over that.

"Glenn Owens" <go****@nixonpeabody.com> wrote in message
news:bc**************************@posting.google.c om...

"Chris Lovett" <chris@!nospam!.net> wrote in message news:<10*************@corp.supernews.com>...

In there user samples on www.gotdotnet.com you will find an XmlCsvReader
which may be of some help to you for step (1).

Converting the DataSet back to XML is easy, then from XML back to CSV can be done with an XSL transform, which I think is included in teh XmlCsvReader download.

So yes, this approach makes sense. A lot of folks are standardizing on XML for this kind of processing. Hopefully eventually, you'll be able to
convince your business partner to just send you XML at some point in the
future, and then you're processing will get a bit more streamlined.

Chris, thanks for the response. I am currently looking at the
XmlCsvReader - it definitely looks like something that I could use.
Thanks for your hard work and pointing me in this direction.

Can it be modified? (it does appear to be copywritten)...

Is there a way to use an .XSD file to describe/interpret the column
tags instead of using the first line of the file to define the column
names (tags)? This would allow me to fully describe any of the input
files, using separate schemas, without needing to modify them before
the initial XML conversion. The added bonus would be to use the .XSD
files to validate the structure, datatype, cardinality, etc. - could
the XmlCsvReader descend from XmlValidatingReader?

Also, I came across a really good MSDN link article on customizing the
XmlReader:

http://msdn.microsoft.com/webservice...ustxmlread.asp
You are correct about the furture of our B2B. This is just the first
step before we "potentially" begin requesting each of our data vendors
to supply well-formed XML input streams (preferably via a web
service). But, we needed to get this part done reasonably quickly.

Thanks again,
Glenn