public void ParseCsvFileTest()
{
string filePath = Path.Combine(this.TestContext.TestDeploymentDir, "Files\\1095_Import_Employees.csv");
ICsvFileParser parser = new CsvFileParser(filePath);
IDictionary <int, IList <string> > fileErrors = new Dictionary <int, IList <string> >();
IObjectValidator validator = new ObjectValidator();
int rowIndex = parser.RowStart;
foreach (dynamic row in parser.ParseFile())
{
List <string> errors = new List <string>();
Employee rowObj = FileIOUtilities.MapObject <Employee>(row, rowIndex, validator, null, ref errors);
//rowObj.MapValues(rowIndex, row, validator, ref errors);
validator.TryValidate(rowObj, ref errors);
if (errors.Count > 0)
{
fileErrors.Add(rowIndex, errors);
}
rowIndex++;
}
Assert.IsTrue(fileErrors.Count >= 2);
}
public static IList <Company> ReadCsvFile1()
{
Console.WriteLine("Running ReadCsvFile1");
var parser = new CsvFileParser(Common.CsvDataPath);
var fileErrors = new Dictionary <int, IList <string> >();
var validator = new ObjectValidator();
//parser.Delimiters // can adjust the delimiters
//parser.FixedWidths // can parse fixed widths
var rowIndex = parser.RowStart;
var companies = new List <Company>();
foreach (dynamic row in parser.ParseFile())
{
var errors = new List <string>();
var rowObj = FileIOUtilities.MapObject <Company>(row, rowIndex, validator, null, ref errors);
validator.TryValidate(rowObj, ref errors);
companies.Add(rowObj);
if (errors.Count > 0)
{
fileErrors.Add(rowIndex, errors);
}
rowIndex++;
}
foreach (var errs in fileErrors)
{
foreach (var err in errs.Value)
{
Console.WriteLine("Line:{0}, Error: {1}", errs.Key, err);
}
}
return(companies);
}
/// <summary>
/// Returns an enumerator that iterates through the collection.
/// </summary>
/// <returns>A <see cref="T:System.Collections.Generic.IEnumerator`1" /> that can be used to iterate through the collection.</returns>
public IEnumerator <SqlDataRecord> GetEnumerator()
{
if (_data == null || !_data.Any())
{
yield break;
}
PropertyInfo[] properties = _importType.GetProperties();
StringComparer comparer = StringComparer.InvariantCultureIgnoreCase;
this._validator = this._validator ?? new ObjectValidator();
bool?isDynamicType = null;
int errorColumnOrdinal = -1;
var sqlMetaArray = _sqlMetadata.ToArray();
if (_sqlMetadata.Any(x => comparer.Equals(x.Name, _errorColumn)))
{
SqlDataRecord tempRecord = new SqlDataRecord(sqlMetaArray);
errorColumnOrdinal = tempRecord.GetOrdinal(_errorColumn); //will cause an exception if it does not exist, hence the any check
tempRecord = null;
}
foreach (dynamic row in _data)
{
_rowIndex++;
SqlDataRecord record = new SqlDataRecord(sqlMetaArray);
List <string> errors = new List <string>();
//check the first object to see if it is a dynamic type as we dont need to run it throught the object mapper in that case
if (!isDynamicType.HasValue)
{
isDynamicType = FileIOHelpers.IsDynamicType(row);
}
T rowObj = default(T);
if (isDynamicType.Value)
{
try
{
rowObj = FileIOUtilities.MapObject <T>(row, _rowIndex, _validator, _fileValuesMapper, ref errors);
}
catch (Exception ex)
{
errors.Add(ex.ToString());
}
}
else
{
rowObj = row;
}
try
{
//built in data annotation validation
this._validator.TryValidate(rowObj, ref errors);
//custom validation
if (_customValidator != null)
{
_customValidator.Invoke(rowObj, ref errors);
}
}
catch (Exception ex)
{
errors.Add(ex.ToString());
}
ISqlRecordMapper mapperObj = null;
//if they provide a custom mapper use that one over the interface.
if (this._customSqlMapper != null)
{
this._customSqlMapper.Invoke(rowObj, record, _rowIndex, errors);
}
else if ((mapperObj = rowObj as ISqlRecordMapper) != null)
{
mapperObj.MapSqlRecord(record, _rowIndex, errors);
}
else //last ditch effort, hopefully they don't rely on this
{
object val;
//try to set the rows from the metadata, and the properties
foreach (SqlMetaData metaData in _sqlMetadata)
{
string name = metaData.Name;
val = null;
if (!comparer.Equals(name, _errorColumn))
{
var prop = properties.FirstOrDefault(x => comparer.Equals(x.Name, name));
if (prop != null && (val = prop.GetValue(rowObj, null)) != null)
{
record.SetValue(record.GetOrdinal(name), val);
}
}
}
//if an error column is defined, set the import errors
if (errorColumnOrdinal != -1 && errors.Count != 0)
{
string errorMessage = FileIOHelpers.ErrorsToXml(errors, _rowIndex);
record.SetString(errorColumnOrdinal, errorMessage);
}
}
yield return(record);
}
}
public static IList <Company> ReadExcelFile1(string path = null, int headerRow = 2, int dataRow = 3)
{
/* Some important notes about header names:
* 1) As the header name is being used for the "property" name of the dynamic object there are many characters that are not valid to be a valid c# property name.
* During the reading of the file, the invalid characters in the header name are replaced using this regex [^A-Za-z0-9_]* with an empty string.
* Then if the return of that replace operation is empty, then the column is named "Col(i)". Where (i) is the zero based column index.
* Examples:
* Column Name---------------Property Name
* Company Id----------------row.CompanyId
* Aims Company Id-----------row.AimsCompanyId
* Some_Id_9-----------------row.Some_Id_9
* (@@@)---------------------row.Col1 - where 1 is the zero based index of that column.
*
* 2) Case sensitivity of the property names does not matter either. As the sender could change the case indiscrimiately.
* 3) If a column is removed that you were expecting, the property will return empty and will not throw an exception.
*/
Console.WriteLine("Running ReadExcelFile1");
/*This example shows reading a Company class from a file. */
if (String.IsNullOrWhiteSpace(path))
{
path = Common.ExcelDataPath;
}
//the parser is designed to read one sheet from a file at a time. Other sheets would require a new parser, or just use the same parser and change the sheet name.
IExcelFileParser parser = new ExcelFileParser(path, "Companies", headerRow, dataRow);
//this is where we will store any parser errors as we parse the file
var fileErrors = new Dictionary <int, IList <string> >();
//this validator provides validation when parsing the columns, and data attributes like [Required] and it will also invoke the IValidatableObject interface
IObjectValidator validator = new ObjectValidator();
//as the rowindex may not start at row 1, get it from the parser
int rowIndex = parser.RowStart;
var companies = new List <Company>();
foreach (dynamic row in parser.ParseFile())
{
List <string> errors = new List <string>();
//create a reference to a custom mapper. this provides complete control over the mapping of the file row to the object, and the interface is skipped
FileValuesMap <Company> mapper = Common.Company_FileMapper;
//this utility performs mapping of the row to the object and invokes column mapping validation
var rowObj = FileIOUtilities.MapObject <Company>(row, rowIndex, validator, mapper, ref errors);
//calling the TryValidate method invoke the data annotation validation, and the IValidatableObject interface
validator.TryValidate(rowObj, ref errors);
companies.Add(rowObj);
if (errors.Count > 0)
{
//we got errors for this row, so add them to the fileErrors dictionary
fileErrors.Add(rowIndex, errors);
}
rowIndex++;
}
//write all the file errors out to the console
foreach (var errs in fileErrors)
{
foreach (var err in errs.Value)
{
Console.WriteLine("Line:{0}, Error: {1}", errs.Key, err);
}
}
return(companies);
}
