Read Contents from text file and write to excel with given format

Question

1.00/5 (1 vote)

See more:

Hi,

I have a task to read the contents from text file and write to excel with same column- values as given below;

Input file: (.txt file)
----------

Fruit:Apple
Vegetable:Carrot
Vehicle:Car
Gender:Male
Age: 25
------------------
Fruit:Mango
Vegetable:Tomato
Vehicle:Bus
Gender:FeMale
Dept: IT
-----------------

Output: .xls
-------------------------------------------------
Fruit Vegetable Vehicle Gender Age Dept >>>>>>>>Excel Column Names
-------------------------------------------------
Apple Carrot Car Male 25 -
Mango Tomato Bus Female - IT

Note:
Each record ends with hypen line (---) in the text file.
Some column will not appear in the text file and if any new column in text file then need to append in excel file,

Please give a solution.

Thanks,
SK

Posted 5-Aug-13 9:38am

subbu_tech

Add a Solution

Comments

Maciej Los 5-Aug-13 16:26pm

What have you tried? Where are you stuck?

6 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2013-08-05T10:36:00

Solution 5

Using Interop[^], you need to do only 3 things:
1) import text file, using OpenText[^] method,
2) transpose rows to columns, copying data into clipboard and pasting it with transpose option[^],
3) remove duplicated column-headers

Please, try. It's really easy ;)

Posted 5-Aug-13 10:36am

Maciej Los

Comments

Sergey Alexandrovich Kryukov 5-Aug-13 17:12pm

Right, this a valid solution, 5ed.
Only please see my comments to my Solution 4.
—SA

Maciej Los 5-Aug-13 17:14pm

Thank you, Sergey ;)

Sergey Alexandrovich Kryukov · Answer 2 · 2013-08-05T10:19:00

Solution 4

For writing Excel file, you can use Open XML SDK. The format itself is available and is standardized under ECMA-376 and ISO/IEC 29500:2008:
http://en.wikipedia.org/wiki/Office_Open_XML[^],
http://en.wikipedia.org/wiki/Office_Open_XML_software[^].

Please see the comparison chart on Office Open XML software:
http://en.wikipedia.org/wiki/Comparison_of_Office_Open_XML_software[^].

This is another option: http://npoi.codeplex.com/[^].

—SA

Posted 5-Aug-13 10:19am

Sergey Alexandrovich Kryukov

Comments

z3ngew 5-Aug-13 16:31pm

This is can be very helpful, i didn't know about it, thanks
z3ngew

Maciej Los 5-Aug-13 16:40pm

Sergey, i think it is possible to achive using Interop. See my answer.
You did not provide any information about reading text file, that's why i voted 4!

Sergey Alexandrovich Kryukov 5-Aug-13 17:11pm

Okay, I got your point (though I can skip it as a simple part).
Interop is of course another way. For a record: it is not recommended in all cases, and Open XML SDK has its benefits, first of all, it does not depend on installation of Office. Please see this Microsoft article warning against using Office Interop in some cases like in server settings:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q257757#kb2.

From the other hand, Open XML does not address old office formats.

—SA

Maciej Los 5-Aug-13 17:23pm

Now, i do really understand why you prefer to use OpenXML technology...
Thank you for sharing link with many useful information ;)

Sergey Alexandrovich Kryukov 5-Aug-13 17:53pm

You are very welcome... :-)
—SA

OriginalGriff · Answer 3 · 2013-08-05T09:46:00

Solution 1

It doesn't quite work like that.
We do not do your work for you.
If you want someone to write your code, you have to pay - I suggest you go to VWorker.com and ask there.

But be aware: you get what you pay for. Pay peanuts, get monkeys

Posted 5-Aug-13 9:46am

OriginalGriff

Comments

adriancs 5-Aug-13 23:47pm

vworker.com is bought by freelancer.com.

ridoy · Answer 4 · 2013-08-05T09:51:00

No one here will give you a complete solution or source code for your problem. You need to try yourself and then share the problems you face here.
Look out this link..
http://social.msdn.microsoft.com/Forums/vstudio/en-US/f22a4a17-6b6e-48a3-9c08-6c28eaf7c673/reading-text-file-and-export-to-excel[^]
The solutions provided there read text from text files and then write to excel file,so this will help you to do your task.

z3ngew · Answer 5 · 2013-08-05T10:18:00

If you want to read text file, simply do the following
1- create an empty form
2- add open file dialog
3- add rich text box

and follow the code in here: http://msdn.microsoft.com/en-us/library/db5x7c0d.aspx[^]

i think you can do this code with little effort, you don't have to pay for it anywhere

Good Luck,
z3ngew

adriancs · Answer 6 · 2013-08-05T17:52:00

Read Text File
For reading a Text file, you may refer here: (lots of suggestions)
What's the fastest way to read a text file line-by-line?[^]

For splitting the records:
You may refer this: http://www.dotnetperls.com/split[^]
For more advance, you may use Regular Expression: The 30 Minute Regex Tutorial[^]

Create Excel Document
Another simple option:
Just create a HTML table and name the file extension as ".xls".

Microsoft Excel (regardless or any version) will open and convert it as usual Excel Document.
All the styles and colouring (formatting) will be handled too.

Read Contents from text file and write to excel with given format

6 solutions

Solution 5

Solution 4

Solution 1

Solution 2

Solution 3

Solution 6

Add your solution here

Preview 0