How to read a CSV file into a .NET Datatable

by

I have been using OleDb provider. However, it has problems if you are reading in rows that have numeric values but you want them treated as text. However, you can get around that issue by creating a schema.ini file. Here is my method I used:

// using System.Data;
// using System.Data.OleDb;
// using System.Globalization;
// using System.IO;

static DataTable GetDataTableFromCsv(string path, bool isFirstRowHeader)
{
    string header = isFirstRowHeader ? "Yes" : "No";

    string pathOnly = Path.GetDirectoryName(path);
    string fileName = Path.GetFileName(path);

    string sql = @"SELECT * FROM [" + fileName + "]";

    using(OleDbConnection connection = new OleDbConnection(
              @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathOnly + 
              ";Extended Properties=\"Text;HDR=" + header + "\""))
    using(OleDbCommand command = new OleDbCommand(sql, connection))
    using(OleDbDataAdapter adapter = new OleDbDataAdapter(command))
    {
        DataTable dataTable = new DataTable();
        dataTable.Locale = CultureInfo.CurrentCulture;
        adapter.Fill(dataTable);
        return dataTable;
    }
}

More Related Contents:

  1. How to split csv whose columns may contain ,
  2. Reading CSV file and storing values into an array
  3. Read SQL Table into C# DataTable
  4. Best way to remove duplicate entries from a data table
  5. How can I turn a DataTable to a CSV?
  6. Reading a CSV file in .NET?
  7. How to select min and max values of a column in a datatable?
  8. Convert DataTable to CSV stream
  9. How to compare 2 dataTables
  10. C# – Fill a combo box with a DataTable
  11. Convert DataTable to generic List?
  12. DataTable to JSON [duplicate]
  13. Using C# to search a CSV file and pull the value in the column next to it
  14. DataTable equivalent in Java [duplicate]
  15. How to split csv whose columns may contain comma
  16. Best way to check if a Data Table has a null value in it
  17. Writing a CSV file in .net
  18. Get Cell Value from a DataTable in C#
  19. Returning DataTables in WCF/.NET
  20. Upload CSV file to SQL server
  21. Is there a “proper” way to read CSV files [duplicate]
  22. How to convert DataTable to class Object?
  23. Replacing a DataReader with a DataTable
  24. DataTable internal index is corrupted
  25. Does any one know of a faster method to do String.Split()?
  26. How can I convert a DataTable to an XML file in C#?
  27. Writing a Header using CsvHelper? C#
  28. regex to split line (csv file)
  29. Why can’t I do foreach (var Item in DataTable.Rows)?
  30. When reading a CSV file using a DataReader and the OLEDB Jet data provider, how can I control column data types?

Leave a Comment