Read CSV files in C#

Question

I have the following code that imports excel documents and parses them so I can manipulate the data before it is saved to the database.

I can parse .xlsx and .xls files just fine but cannot figure out how to use my existing code for .csv files

the customer I am working for wants to use .csv file type to accept special characters.

OpenFileDialog opener = new OpenFileDialog();
opener.Filter = "Excel Files| *.xlsx;*.xls;*.csv;";
if (opener.ShowDialog() == DialogResult.Cancel)
    return;

FileStream streamer = new FileStream(opener.FileName, FileMode.Open);
IExcelDataReader reader;
if (Path.GetExtension(opener.FileName) == ".xls")
{
    reader = ExcelReaderFactory.CreateBinaryReader(streamer);
}
else if (Path.GetExtension(opener.FileName) == ".csv")
{

    *** Need Something Here to read CSV Files that will work with 
        the rest of code***
}
else
{
    reader = ExcelReaderFactory.CreateOpenXmlReader(streamer);
}
DataSet results = reader.AsDataSet();
results.Tables[0].Rows[0].Delete();
results.AcceptChanges();


foreach (System.Data.DataTable table in results.Tables)
{
    foreach (DataRow dr in table.Rows)
    {
       >>> Do Something With the Data
    }
}

Reading a `csv` file if fairly easy, open the file and read it line by line. What do you mean by special characters? — JohnG, Jan 17 '17 at 08:03
JohnG -- It is with the right library, but not natively. If you mean `.Split(',')` that's far from bulletproof. If you mean the Visual Basic parser, then it's not *really* reading line-by-line (there can be returns within a quoted CSV field, etc). — Hambone, Jan 19 '17 at 03:34
CSVHelper for sure ... https://joshclose.github.io/CsvHelper/ ... don't waste your time with anything else. — Skin, Jun 22 '23 at 11:13

score 0 · Answer 1 · edited May 24 '19 at 13:55

private void ReadCSVFile(string filepath)
{
    //receiverList = new List<ReceiverUser>();

    try
    {
        if (filepath == string.Empty)
            return;

        using (StreamReader sr = new StreamReader(FileUpload1.PostedFile.InputStream))
        {
            string line;

            while ((line = sr.ReadLine()) != null)
            {
                SplitLine(line);
            }
        }

        #region row add test
        DataTable dt = new DataTable();

        if (dt.Columns.Count == 0)
        {
            dt.Columns.Add("Name", typeof(string));
            dt.Columns.Add("Mail", typeof(string));
            dt.Columns.Add("Amount", typeof(double));
        }

        DataRow NewRow;
/*
        foreach (var item in receiverList)
        {
            NewRow = dt.NewRow();
            NewRow[0] = item.Name + " " + item.Surname;
            NewRow[1] = item.Mail;
            NewRow[2] = item.Amount;
            dt.Rows.Add(NewRow);
        }
*/


        grdRec.DataSource = dt;
        grdRec.DataBind();

        #endregion
    }
    catch (Exception)
    {

    }

}//end of function

This function reads a CSV file, load parameters to Datatable and set datasource of grid as Datatable. This is an ASP.NET WebfoRM CODE.

is there somewhere you are adding the lines to the List? I am fairly New to C#. — Big Al Ruby Newbie, Jan 17 '17 at 06:18
Ok i thought that would only loop through what is already in the recieverList. — Big Al Ruby Newbie, Jan 17 '17 at 06:21

score 0 · Answer 2 · answered Jan 19 '17 at 03:30

