Import CSV to Gridview

Question

Please help!

I'm trying to import data from a csv into a grid before saving the data to the database... The problem is that no matter what I try, the lines are reading garbage or some format that I don't know. This is the first time I'm trying to import to my asp.net Gridview but it's not working. If anybody can please direct me in the right way I will appreciate it!

This is what I've done:

    private object ReadToEnd(string filePath)
{
    DataTable dtDataSource = new DataTable();

    string[] fileContent = File.ReadAllLines(filePath);

    if (fileContent.Count() > 0)
    {
        string[] columns = fileContent[0].Split(',');
        for (int i = 0; i < columns.Count(); i++)
        {
            dtDataSource.Columns.Add(columns[i]);
        }

        for (int i = 1; i < fileContent.Count(); i++)
        {
            string[] rowData = fileContent[i].Split(',');
            dtDataSource.Rows.Add(rowData);
        }
    }

    gvTest.DataSource = dtDataSource;
    gvTest.DataBind();

}

I only need one column from the entire csv which is SerialNo. After I receive the correct information I will bind the rest of the data before inserting into the database(sql)...

This is the what it's reading:

After reading the data it gives me an error message of : "Input array is longer than the number of columns in this table."

I've tried doing this another way:

       private static DataTable GetDataTabletFromCSVFile(string path)
{
    DataTable csvData = new DataTable();
    csvData.Columns.Add("SerialNo", typeof(string));
 //   csvData.Columns.Add("MachineDetailsRef", typeof(int));

    try
    {           
        using (TextFieldParser csvReader = new TextFieldParser( path))
        {
            csvReader.SetDelimiters(new string[] { "," });
            csvReader.HasFieldsEnclosedInQuotes = true;
            string[] colFields = csvReader.ReadFields();
            foreach (string column in colFields)
            {
                DataColumn serialno = new DataColumn(column);
                serialno.AllowDBNull = true;
                csvData.Columns.Add(serialno);
            }

            while (!csvReader.EndOfData)
            {
                string[] fieldData = csvReader.ReadFields();
                //Making empty value as null
                for (int i = 0; i < fieldData.Length; i++)
                {
                    if (fieldData[i] == "")
                    {
                        fieldData[i] = null;
                    }
                }
                csvData.Rows.Add(fieldData);
            }

        }
    }
    catch (Exception ex)
    {
    }
    return csvData;
}

This gives out the exact same data as the previous attempt:

    ![enter image description here][2]

But in this method I receive no errors and the Grid gets populated as:

Any help will be greatly appreciated! Thanks

EDIT -------------

This is my csv File:

Answer 1

NOTE: If CSV file encoding is different then ANSI? Then you should use StreamReader following:

StreamReader MyStreamReader = new StreamReader(path, System.Text.Encoding.Unicode);

Otherwise, I have made few changes in the code. See below:

 private static DataTable GetDataTabletFromCSVFile(string path)
 {
    DataTable csvData = new DataTable();        

    try
    {           
        using (TextFieldParser csvReader = new TextFieldParser( path))
        {
           csvReader.SetDelimiters(new string[] { "," });
           csvReader.HasFieldsEnclosedInQuotes = true;
           string[] colFields = csvReader.ReadFields();

           foreach (string column in colFields)
           {
               DataColumn serialno = new DataColumn(column);
               serialno.AllowDBNull = true;                   
               csvData.Columns.Add(serialno);
           }

           while (!csvReader.EndOfData)
           {
                string[] fieldData = csvReader.ReadFields();
                DataRow dr = csvData.NewRow();
                //Making empty value as empty
                for (int i = 0; i < fieldData.Length; i++)
                {
                   if (fieldData[i] == null)                       
                       fieldData[i] = string.Empty;

                   dr[i] = fieldData[i];
                }
                csvData.Rows.Add(dr);              
            }

        }
    }
    catch (Exception ex)
    {
    }
    return csvData;
}