Click here to Skip to main content
15,882,315 members
Please Sign up or sign in to vote.
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
Comments
Maciej Los 5-Aug-13 16:26pm    
What have you tried? Where are you stuck?

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 ;)
 
Share this answer
 
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 ;)
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
 
Share this answer
 
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
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
 
Share this answer
 
Comments
adriancs 5-Aug-13 23:47pm    
vworker.com is bought by freelancer.com.
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.
 
Share this answer
 
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
 
Share this answer
 
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.
 
Share this answer
 
v4

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900