You must Sign In to post a response.
  • Category: .NET

    How to read data from text file and write to excel in C#?

    I have a text file with sample data as below:

    ACR Name1 Name2 Name3 Name4 Street HouseNo Street5 District PostalCode City Cty Rg PO Box POBoxPcd
    1001124248 MEX LIMITED LEVEL 2 LOTEMAU CENTRE VAEA STR APIA AS
    1001206457 LEADER COMPUTERS LTD PTY SKARRATT STREET NORTH 2128 SILVERWATER AU QLD
    1001206533 QUEST POTTS POINT SPRINGFIELD AVE 15 5006 MASCOT AU NSW
    1001211948 4D DOORS PTY LTD 95-97 WHITEHORSE ROAD 0000 BLACBURN AU VIC
    1001213081 CLAY & CO ENGINEERS PTY LTD 40 PRINCES CHARLES PDE 2231 KURNELL AU NSW
    1001225939 AZELIS AUSTRALIA PTY LTD C/O NORTHLINE 11 SHORT STREET 2164 SMITHFIELD AU NSW
    1001209854 STEMCELL TECH AUSTRALIA PTY. LTD C/O LOGICAL FREIGHT SOLUTIONS 4 INTERNATIONAL SQUARE 3043 TULLAMARINE AU VIC
    1001222543 AB KNITWEAR T/A MERINO GOLD 11 THOMAS ST 3056 BRUNSWICK AU VIC
    1001213071 MURRAY RIVER ORGANICS PTY LTD LEVEL 1/360 LITTLE COLLINS ST 3000 MELBOURNE AU VIC
    1001213075 G-STAR AUSTRALIA PTY LTD 3B VICTORIA STREET 2021 PADDINGTON AU NSW
    1001211233 SCHMOLZ-BICKENBACH AUSTRALIA PTY.LTD. 800-808 PRINCES HIGHWAY 3171 SPRINGVALE AU VIC
    1001225553 DUTY FREE STORE AUSTRALIA PYT. LTD KEITH SMITH AVE 2020 MASCOT AU
    1001225554 AMUSE BEAUTY STUDIO T2 SYDNEY DOMESTIC TERM RPORT 2020 MASCOT AU
    1001221462 HEINEMANN AUSTRALIA PTY LTD 1767 BOTANY ROAD 2019 BANKSMEADOW AU

    I want the C# code to read the text file and write each row into an excel file.
    So my output would be an excel.
    Can anybody help me in this??
  • #768323
    Hi,

    Initially for this kind of requirements. We need to start coding step by step. Here we have two steps.
    One is reading information from the text file or CSV files. Then we need to write the data into the Excel.

    Let see how initially we read the data from the text file.


    private void XLConv_Click(object sender, EventArgs e)
    {
    try
    {
    StreamReader objReader = new StreamReader(@"Yourfile.txt");// This is for path
    ArrayList arrText = new ArrayList();
    while (sLine != null)
    {
    sLine = objReader.ReadLine();
    if (sLine != null)
    arrText.Add(sLine);
    }
    callXL(arrText, false); // This is the Excel Function where your data get stored.
    }
    catch(Exception ex)
    {
    MessageBox.Show(ex.ToString());
    }
    }



    Now we need to write the code for filling the data in Excelsheet


    Using Microsoft.Office.Interop.Excel.Application xlexcel;
    Using Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
    Using Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;


    private void callXL(ArrayList arrText, bool value)
    {
    try
    {
    String textString = null;
    foreach (var item in arrText)
    {
    textString = textString + item + Environment.NewLine;
    }
    Clipboard.SetText(textString);
    object misValue = System.Reflection.Missing.Value;
    xlexcel = new Excel.Application();
    // for excel visibility
    //xlexcel.Visible = true;
    // Creating a new workbook
    xlWorkBook = xlexcel.Workbooks.Add(misValue);
    // Putting Sheet 1 as the sheet you want to put the data within
    xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
    // creating the range
    Excel.Range CR = (Excel.Range)xlWorkSheet.Cells[1, 1];
    CR.Select();
    xlWorkSheet.Paste(CR, false);
    if (value == true)
    {
    try
    {
    // saving the file as .xls
    xlWorkSheet.SaveAs(@"C:\Users\MyXLS.xls");
    }
    catch (Exception)
    {
    MessageBox.Show("File already exist");
    }
    }
    else
    {
    try
    {
    // saving the file as .xlsx
    xlWorkSheet.SaveAs(@"C:\Users\myXLSX.xlsx");
    }
    catch (Exception)
    {
    MessageBox.Show("File already exist");
    }
    }
    xlexcel.Quit();
    }
    catch(Exception ex)
    {
    MessageBox.Show(ex.ToString());
    }
    }


    Thanks,
    Mani

  • #768324
    Hi Manigandan,

    I have already tried this code from CodeProject and it is not working out. Please try to implement this and you will get many exceptions.

    Please give me some solution to read the Lines line by line and write it.

  • #768325
    Hi All,

    The C# code is for a console application. So we need to call the CreateExcel Method from the Void Main() Method.
    So please suggest accordingly.

  • #768327
    You can use given code snippet to read data from text file and write to excel in C# console
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;

    namespace AbundantcodeConsole
    {

    class Program
    {
    static void Main(string[] args)
    {
    string InputFileName = @"E:\InputYourFile.txt";
    string tmp = "";
    string OutPut="";
    if (System.IO.File.Exists(InputYourFileName))
    {
    System.IO.TextReader txtReader = System.IO.File.OpenText(InputFileName);
    while ((tmp = txtReader.ReadLine()) != null)
    {
    OutPut += tmp + "\n"; ;
    }
    txtReader.Close();
    txtReader.Dispose();
    }
    Console.WriteLine(OutPut);
    Console.ReadLine();

    }

    }

    }

    Useful reference: http://stackoverflow.com/questions/1037913/write-the-data-from-an-excel-sheet-to-the-text-file-though-c-sharp

  • #768328
    Reading a text file is simple, just you need to import system.io namespace and you will be able to do it, see below snippet

    using (StreamReader sr = File.OpenText(fileName))
    {
    string s = String.Empty;
    while ((s = sr.ReadLine()) != null)
    {
    //do minimal amount of work here
    }
    }

    now to write text in Excel we either need to use OLEDB object or Excel object, but OLEDB is far easy and light weight than OLEDB
    check out below code to write excel


    //In above code '[Sheet1$]' is the first sheet name with '$' as default selector,
    // with the help of data adaptor we can load records in dataset

    //write data in EXCEL sheet (Insert data)
    using (OleDbConnection conn = new OleDbConnection(connectionString))
    {
    try
    {
    conn.Open();
    OleDbCommand cmd = new OleDbCommand();
    cmd.Connection = conn;
    cmd.CommandText = @"Insert into [Sheet1$] (month,mango,apple,orange)
    VALUES ('DEC','40','60','80');";
    cmd.ExecuteNonQuery();
    }
    catch (Exception ex)
    {
    //exception here
    }
    finally
    {
    conn.Close();
    conn.Dispose();
    }
    }

    for more details see below link
    http://www.codeproject.com/Articles/1088970/Read-Write-Excel-file-with-OLEDB-in-Csharp-without

    Thanks
    Koolprasd2003
    Editor, DotNetSpider MVM
    Microsoft MVP 2014 [ASP.NET/IIS]

  • #768381
    A good option is to use third party libraries. Check below one which is free and supports to create, edit and convert excel.
    https://freenetexcel.codeplex.com


Sign In to post your comments