Click here to Skip to main content
14,865,273 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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
Updated 11-Mar-16 11:27am
v4
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.

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);
                        }
                    }
   
v4
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.
   
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.
   
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.
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
  }
}
   

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