Click here to Skip to main content
15,885,914 members
Please Sign up or sign in to vote.
1.91/5 (3 votes)
Hello Friends...

I have developed following code to generate .csv file in C#.

C#
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Microsoft.Office.Interop.Excel;
using System.IO;

namespace ProcessExcelFile
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            string _filename = @"E:\Import File\Working\20_04_2015_21_00_57_B14.csv";
            StreamReader ObjStreamReader = new StreamReader(File.OpenRead(_filename));
            string[] line = ObjStreamReader.ReadLine().Split(';');
            string[] machinename = _filename.Split('_');
            for (int i = 2; i < line.Length; i++)
            {
                line[i] = string.Concat(line[i].Trim('"'), "_" ,machinename[machinename.Length - 1].Remove(machinename[machinename.Length - 1].IndexOf(".csv")));
            }
            
            List<System.Collections.Generic.List<string>> ObjListColumns = new List<List<string>>();
            //here 
            for (int i = 1; i < line.Length; i++)
            {
                ObjListColumns.Add(new List<string>());
            }

            string[] values = null;
            while (!ObjStreamReader.EndOfStream)
            {
                values = null;
                values = ObjStreamReader.ReadLine().Split(';');
                for (int i = 0; i < ObjListColumns.Count; i++)
                {
                    ObjListColumns[i].Add(values[i + 1]);
                }
            }
            ObjStreamReader.Close();

            for (int i = 0; i < ObjListColumns[0].Count; i++)
            {
                ObjListColumns[0][i] = ObjListColumns[0][i].Trim('"').Trim('\\').Trim('"');
            }
            for (int i = 0; i < ObjListColumns.Count; i++)
            {
                for (int j = 0; j < ObjListColumns[i].Count; j++)
                {
                    ObjListColumns[i][j] = ObjListColumns[i][j].Trim('"');
                }
            }
            FileStream ObjFileStream = new FileStream(@"E:\Import File\Working\abc1.csv", FileMode.OpenOrCreate);
            StreamWriter ObjWriter = new StreamWriter(ObjFileStream);

            string text=null;
            ObjWriter.WriteLine("[Data]");
            ObjWriter.WriteLine("Tagname;TimeStamp;Value;DataQuality");

            for (int i = 2; i < line.Length; i++)
            {
                
                for (int j = 0; j < ObjListColumns[i-1].Count; j++)
                {

                    text=null;
                    text=line[i].ToString()+";"+ObjListColumns[0][j].ToString()+";"+ObjListColumns[i - 1][j].ToString()+";"+"Good";
                    ObjWriter.WriteLine(text);

                }
            }
            ObjWriter.Close();

            DateTime endtime = DateTime.Now;
            MessageBox.Show("Compeleted in ");

        }
    }
}


An Excel file is being generated but Data in Excel file is same as we put in. I want to use Text To Columns Option which is there in Microsoft Excel using C#. Please provide me a code to convert text to columns.
Posted
Comments
Sinisa Hajnal 28-Apr-15 5:25am    
First of all, it is not nice to ask to provide a code. This is not free code service.
Second, you didn't really describe your problem not what you're trying to accomplish. If you open CSV file in excel, you'll get columns based on the separator. If you want to format the data, do it before writing into the file. If you need to really create excel file and not CSV, use Excel interop.
Sushil Mate 4-May-15 2:41am    
what exactly you trying to achieve here, in Windows CSV file gets opened in excel by default. you are not creating excel file here.
Subramanyam Shankar 7-May-15 13:06pm    
Do you mean you want to set the format of columns to text?

Depending on what nationale your machine is running under the issue could be your CSV formatting.

First of all text to columns is an Excel guide and has nothing to do with C#, and launching that guide from C# code doesn't make much sense, is it will immediately require human interaction and as such stops being a batch process.
(ref: http://www.excel-easy.com/examples/text-to-columns.html[^])

Now perhaps the problem is that the nationionale list seperator between sender and reciever is not the same, for instance you're using semi-colon and a csv file uses colon, unless in one of several countries, but a uniform way to address this is to ensure that you're using the same version and if running locally make sure to use System.Globalization.CultureInfo.GetCultureInfo(yourculturename i.e. "da-DK").TextInfo.ListSeparator instad of hardcoding the seperator.

I suspect that's where your problem recides, i've taken some more time to explain csv format there: C# code write only first column data in excel[^]
 
Share this answer
 
Though I can't really get what you are asking, but it seems you want to either read or write a csv file.

http://www.codeproject.com/Articles/415732/Reading-and-Writing-CSV-Files-in-Csharp
 
Share this answer
 
Hey its very simple, you can use excel dll which provide you the feature to convert your excel or csv into dataset directly. Follow the below code:


C#
string ext = Path.GetExtension(FileExport.FileName).ToLower();// get the extension of file name i.e. csv or xls

                                Stream fileStream = new MemoryStream(FileExport.FileBytes); // converting the excel file into binary

                                //1. Reading Excel file
                                IExcelDataReader excelReader;
 
                                if (ext.Trim() == ".xls")
                                {
                                    excelReader = ExcelReaderFactory.CreateBinaryReader(fileStream);
                                }
                                else //Added support for Excel 2007 files.
                                {
                                    excelReader = ExcelReaderFactory.CreateOpenXmlReader(fileStream);
                                }
 
                                //2. DataSet - Create column names from first row. Use this if you want to make your first row as a data column.
                                excelReader.IsFirstRowAsColumnNames = true;
 
                                //3. The result of each spreadsheet will be created in the resultDS.Tables
                                DataSet excelDataSet = excelReader.AsDataSet(); // here it store the excel data in a dataset.

                                //4. DataTable - Get the DataTable from DataSet
                                ExcelImportData = excelDataSet.Tables[0];
 
                                //5. Free resources (IExcelDataReader is IDisposable)
                                excelReader.Close();



Once the data came in dataset you can use it as the way you want. You can use a loop to extract the data row by row or column by column or you can directly save to another excel file.
you can download the excel dll fro here http://www.dllme.com/dll/files/microsoft_office_tools_excel_dll.html[^]
In case of any issue please let me know.
 
Share this answer
 

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