Csv importing to datagridview with commas and double quotes

Matti Tiira

Hi!

I have made a windows forms application which I can open and modify csv files with.
I have an other program where the csv file is made with.
The problem is that the csv file has commas and double quotes as delimiters.
How can I trim the quotes so they dont appear in the datagridview?

Now it looks like this in my program.

And my code for the import looks like this:

private void btnImport_Click(object sender, EventArgs e)
{
openFileDialog1.ShowDialog();
txtFilePath.Text = openFileDialog1.FileName;
BindDataCSV(txtFilePath.Text);
}
private void BindDataCSV(string filePath)
{
DataTable dt = new DataTable();
string[] lines = System.IO.File.ReadAllLines(filePath);
if (lines.Length > 0)
{
string firstLine = lines[0];
string[] headerLabels = firstLine.Split(',');
foreach (string headerWord in headerLabels)
{
dt.Columns.Add(new DataColumn(headerWord));
}
for (int r = 1; r < lines.Length; r++)
{
string[] dataWords = lines[r].Split(',');
DataRow dr = dt.NewRow();
int columnIndex = 0;
foreach (string headerWord in headerLabels)
{
dr[headerWord] = dataWords[columnIndex++];
}
dt.Rows.Add(dr);
}
}
if (dt.Rows.Count > 0)
{
dataGridView1.DataSource = dt;
}
}

I would really appreciate all help, Thanks

-Matti

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

Jignesh Kumar7y ago
Accepted answer
Please find below link,
https://forums.asp.net/t/1713910.aspx?How+to+remove+single+and+double+quotes+from+a+string+

Please change below place,
foreach (string headerWord in headerLabels)
{
dr[headerWord] = dataWords[columnIndex++].Replace("\"","").Replace("'","");
;
}
+3
Matti Tiira7y ago
Please find below link,
https://forums.asp.net/t/1713910.aspx?How+to+remove+single+and+double+quotes+from+a+string+

Please change below place,
foreach (string headerWord in headerLabels)
{
dr[headerWord] = dataWords[columnIndex++].Replace("\"","").Replace("'","");
;
}
This worked. Thanks a lot!

-Matti
+1
Jignesh Kumar7y ago
Please find below link,
https://forums.asp.net/t/1713910.aspx?How+to+remove+single+and+double+quotes+from+a+string+

Please change below place,
foreach (string headerWord in headerLabels)
{
dr[headerWord] = dataWords[columnIndex++].Replace("\"","").Replace("'","");
;
}
+2
Leon D7y ago
You can use Spire.Xls nuget package to easily export data from csv to datagridview.
using Spire.Xls;
using Spire.Xls.Converter;

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

        private void button1_Click(object sender, EventArgs e)
        {
            OpenFileDialog dialog = new OpenFileDialog();
            dialog.Filter = "CSV document(*.csv)|*.csv";
            DialogResult result = dialog.ShowDialog();
            if (result == DialogResult.OK)
            {
                string csvFile = dialog.FileName;
                Workbook workbook = new Workbook();
                workbook.LoadFromFile(@"..\csvtodatatable.csv", ",");
                Worksheet worksheet = workbook.Worksheets[0];
                System.Data.DataTable t = worksheet.ExportDataTable();
                this.dataGridView1.DataSource = t;
            }
        }
    }
}
You can check this link for more information:
https://www.e-iceblue.com/Tutorials/Spire.XLS/Spire.XLS-Program-Guide/CSV-to-Datatable-Convert-CSV-to-Datatable-with-C-VB.NET.html
+1
Matti Tiira7y ago
Tried that, didn't work.
Does exactly the same as before.
Still shows the quotes on the datagridview.
+1
brijesh rathor7y ago
please use this below code

if (dt.Rows.Count > 0)
{
DataTable clone = dt.Clone();
string t;
var qry = from DataRow row in dt.Rows
let arr = row.ItemArray
select Array.ConvertAll(arr, s =>
(t = s as string) != null
&& t.StartsWith("\'")
&& t.EndsWith("\'") ? t.Trim('\'') : s);
foreach (object[] arr in qry) {
clone.Rows.Add(arr);
}
dataGridView1.DataSource = clone;
}

it will remove double quote in all table rows.
+1