How to access csv file and insert data into csv

Question

I am trying to insert data into csv file. I tried using XLWorkbook reference to access and insert data but I know that XLWorkbook can only support extension which are xlsx,xslm,xltx and xltm.

I am trying to find something similar to what I am trying to achieve through which I can insert data into specified column in csv file. I have used XLWorkbook for some other purpose but I am not aware to what I can use when I have to use csv.

//Accessing the csv file where I am trying to insert data.
string rootPath = Path.GetDirectoryName(Assembly.GetExecutingAssembly().CodeBase);
string filelocation = @"\csv\TestData.csv";
string location = rootPath + filelocation;

XLWorkbook workbook = new XLWorkbook(pathfile);
IXLWorksheet worksheet = workbook.Worksheet("Sheet1");
//Insert data after first row as first row contains column header
int lastrow = worksheet.LastRowUsed().RowNumber() + 1;

//through previous function I am trying get data from database and insert those data into csv cells
worksheet.Cell(String.Format("B{0}", lastrow)).Value = dummydata.FirstName;
worksheet.Cell(String.Format("C{0}", lastrow)).Value = dummydata.LastName;
worksheet.Cell(String.Format("D{0}", lastrow)).Value = dummydata.Address1;
worksheet.Cell(String.Format("E{0}", lastrow)).Value = dummydata.Address2;
worksheet.Cell(String.Format("F{0}", lastrow)).Value = dummydata.City;
worksheet.Cell(String.Format("G{0}", lastrow)).Value = dummydata.StateProvinceCode;
worksheet.Cell(String.Format("H{0}", lastrow)).Value = dummydata.ZipCode;
worksheet.Cell(String.Format("I{0}", lastrow)).Value = dummydata.Country;
worksheet.Cell(String.Format("J{0}", lastrow)).Value = dummydata.HomePhone;
worksheet.Cell(String.Format("L{0}", lastrow)).Value = dummydata.HomePhone;
worksheet.Cell(String.Format("M{0}", lastrow)).Value = dummydata.CellPhone;
worksheet.Cell(String.Format("T{0}", lastrow)).Value = dummydata.Email;
worksheet.Cell(String.Format("U{0}", lastrow)).Value = dummydata.Country;

//After inserting save the file
workbook.Save();

Have you tried using OpenXML? learn.microsoft.com/en-us/office/open-xml/open-xml-sdk . Also, I very much relate to your PN haha. — Matthew Kligerman
– Matthew Kligerman, Commented May 29, 2020 at 1:54
@MatthewKligerman I tried but didn't got an idea on how to implement or how to use it. I am familiar to XLWorkbook so was trying to accomplish it as soon as possible. — user1413
– user1413, Commented May 29, 2020 at 2:08
Seems reasonable - I did a little research and answered your question with XLWorkbook — Matthew Kligerman
– Matthew Kligerman, Commented May 29, 2020 at 2:16
Two ideas, first you can use linq to parse csv as an entity list, manipulate the list and then convert back your entity list into csv, second, you can use this library: filehelpers.net is very simple and powerful — Mauricio Atanache
– Mauricio Atanache, Commented May 29, 2020 at 2:36
joshclose.github.io/CsvHelper is the defacto c# csv handler if thats all you want — pm100
– pm100, Commented May 29, 2020 at 4:17

Matthew Kligerman · Accepted Answer · 2020-05-29 03:46:04Z

You can simply copy and use this code as is. It should resolve your issues.

Here's the class I developed to replace and/or add csv cells:

using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;

namespace CSVManager
{
    public class CSVWorker
    {
        private string m_FileName = string.Empty;

    public CSVWorker(string fileName)
    {
        m_FileName = fileName;
    }

    public void AddCells(int row, int column, string newValue)
    {
        var encoding = Encoding.GetEncoding("iso-8859-1");
        var csvLines = File.ReadAllLines(m_FileName, encoding);

        if (row < csvLines.Length)
        {         
            ReplaceCells(row, column, newValue);         
        }
        else
        {
            using (FileStream stream = new FileStream(m_FileName, FileMode.Create))
            {
                using (StreamWriter writer = new StreamWriter(stream, encoding))
                {
                    foreach (var line in csvLines)
                    {
                        writer.WriteLine(line);
                    }

                    int blankLines = row - csvLines.Length - 1;

                    for (int i =  0; i < blankLines; i++)
                    {
                        writer.WriteLine("");
                    }

                    string blankCols = string.Empty;

                    for (int i = 0; i < column-1; i++)
                    {
                        blankCols += ',';
                    }

                    writer.WriteLine(blankCols + newValue);                       
                }
            }
        }
    }

    public void ReplaceCells(int row, int column, string newValue)
    {
        var encoding = Encoding.GetEncoding("iso-8859-1");
        var csvLines = File.ReadAllLines(m_FileName, encoding);

        for (int i = 0; i < csvLines.Length; i++)
        {
            //var values = csvLines[i].Split(',');
            List <string> values = csvLines[i].Split(',').ToList();

            if (i == row)
            {
                if (column < values.Count)
                {
                    values[column] = newValue;
                }
                else 
                {
                    while (values.Count < column - 1)
                    {
                        values.Append(",");
                    }

                    values.Append(newValue);
                }

                using (FileStream stream = new FileStream(m_FileName, FileMode.Create))
                {
                    using (StreamWriter writer = new StreamWriter(stream, encoding))
                    {
                        for (int currentLine = 0; currentLine < csvLines.Length; ++currentLine)
                        {
                            if (currentLine == i)
                            {
                                writer.WriteLine(string.Join(",", values));
                            }
                            else
                            {
                                writer.WriteLine(csvLines[currentLine]);
                            }
                        }

                        writer.Close();
                    }

                    stream.Close();
                    break;
                }
            }
        }
    }
  } 
}

Here's how I used it:

namespace CSVManager
{
    class Program
    {
        static void Main(string[] args)
        {
            string fileName = @"C:\Users\mklig\Documents\TestCsv.csv";
            CSVWorker csvWorker = new CSVWorker(fileName);

            int row = 4;
            int col = 4;
            string newVal = "success";
            //csvWorker.ReplaceCells(row, col, newVal);
            csvWorker.AddCells(row, col, newVal);

         }
    }
}

Appreciate that. Sorry for delay in my comment. Had some personal issue.
No worries. Hope all is well. These are definitely tough times.

Collectives™ on Stack Overflow

How to access csv file and insert data into csv

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related