/// <summary>
/// reads a CSV file into a Datatable and deduces the data type in each column.
/// </summary>
public static DataTable ReadCSVToTable(string filePath, bool isFirstRowHeader)
{
string[] csvRows = File.ReadAllLines(filePath);
if (csvRows.Length == 0)
{
return(null);
}
//convert rows 1-300 toList of strings so can deduce their types.
var listOfStringArrays = ConvertCSVRowsToListOfStringArrays(csvRows, 1, 300);
Type[] types = DataTools.GetArrayTypes(listOfStringArrays);
//initialise the DataTable
var dt = new DataTable();
if (isFirstRowHeader)
{
string[] headers = csvRows[0].Split(',');
for (int i = 0; i < headers.Length; i++)
{
dt.Columns.Add(headers[i], types[i]);
}
csvRows[0] = null; //remove header row
}
else
{
for (int i = 0; i < types.Length; i++)
{
dt.Columns.Add("Field" + i, types[i]);
}
}
//fill the DataTable
foreach (string csvRow in csvRows)
{
if (csvRow == null)
{
continue; //skip header row
}
var fields = csvRow.Split(',');
var row = dt.NewRow();
row.ItemArray = MakeItemArray(fields, types);
dt.Rows.Add(row);
}
return(dt);
}
