/// <summary>
/// Takes an existing DataTable with a fixed schema and validates the columns read during <see cref="GetHeadersFromFile"/> against it making minor changes
/// where appropriate to match the schema
/// </summary>
/// <param name="dt"></param>
/// <param name="listener"></param>
/// <returns></returns>
public void MakeDataTableFitHeaders(DataTable dt, IDataLoadEventListener listener)
{
if (_state != State.AfterHeadersRead)
{
throw new Exception("Illegal state, data table cannot be created at state " + _state);
}
_state = State.AfterTableGenerated;
StringBuilder ASCIIArt = new StringBuilder();
List <string> headersNotFound = new List <string>();
for (int index = 0; index < _headers.Length; index++)
{
ASCIIArt.Append("[" + index + "]");
if (dt.Columns.Contains(_headers[index])) //exact match
{
ASCIIArt.AppendLine(_headers[index] + ">>>" + _headers[index]);
continue;
}
if (string.IsNullOrWhiteSpace(_headers[index])) //Empty column header, ignore it
{
ASCIIArt.AppendLine("Blank Column>>>IGNORED");
continue;
}
//if we are ignoring the header
if (IgnoreColumnsList.Contains(_headers[index]))
{
ASCIIArt.AppendLine(_headers[index] + ">>>IGNORED");
continue;
}
//try replacing spaces with underscores
if (dt.Columns.Contains(_headers[index].Replace(" ", "_")))
{
string before = _headers[index];
_headers[index] = _headers[index].Replace(" ", "_");
ASCIIArt.AppendLine(before + ">>>" + _headers[index]);
continue;
}
//try replacing spaces with nothing
if (dt.Columns.Contains(_headers[index].Replace(" ", "")))
{
string before = _headers[index];
_headers[index] = _headers[index].Replace(" ", "");
ASCIIArt.AppendLine(before + ">>>" + _headers[index]);
continue;
}
ASCIIArt.AppendLine(_headers[index] + ">>>" + UnmatchedText);
headersNotFound.Add(_headers[index]);
}
//now that we have adjusted the header names
string[] unmatchedColumns =
dt.Columns.Cast <DataColumn>()
.Where(c => !_headers.Any(h => h != null && h.ToLower().Equals(c.ColumnName.ToLower()))) //get all columns in data table where there are not any with the same name
.Select(c => c.ColumnName)
.ToArray();
if (unmatchedColumns.Any())
{
ASCIIArt.AppendLine(Environment.NewLine + "Unmatched Columns In DataTable:" + Environment.NewLine +
string.Join(Environment.NewLine, unmatchedColumns));
}
//if there is exactly 1 column found by the program and there are unmatched columns it is likely the user has selected the wrong separator
if (_headers.Length == 1 && unmatchedColumns.Any())
{
foreach (string commonSeparator in _commonSeparators)
{
if (_headers[0].Contains(commonSeparator))
{
listener.OnNotify(this, new NotifyEventArgs(ProgressEventType.Error, "Your separator does not appear in the headers line of your file (" + _toLoad.File.Name + ") but the separator '" + commonSeparator + "' does... did you mean to set the Separator to '" + commonSeparator + "'? The headers line is:\"" + _headers[0] + "\""));
}
}
}
listener.OnNotify(this, new NotifyEventArgs(
headersNotFound.Any()?ProgressEventType.Error : ProgressEventType.Information, //information or warning if there are unrecognised field names
"I will now tell you about how the columns in your file do or do not match the columns in your database, Matching flat file columns (or forced replacement headers) against database headers resulted in:" + Environment.NewLine + ASCIIArt)); //tell them about what columns match what
if (headersNotFound.Any())
{
throw new Exception("Could not find a suitable target column for flat file columns " + string.Join(",", headersNotFound) + " amongst database data table columns (" + string.Join(",", from DataColumn col in dt.Columns select col.ColumnName) + ")");
}
}
/// <summary>
/// Creates a new empty DataTable has only the columns found in the headers that were read during <see cref="GetHeadersFromFile"/>
/// </summary>
/// <param name="listener"></param>
/// <returns></returns>
public DataTable GetDataTableWithHeaders(IDataLoadEventListener listener)
{
if (_state != State.AfterHeadersRead)
{
throw new Exception("Illegal state, data table cannot be created at state " + _state);
}
_state = State.AfterTableGenerated;
var dt = new DataTable();
List <string> duplicateHeaders = new List <string>();
List <DataColumn> unamedColumns = new List <DataColumn>();
//create a string column for each header - these will change type once we have read some data
foreach (string header in _headers)
{
string h = header;
//if we are ignoring this column
if (h != null && IgnoreColumnsList.Contains(h.Trim()))
{
continue; //skip adding to dt
}
//watch for duplicate columns
if (dt.Columns.Contains(header))
{
if (_makeHeaderNamesSane)
{
h = MakeHeaderUnique(header, dt.Columns, listener, this);
}
else
{
duplicateHeaders.Add(header);
continue;
}
}
if (h.IsBasicallyNull())
{
unamedColumns.Add(dt.Columns.Add(h));
}
else
//override type
if (_explicitlyTypedColumns != null &&
_explicitlyTypedColumns.ExplicitTypesCSharp.ContainsKey(h))
{
dt.Columns.Add(h, _explicitlyTypedColumns.ExplicitTypesCSharp[h]);
}
else
{
dt.Columns.Add(h);
}
}
UnamedColumns = new ReadOnlyCollection <DataColumn>(unamedColumns);
if (duplicateHeaders.Any())
{
throw new FlatFileLoadException("Found the following duplicate headers in file '" + _toLoad.File + "':" + string.Join(",", duplicateHeaders));
}
return(dt);
}
