Export XML data from text file to Excel file

[pulavarthipraveen]

Overview: We have a requirement in the c#.NET 1.0 windows application. There will be some input text file[with extension .log] in the user’s machine[in C:\ or D:\ drive]. The user should browse and select the input text file and also select the destination path. click some “Convert” button, then the XML data which is present in the text file should be exported to a new excel sheet and this excel should be stored in the destination path which the user already selected.

Problem 1: The input text file contains normal text along with the repeated set of XML nodes for different data. We have to pick the specific XML nodes and their data and import into the excel sheet. As being the text file, how we can separate XML content from it. What can be the approach we can adopt?

Problem 2: a) Even we copy paste the “<MerchantRefer ence>” value e.g 0000101151, to the excel sheet cell it’s being taken as 101151
b) Even we copy paste the “<CurrentTime> ” value e.g. 06/03/2008 05:43:49.849 AM, to the excel sheet cell it’s being taken as 6/3/2008 5:43:50 AM

For Problem 2 we tried with “format cells” – Custom option of the excel sheet, but didn’t find the required one.

Please find below sample XML input which the (a)text file contains and the expected output[without formatting of MerchantReferen ce and CurrentTime] which the excel sheet sholud contain(b).

We want to shown the XML nodes as heading/column and their data as different rows.

a)_ _ _ _ _
5:43:49 AM: Source:CPM_Paym entService.Paym entGateway.Auth , Message:
<TimeNode>
<AccountNumber> 429005806175551 7</AccountNumber>
<MerchantRefere nce>0000101151</MerchantReferen ce>
<Action>AUTH START</Action>
<CurrentTime> 06/03/2008 05:43:49.849 AM</CurrentTime>
</TimeNode>
_ _ _ _ _ _

b)
AccountNumber MerchantReferen ce Action CurrentTime
429005806175551 7 101151 AUTH START 6/3/2008 5:43:50 AM


Please provide any possible solution which you come across. Thanks in advance.

[Dököll]

Hey there!

See if this can give you an idea, it's in VB6 but get a a look, I think it helps address some of the issues:

http://bytes.com/forum/thread777267.html

Also, please look at related links, hope you can use some of the ideas there...

Later!

[jkmyoung]

Problem 2:

It is format cells. Use the following 'custom' formats:
1. 0000000000
2. dd/mm/yyyy hh:mm:ss.000 AM/PM