Assuming your IExcelDataReader is a third party package that reads Excel files (a pretty safe bet) and it does not natively handle CSV (that part I'm not sure about), then you can always just handle the CSV completely separately.

OLE has a nice CSV reader, so something like this should work.

List<DataTable> tables = new List<DataTable>();

if (Path.GetExtension(opener.FileName) == ".csv")
{
    OleDbConnection conn = new OleDbConnection(string.Format(
        @"Provider=Microsoft.Jet.OleDb.4.0; Data Source={0};" +
        "Extended Properties=\"Text;HDR=YES;FMT=Delimited\"",
        opener.FileName
    ));
    conn.Open();

    string sql = string.Format("select * from [{0}]", Path.GetFileName(opener.FileName));
    OleDbCommand cmd = new OleDbCommand(sql, conn);
    OleDbDataReader reader = cmd.ExecuteReader();

    DataTable dt = new DataTable();
    dt.Load(reader);
    tables.Add(dt);

    reader.Close();
}
else
{
    FileStream streamer = new FileStream(opener.FileName, FileMode.Open);
    IExcelDataReader reader = null;
    if (Path.GetExtension(opener.FileName) == ".xls")
        reader = ExcelReaderFactory.CreateBinaryReader(streamer);
    else
        reader = ExcelReaderFactory.CreateOpenXmlReader(streamer);
    DataSet results = reader.AsDataSet();
    results.Tables[0].Rows[0].Delete();
    results.AcceptChanges();

    foreach (DataTable table in results.Tables)
        tables.Add(table);
}

And then just reference your local list of datatables (tables) instead of Results.Tables, as that is now locally scoped to IExcelReader.

foreach (System.Data.DataTable table in tables)
{
    foreach (DataRow dr in table.Rows)
    {
       >>> Do Something With the Data
    }
}

If you can't use OLE for some reason, the .NET class library actually has a CSV parser. It's pretty well hidden, in my opinion, but it's nice:

http://odedcoster.com/blog/2012/03/28/did-you-know-a-net-csv-parser-that-comes-with-visual-studio/

[ExcelDataReader](https://github.com/ExcelDataReader/ExcelDataReader) does in fact handle CSV files. — crush, Sep 17 '18 at 18:35

score 0 · Answer 3 · answered Oct 01 '18 at 14:30

Can you check below code?. This will be useful to you.

 OpenFileDialog opener = new OpenFileDialog();
   opener.Filter = "Excel Files| *.xlsx;*.xls;*.csv;";
if (opener.ShowDialog() == DialogResult.Cancel)
    return;

FileStream streamer = new FileStream(opener.FileName, FileMode.Open);
IExcelDataReader reader;
if (Path.GetExtension(opener.FileName) == ".xls")
{
    reader = ExcelReaderFactory.CreateBinaryReader(streamer);
}
else if (Path.GetExtension(opener.FileName) == ".csv")
{

   var reader = ExcelReaderFactory.CreateReader(stream, new ExcelReaderConfiguration() {

// Gets or sets the encoding to use when the input XLS lacks a CodePage
// record, or when the input CSV lacks a BOM and does not parse as UTF8. 
// Default: cp1252. (XLS BIFF2-5 and CSV only)
FallbackEncoding = Encoding.GetEncoding(1252),

// Gets or sets the password used to open password protected workbooks.
Password = "password",

// Gets or sets an array of CSV separator candidates. The reader 
// autodetects which best fits the input data. Default: , ; TAB | # 
// (CSV only)
AutodetectSeparators = new char[] { ',', ';', '\t', '|', '#' };
});
    }
    else
    {
        reader = ExcelReaderFactory.CreateOpenXmlReader(streamer);
    }
    DataSet results = reader.AsDataSet();
    results.Tables[0].Rows[0].Delete();
    results.AcceptChanges();


    foreach (System.Data.DataTable table in results.Tables)
    {
        foreach (DataRow dr in table.Rows)
        {
           >>> Do Something With the Data
        }
    }

Refer this link ExcelDataReader

score -1 · Answer 4 · answered May 25 '22 at 12:27

-1

You can use CSVHelper to avoid writing CVS parser manually

answered May 25 '22 at 12:27

Oleksandr Tsvirkun

29
4

This does not provide an answer to the question. Once you have sufficient [reputation](https://stackoverflow.com/help/whats-reputation) you will be able to [comment on any post](https://stackoverflow.com/help/privileges/comment); instead, [provide answers that don't require clarification from the asker](https://meta.stackexchange.com/questions/214173/why-do-i-need-50-reputation-to-comment-what-can-i-do-instead). - [From Review](/review/late-answers/31861387) – MD. RAKIB HASAN May 30 '22 at 08:48

Read CSV files in C#

4 Answers4