Converting text files (.txt) into excel files (.xls) in c#

Question

1.70/5 (3 votes)

See more:

Hi Sir,

Could anyone help me about converting text files (.txt) into excel files (.xls) in C#?.

Here's the sample format of text file:

tag ord_no ref_no acv_no prog_no prog_date acv_type_id
3G 6684516 1 9 9 Feb 3 2011 6:54PM 3GINSTLPC
3G 6727254 1 6 12 Feb 5 2011 11:13AM 3GNORINST
3G 6732192 1 6 6 Feb 1 2011 10:43AM 3GINSTLPC

it should be converted to excel files using the c# programs.
I really appreciate all the helps and sample code that you will be given to me. Thank you and God bless...

Posted 9-Feb-11 15:32pm

Silver Lightning

Updated 21-Dec-17 5:49am

Add a Solution

Comments

Albin Abel 9-Feb-11 22:39pm

Are the cells in a column has constant number of chars? In above example it follows a constant pattern. If that is the case what would be the difficulty to parse. May use a necessary unicode char range to avoid invalid chars. Only if we could predict what kind of invalid tokens can occur then only can write a parsing code. If the text files coming from another source...is it has an option to export as csv?

Silver Lightning 11-Feb-11 0:09am

Hi Sir,

I already got the code on how to convert text file to excel file. But the result was not in a proper format. Can you help me to revise this code on how could I get the header file and body of the data in clear view. Thank you and God bless...

private void Convert_TextFile_To_ExcelFile()
{
// Open the text file in Excel.
m_objExcel = new Microsoft.Office.Interop.Excel.Application();
m_objBooks = (Microsoft.Office.Interop.Excel.Workbooks)m_objExcel.Workbooks;
m_objBooks.OpenText(m_strSampleFolder + "sampledoc.txt",
Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, 1,
Microsoft.Office.Interop.Excel.XlTextParsingType.xlDelimited,
Microsoft.Office.Interop.Excel.XlTextQualifier.xlTextQualifierDoubleQuote,
false, true, false, false, false, false, m_objOpt, m_objOpt,
m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt);

m_objBook = m_objExcel.ActiveWorkbook;

// Save the text file in the typical workbook format and quit Excel.
m_objBook.SaveAs(m_strSampleFolder + "sampledoc.xls",
Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal,
m_objOpt, m_objOpt, m_objOpt, m_objOpt,
Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt);
m_objBook.Close(false, m_objOpt, m_objOpt);
m_objExcel.Quit();

}

Member 12561568 7-Jun-16 8:13am

Not Working, And what is m_objOpt

Silver Lightning 11-Feb-11 0:40am

Could anyone help me to revise or update this code?. Your help greatly appreciated. Thank you...

4 solutions

Solution 1

This Link1-[Convert txt file to excel file][^] and Link2-[transfer data to an Excel][^] might help you

Posted 9-Feb-11 15:44pm

RaviRanjanKr

Updated 9-Feb-11 15:59pm

v2

Comments

Silver Lightning 9-Feb-11 21:52pm

thank you sir, but how could I reformat (needed) all the fields from text file to excel file?

RaviRanjanKr 9-Feb-11 22:01pm

Hey Jesureno have you try this link check it in which mention how Code delimited text file that excel can parse into rows and column.

Silver Lightning 9-Feb-11 22:29pm

Hi sir, the sample code in link have a lot of invalid tokens. can you give me a sample code that I easily to understand? sorry for disturbing you sir. but I need it urgently. Your help really appreciated.

Solution 3

You can do File | Open the txt file and you should see a prompt to import the file. It should ask you how the file is delimited (such as ',', or '|' or \t etc.) and you can import into an excel sheet.

Posted 9-Feb-11 17:19pm

Ramalinga Koushik

Comments

JF2015 10-Feb-11 2:43am

He meant to do that in code, so your solution won't help.

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)

Espen Harlinn · Accepted Answer · 2011-02-10T11:37:00

Solution 4

EPPlus on CodePlex[^] allows you to create excel files using c#, and you don't need to have excel installed.

read in all the lines
process one line at a time
use the string Split method to break each line up into an array of elements for the column data

add data to the spreadsheet using EPPlus

Regards
Espen Harlinn

Posted 10-Feb-11 11:37am

Espen Harlinn

Comments

Sergey Alexandrovich Kryukov 14-Feb-11 11:15am

Very good, my 5.
--SA

Espen Harlinn 14-Feb-11 14:22pm

Thanks SAKryukov!

Sandeep Mewara 14-Feb-11 13:40pm

Nice one. Didn't knew - good to know! 5! :)

Espen Harlinn 14-Feb-11 14:22pm

Thanks Sandeep!

thatraja 29-May-11 13:07pm

Fine library Espen. Have my 5!

Espen Harlinn 29-May-11 14:30pm

Thank you, thatraja :)

Nish Nishant · Accepted Answer · 2011-02-09T16:29:00

Solution 2

It may be a far simpler approach to write out a csv file (comma separated values). Excel can directly open csv files, and then you can save the file as an excel sheet.

Posted 9-Feb-11 16:29pm

Nish Nishant

Comments

Silver Lightning 9-Feb-11 22:40pm

thanks sir, but I need to automate the conversion of text files to excel file. Can you give me a simple code on how could I do it? Thank you and God bless...

Nish Nishant 10-Feb-11 8:44am

Ok, in that case you need to either look at Excel automation (there is documentation on MSDN) or look at using a 3rd party library that can generate Excel files.

Espen Harlinn 10-Feb-11 17:32pm

Good advice, a 5