Some time back I wrote article on how to export data from DataGrid into a CSV file. This article in complement to the earlier article and will answer the following questions.

• How to import CSV file into DataGrid?
• How to import CSV file into GridView?
• How to create DataTable from CSV file?
• How to create DataSet from CSV file?

From the definition of the problem it does not sound that hard to import CSV and create a DataTable and bind it to a DataGrid or GridView. But when I tried to create a sample CSV file by exporting data from AdventureWorks sample database I saw that the lines can have different number of column depending on the schema of the table and data types stored. Some fields were and some were not. Which created gaps in the rows. Finally I came up with the implementation that adds columns to the DataTable on the fly. When an increase in value count is detected more columns are added.

The attached project contains sample page which allows user to upload a CSV file and then we read the file in codeebehind , create a DataTable from it and then bind it to GridView.

Reading CSV File And Creating DataTable

The first task in this implementation is going to be reading CSV file and creating DataTable. This is the part where you will have to make some decision about how many columns and data types for the columns in the table. Since data in CSV is all text, safe approach would be to create each column in the table as String. And you can name the columns with index of column appended to text like "Column" to use as column name. As you read each line, compare the number of values in current line with the number of columns in table. If there are more values in the line than columns then add more required columns to DataTable and continue till you have read all the lines. Following code snippet is from the sample project for this article.

private void PopulateDataTableFromUploadedFile(System.IO.Stream strm)
{
    System.IO.StreamReader srdr = new System.IO.StreamReader(strm);
    String strLine = String.Empty;
    Int32 iLineCount = 0;
    do
    {
        strLine = srdr.ReadLine();
        if (strLine == null)
        {
            break;
        }
        if (0 == iLineCount++)
        {
            m_dtCSV = this.CreateDataTableForCSVData(strLine);
        }
        this.AddDataRowToTable(strLine, m_dtCSV);
    } while (true);
}       

private DataTable CreateDataTableForCSVData(String strLine)
{
    DataTable dt = new DataTable("CSVTable");
    String[] strVals = strLine.Split(new char[] { ',' });
    m_iColumnCount = strVals.Length;
    int idx = 0;
    foreach(String strVal in strVals)
    {
        String strColumnName = String.Format("Column-{0}", idx++);
        dt.Columns.Add(strColumnName, Type.GetType("System.String"));
    }
    return dt;
}

private DataRow AddDataRowToTable(String strCSVLine, DataTable dt)
{
    String[] strVals = strCSVLine.Split(new char[] { ',' });
    Int32 iTotalNumberOfValues = strVals.Length;
    // If number of values in this line are more than the columns
    // currently in table, then we need to add more columns to table.
    if (iTotalNumberOfValues > m_iColumnCount)
    {
        Int32 iDiff = iTotalNumberOfValues - m_iColumnCount;
        for(Int32 i = 0; i < iDiff; i++)
        {
            String strColumnName = String.Format("Column-{0}", (m_iColumnCount + i));
            dt.Columns.Add(strColumnName, Type.GetType("System.String"));
        }
        m_iColumnCount = iTotalNumberOfValues;
    }
    int idx = 0;
    DataRow drow = dt.NewRow();
    foreach (String strVal in strVals)
    {
        String strColumnName = String.Format("Column-{0}", idx++);
        drow[strColumnName] = strVal.Trim();
    }
    dt.Rows.Add(drow);
    return drow;
}                     
                            

Loading Into Grid

Once you have read the file and created DataTable from it, next step is simple. Attach this DataTable as DataSource for Grid and then call DataBind and you are