Import from csv in c#

Question

0.00/5 (No votes)

See more:

, +

I wanna import csv file in c# windows application.

What I have tried:

C#

StreamReader sr = new StreamReader(csvfolderPath);
                    string line = sr.ReadLine();
                    string[] value = line.Split(',');
                    DataTable dt = new DataTable();
                    DataRow row;
                    foreach (string dc in value)
                    {

                        dt.Columns.Add(new DataColumn(dc));
                    }

                    while (!sr.EndOfStream)
                    {
                        value = sr.ReadLine().Split(',');
                        if (value.Length == dt.Columns.Count)
                        {
                            row = dt.NewRow();
                            row.ItemArray = value;
                            dt.Rows.Add(row);
                        }
                    }

I am able to read the csv file from my above code but main problem is column have sentences with , separated and StreamReader is reading all column with comma separated so,

If I have column

"Column1" "column2" "column3" "column4" "My, Name"

Note: last column "My, Name" is single column

In above, I have 5 column

after result i am getting 5 columns like "Column1","column2","column3","column4","My","Name"

and if I split column with ,

then i am getting 5 column ("Column1","column2","column3","column4","My","Name")

so, can you explain me how to split column with two special character ",

Posted 9-Mar-16 21:06pm

itsathere

Updated 11-Mar-16 11:27am

PIEBALDconsult

v4

Add a Solution

Comments

PIEBALDconsult 10-Mar-16 11:07am

0) Don't use Split; Split is hardly ever the correct tool; it is too simplistic. You need something more robust, such as http://www.codeproject.com/Articles/69080/Rive
1) sr.ReadLine().Split -- won't that throw a null reference exception when you reach the end of the file?

Per Söderlund 11-Mar-16 17:32pm

0) I disagree, split is fine for basic stuff.
When it isn´t fine I use regular expressions.
See namespace "System.Text.RegularExpressions".
1) while(!sr.EndOfStream) is preventing a null reference exception?

PIEBALDconsult 11-Mar-16 19:39pm

I use Regular Expressions a lot as well, but it doesn't seem like the right tool for splitting CSV.

Per Söderlund 12-Mar-16 5:23am

Why not?

gggustafson 8-Apr-18 12:53pm

Because it does not observe the nuances of either the definitions of CSV found in RFC 4180 or in the defacto Microsoft standard.

4 solutions

Solution 5

If you don´t want the quotation marks in values you can do this?
I copied your code and made some changes.
This will split on "," instead of ,

C#

StreamReader sr = new StreamReader(csvfolderPath);
                    string line = sr.ReadLine();
                    string[] value = 
line.Split(new string[] { \",\""},StringSplitOptions.None);
                    DataTable dt = new DataTable();
                    DataRow row;
                    foreach (string dc in value)
                    {
 
                        dt.Columns.Add(new DataColumn(dc.Trim('"'));
                    }
 
                    while (!sr.EndOfStream)
                    {
                        value = sr.ReadLine().Split(',');
                        if (value.Length == dt.Columns.Count)
                        {
                            row = dt.NewRow();
                            row.ItemArray = value;
                            dt.Rows.Add(row);
                        }
                    }

Posted 11-Mar-16 11:27am

Per Söderlund

Updated 11-Mar-16 11:37am

v4

Solution 2

Because you are splitting fields at each comma, but ignoring fields in quotes. See Using OleDb to Import Text Files (tab, CSV, custom)[^] for a simpler method.

Posted 9-Mar-16 22:51pm

Richard MacCutchan

Comments

itsathere 10-Mar-16 5:15am

Your suggested url is good but i am not getting the column name and row values matches with csv file.What to do for?

Richard MacCutchan 10-Mar-16 5:18am

Sorry, but your comment is not clear.

itsathere 10-Mar-16 5:35am

using (OleDbCommand cmd = new OleDbCommand(string.Format
("SELECT * FROM [{0}]", file.Name), con))
{
con.Open();

// Using a DataReader to process the data
using (OleDbDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
// Process the current reader entry...
// I don't understand
}
}

// Using a DataTable to process the data
using (OleDbDataAdapter adp = new OleDbDataAdapter(cmd))
{
DataTable tbl = new DataTable("MyTable");
adp.Fill(tbl);

foreach (DataRow row in tbl.Rows)
{
// Process the current row...
// I don't understand
}
}
}

In current situation csv file is reading but Column Name is not matching with exact .csv file column and column value is also not same. MyTable datatable value is in different format or something goes wrong.

Richard MacCutchan 10-Mar-16 5:41am

Unless you show the actual detail we cannot begin to guess what is going wrong.

itsathere 10-Mar-16 5:52am

In csv file, I am watching below format

A B C D E
1 COLUMN1 COLUMN2 COLUMN3 COLUMN4
2 col val col val col val col val
3 col val col val col val col val
4 col val col val col val col val

after export, Table data is like below format

csvfileName#csv.E1 csvfileName#csv.E2 csvfileName#csv.E3 csvfileName#csv.E4

1 - s @
1 4 c f

Richard MacCutchan 10-Mar-16 7:27am

Sorry, but what does that have to do with your original question? You seem to have more than one file, and that data does not look like CSV.

Solution 1

Start here :
Reading and Writing CSV Files in C#[^]

Simple and fast CSV library in C#[^]

Posted 9-Mar-16 21:09pm

Mehdi Gholam

Comments

itsathere 10-Mar-16 4:18am

I am able to read the csv file from my above code but main problem is column have sentences with , separated and StreamReader is reading all column with comma separated so,

If I have column

"Column1" "column2" "column3" "column4" "My, Name"

In above i have 5 column

after result i am getting "Column1","column2","column3","column4","My","Name"

and if I split column with ,

then i am getting 5 column ("Column1","column2","column3","column4","My","Name")

so, can you explain me how to split column with ",

Mehdi Gholam 10-Mar-16 4:52am

The above links support that.

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

itsathere · Accepted Answer · 2016-03-10T18:35:00

C#

StreamReader sr = new StreamReader(itm);
string line = sr.ReadLine();
var value = line.Split('\"').Where((s, n) => n % 2 == 1);
var strColumn = value;
while (!sr.EndOfStream)
{
  value = sr.ReadLine().Split('\"').Where((s, n) => n % 2 == 1);
  string[] Colresult = strColumn.Select(x => x.ToString()).ToArray();//Your column
  string[] Rowresult = value.Select(x => x.ToString()).ToArray();//Your Row Value
  for (int j = 0; j < Colresult.Count(); j++)
  {
     //Use column
  }
}

Import from csv in c#

4 solutions

Solution 5

Solution 2

Solution 1

Solution 4

Add your solution here

Preview 0

Import from csv in c#

4 solutions

Solution 5

Solution 2

Solution 1

Solution 4

Add your solution here

Preview 0

Existing Members

...or Join us