By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,635 Members | 2,188 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,635 IT Pros & Developers. It's quick & easy.

Export XML data from text file to Excel file

P: 3
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 “<MerchantReference>” 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 MerchantReference 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_PaymentService.PaymentGateway.Auth, Message:
<Action>AUTH START</Action>
<CurrentTime>06/03/2008 05:43:49.849 AM</CurrentTime>
_ _ _ _ _ _

AccountNumber MerchantReference Action CurrentTime
4290058061755517 101151 AUTH START 6/3/2008 5:43:50 AM

Please provide any possible solution which you come across. Thanks in advance.
Jun 11 '08 #1
Share this Question
Share on Google+
2 Replies

Expert 100+
P: 2,364
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:

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

Jun 27 '08 #2

Expert 100+
P: 2,057
Problem 2:

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

Post your reply

Sign in to post your reply or Sign up for a free account.