public void TestNullDeserializationWithToken()
{
var csv = "FIRSTCOLUMN,SECONDCOLUMN,THIRDCOLUMN\n" +
"SPECIAL_NULL,SPECIAL_NULL,SPECIAL_NULL\n" +
"\"hi2, hi2, hi2!\",34,Second\n" +
"\"hi3 says, \"\"Hi Three!\"\"\",56,Third\n" +
"NULL,7,Fourth\n" +
",8,Second";
var settings = new CSVSettings()
{
AllowNull = true,
NullToken = "SPECIAL_NULL",
};
// Deserialize back from a CSV string - should not throw any errors!
var list = CSV.Deserialize <TestClassTwo>(csv, settings).ToList();
Assert.AreEqual(null, list[0].FirstColumn);
Assert.AreEqual(0, list[0].SecondColumn);
Assert.AreEqual(null, list[0].ThirdColumn);
// Did the regular text "NULL" still come through?
Assert.AreEqual("NULL", list[3].FirstColumn);
// Did an empty field get imported as an empty string?
Assert.AreEqual("", list[4].FirstColumn);
}
public async Task TestAsyncReader()
{
// The "sep=" line is a feature of Microsoft Excel that makes CSV files work more smoothly with
// European data sets where a comma is used in numerical separation. If present, it overrides
// the FieldDelimiter setting from the CSV Settings.
var source = "sep=|\n" +
"Name|Title|Phone\n" +
"JD|Doctor|x234\n" +
"Janitor|Janitor|x235\n" +
"\"Dr. Reed, " + Environment.NewLine + "Eliot\"|\"Private \"\"Practice\"\"\"|x236\n" +
"Dr. Kelso|Chief of Medicine|x100";
// Convert into stream
var settings = new CSVSettings()
{
HeaderRowIncluded = true, FieldDelimiter = '\t', AllowSepLine = true
};
using (var cr = CSVReader.FromString(source, settings))
{
// The field delimiter should have been changed, but the original object should remain the same
Assert.AreEqual('\t', settings.FieldDelimiter);
Assert.AreEqual('|', cr.Settings.FieldDelimiter);
Assert.AreEqual("Name", cr.Headers[0]);
Assert.AreEqual("Title", cr.Headers[1]);
Assert.AreEqual("Phone", cr.Headers[2]);
var i = 1;
await foreach (var line in cr)
{
switch (i)
{
case 1:
Assert.AreEqual("JD", line[0]);
Assert.AreEqual("Doctor", line[1]);
Assert.AreEqual("x234", line[2]);
break;
case 2:
Assert.AreEqual("Janitor", line[0]);
Assert.AreEqual("Janitor", line[1]);
Assert.AreEqual("x235", line[2]);
break;
case 3:
Assert.AreEqual("Dr. Reed, " + Environment.NewLine + "Eliot", line[0]);
Assert.AreEqual("Private \"Practice\"", line[1]);
Assert.AreEqual("x236", line[2]);
break;
case 4:
Assert.AreEqual("Dr. Kelso", line[0]);
Assert.AreEqual("Chief of Medicine", line[1]);
Assert.AreEqual("x100", line[2]);
break;
}
i++;
}
}
}
public void TestBasicReader()
{
string source = "Name,Title,Phone\n" +
"JD,Doctor,x234\n" +
"Janitor,Janitor,x235\n" +
"\"Dr. Reed, " + Environment.NewLine + "Eliot\",\"Private \"\"Practice\"\"\",x236\n" +
"Dr. Kelso,Chief of Medicine,x100";
// Skip header row
CSVSettings settings = new CSVSettings()
{
HeaderRowIncluded = false
};
// Convert into stream
byte[] byteArray = Encoding.UTF8.GetBytes(source);
MemoryStream stream = new MemoryStream(byteArray);
using (StreamReader sr = new StreamReader(stream)) {
using (CSVReader cr = new CSVReader(sr, settings)) {
int i = 0;
foreach (string[] line in cr)
{
if (i == 0)
{
Assert.AreEqual(line[0], "Name");
Assert.AreEqual(line[1], "Title");
Assert.AreEqual(line[2], "Phone");
}
else if (i == 1)
{
Assert.AreEqual(line[0], "JD");
Assert.AreEqual(line[1], "Doctor");
Assert.AreEqual(line[2], "x234");
}
else if (i == 2)
{
Assert.AreEqual(line[0], "Janitor");
Assert.AreEqual(line[1], "Janitor");
Assert.AreEqual(line[2], "x235");
}
else if (i == 3)
{
Assert.AreEqual(line[0], "Dr. Reed, " + Environment.NewLine + "Eliot");
Assert.AreEqual(line[1], "Private \"Practice\"");
Assert.AreEqual(line[2], "x236");
}
else if (i == 4)
{
Assert.AreEqual(line[0], "Dr. Kelso");
Assert.AreEqual(line[1], "Chief of Medicine");
Assert.AreEqual(line[2], "x100");
}
i++;
}
}
}
}
public void TestAlternateDelimiterQualifiers()
{
var source = "Name\tTitle\tPhone\n" +
"JD\tDoctor\tx234\n" +
"Janitor\tJanitor\tx235\n" +
"\"Dr. Reed, " + Environment.NewLine + "Eliot\"\t\"Private \"\"Practice\"\"\"\tx236\n" +
"Dr. Kelso\tChief of Medicine\tx100";
// Convert into stream
var settings = new CSVSettings()
{
HeaderRowIncluded = true, FieldDelimiter = '\t'
};
using (var cr = CSVReader.FromString(source, settings))
{
Assert.AreEqual("Name", cr.Headers[0]);
Assert.AreEqual("Title", cr.Headers[1]);
Assert.AreEqual("Phone", cr.Headers[2]);
var i = 1;
foreach (var line in cr)
{
Assert.AreEqual(3, line.Length);
switch (i)
{
case 1:
Assert.AreEqual("JD", line[0]);
Assert.AreEqual("Doctor", line[1]);
Assert.AreEqual("x234", line[2]);
break;
case 2:
Assert.AreEqual("Janitor", line[0]);
Assert.AreEqual("Janitor", line[1]);
Assert.AreEqual("x235", line[2]);
break;
case 3:
Assert.AreEqual("Dr. Reed, " + Environment.NewLine + "Eliot", line[0]);
Assert.AreEqual("Private \"Practice\"", line[1]);
Assert.AreEqual("x236", line[2]);
break;
case 4:
Assert.AreEqual("Dr. Kelso", line[0]);
Assert.AreEqual("Chief of Medicine", line[1]);
Assert.AreEqual("x100", line[2]);
break;
}
i++;
}
}
}
private Dictionary <string, int> GetColumnNames(CSVColumnSettings columnSettings)
{
if (columnSettings.DataRows.Length == 0 || columnSettings.DataRows.Length <= columnSettings.StartRowIndex)
{
throw new ArgumentException($"{nameof(columnSettings.DataRows)} must no be empty");
}
string[] columns = null;
var settings = new CSVSettings
{
Separator = columnSettings.Separator,
HasSurroundingQuotationMarks = columnSettings.HasSurroundingQuotationMarks
};
if (columnSettings.HasColumnRow)
{
if (columnSettings.HasSurroundingQuotationMarks)
{
var columnRow = columnSettings.DataRows[columnSettings.StartRowIndex];
columns = columnRow.Split('\"').Where(part => part != columnSettings.Separator.ToString()).Skip(1).ToArray();
if (columns.Last().IsNullOrEmpty() && !columnRow.EndsWith(columnSettings.Separator.ToString()))
{
columns = columns.Take(columns.Length - 1).ToArray();
}
}
else
{
columns = columnSettings.DataRows[columnSettings.StartRowIndex].Replace("\n", "").Trim().Split(columnSettings.Separator);
}
}
else
{
foreach (var columnRow in columnSettings.DataRows.Skip(columnSettings.StartRowIndex))
{
settings.DataRow = columnRow;
var dataCells = GetDataCells(settings);
if (columns == null || columns.Length < dataCells.Length)
{
columns = dataCells;
}
}
}
var columnsDictionary = new Dictionary <string, int>();
for (var i = 0; i < columns.Length; i++)
{
columnsDictionary.Add(columnSettings.HasColumnRow ? columns[i] : i.ToString(), i);
}
return(columnsDictionary);
}
public void DeserializeExcludeColumns()
{
var csv = "TestString,IntProperty,NullableSingle\n" +
"Test String,57,12.35\n" +
"Test String,57,\n" +
"Test String,57,56.19\n" +
"Test String,57,\n" +
"\n";
// Let's specifically allow null
var settings = new CSVSettings
{
AllowNull = true,
NullToken = string.Empty,
IgnoreEmptyLineForDeserialization = true,
ExcludedColumns = new [] { "TestString" }
};
// Try deserializing - we should see nulls in the TestString column
var list = CSV.Deserialize <TestClassLastColumnNullableSingle>(csv, settings).ToList();
Assert.AreEqual(4, list.Count);
Assert.IsNull(list[0].TestString);
Assert.AreEqual(57, list[0].IntProperty);
Assert.AreEqual(12.35f, list[0].NullableSingle);
Assert.IsNull(list[1].TestString);
Assert.AreEqual(57, list[1].IntProperty);
Assert.IsNull(list[1].NullableSingle);
Assert.IsNull(list[2].TestString);
Assert.AreEqual(57, list[2].IntProperty);
Assert.AreEqual(56.19f, list[2].NullableSingle);
Assert.IsNull(list[3].TestString);
Assert.AreEqual(57, list[3].IntProperty);
Assert.IsNull(list[3].NullableSingle);
// Try serializing - we should no longer see a TestString column
settings.LineSeparator = "\n";
var backToCsv = CSV.Serialize(list, settings);
Assert.AreEqual("IntProperty,NullableSingle\n" +
"57,12.35\n" +
"57,\n" +
"57,56.19\n" +
"57,\n", backToCsv);
}
public VaccinatieController(ILogger <VaccinatieController> logger, IOptions <CSVSettings> settings, IMapper mapper)
{
_mapper = mapper;
_CSVSettings = settings.Value;
_logger = logger;
configuration = new CsvConfiguration(CultureInfo.InvariantCulture)
{
HasHeaderRecord = false,
Delimiter = ";"
};
if (_locaties.Count == 0)
{
using (var reader = new StreamReader(_CSVSettings.CSVLocations))
using (var csv = new CsvReader(reader, configuration)){
// csv.Read();
while (csv.Read())
{
var record = csv.GetRecord <VaccinatieLocatie>();
_locaties.Add(record);
}
}
}
if (_types.Count == 0)
{
using (var reader = new StreamReader(_CSVSettings.CSVTypes))
using (var csv = new CsvReader(reader, configuration)){
// csv.Read();
while (csv.Read())
{
var record = csv.GetRecord <VaccinatieType>();
_types.Add(record);
}
}
}
if (_registratiess.Count == 0)
{
using (var reader = new StreamReader(_CSVSettings.CSVRegistraties))
using (var csv = new CsvReader(reader, configuration)){
// csv.Read();
while (csv.Read())
{
var record = csv.GetRecord <Vaccinatie>();
_registratiess.Add(record);
}
}
}
}
private T ProcessDataRow <T>(CSVSettings settings) where T : class
{
var dataCells = GetDataCells(settings);
var dataRecordSettings = new CSVSettingsDataRecord
{
ColumnNames = settings.ColumnNames,
Properties = settings.Properties,
DataCells = dataCells,
DataRecord = null,
DataRecordType = typeof(T),
CultureInfo = settings.CultureInfo
};
return(ProcessDataRowForDataRecordType(dataRecordSettings) as T);
}
public void TestForceQualifiers()
{
var array = new string[] { "one", "two", "three", "four, five" };
var s = CSV.ToCSVString(array);
Assert.AreEqual("one,two,three,\"four, five\"", s);
// Now construct new settings
var settings = new CSVSettings()
{
FieldDelimiter = '|',
TextQualifier = '\'',
ForceQualifiers = true
};
s = CSV.ToCSVString(array, settings);
Assert.AreEqual("'one'|'two'|'three'|'four, five'", s);
}
public IEnumerable <T> Convert <T>(DataProperty configuration, string data, bool removeDublicates = true) where T : class, IEmpty
{
if (data.IsNullOrEmpty())
{
return(new List <T>());
}
data = data.Replace("\r", "");
var dataRows = data.Split('\n');
var dataRecords = new List <T>();
var columnSettings = new CSVColumnSettings
{
DataRows = dataRows,
HasColumnRow = configuration.HasColumnNamesCSV,
HasSurroundingQuotationMarks = configuration.HasSurroundingQuotationMarksCSV,
Separator = configuration.SeparatorCSVColumn,
StartRowIndex = configuration.StartRowIndexCSV
};
var settings = new CSVSettings
{
ColumnNames = GetColumnNames(columnSettings),
Properties = configuration.DataProperties,
Separator = configuration.SeparatorCSVColumn,
CultureInfo = configuration.CultureCode.GetCultureInfo(),
HasSurroundingQuotationMarks = configuration.HasSurroundingQuotationMarksCSV
};
var startRowIndex = configuration.StartRowIndexCSV + (configuration.HasColumnNamesCSV ? 1 : 0);
for (var rowIndex = startRowIndex; rowIndex < dataRows.Length; rowIndex++)
{
settings.DataRow = dataRows[rowIndex];
var dataRecord = ProcessDataRow <T>(settings);
if (!(dataRecord?.IsEmpty ?? false))
{
dataRecords.Add(dataRecord);
}
}
return(removeDublicates ? RemoveDoublets(configuration, dataRecords) : dataRecords);
}
private string[] GetDataCells(CSVSettings settings)
{
string[] dataCells;
var dataRow = settings.DataRow.Replace("\n", "").Trim();
if (settings.HasSurroundingQuotationMarks)
{
dataCells = dataRow.Split('\"').Where(part => part != settings.Separator.ToString()).Skip(1).ToArray();
if (dataCells.Last().IsNullOrEmpty() && !dataRow.EndsWith(settings.Separator.ToString()))
{
dataCells = dataCells.Take(dataCells.Length - 1).ToArray();
}
}
else
{
dataCells = dataRow.Split(settings.Separator);
}
return(dataCells);
}
public void TestNullDeserialization()
{
var csv = "FIRSTCOLUMN,SECONDCOLUMN,THIRDCOLUMN\n" +
",,\n" +
"\"hi2, hi2, hi2!\",34,Second\n" +
"\"hi3 says, \"\"Hi Three!\"\"\",56,Third\n" +
"NULL,7,Fourth";
var settings = new CSVSettings()
{
AllowNull = true,
NullToken = string.Empty,
};
// Deserialize back from a CSV string - should not throw any errors!
var list = CSV.Deserialize <TestClassTwo>(csv, settings).ToList();
Assert.AreEqual(null, list[0].FirstColumn);
Assert.AreEqual(0, list[0].SecondColumn);
Assert.AreEqual(null, list[0].ThirdColumn);
}
public async Task DeserializeLastColumnNullableSingleAsync()
{
var csv = "TestString,IntProperty,NullableSingle\n" +
"Test String,57,12.35\n" +
"Test String,57,\n" +
"Test String,57,56.19\n" +
"Test String,57,\n" +
"\n";
// Let's specifically allow null
var settings = new CSVSettings();
settings.AllowNull = true;
settings.NullToken = string.Empty;
settings.IgnoreEmptyLineForDeserialization = true;
// Try deserializing using the async version
var list = await CSV.DeserializeAsync <TestClassLastColumnNullableSingle>(csv, settings).ToListAsync();
Assert.AreEqual(4, list.Count);
Assert.AreEqual("Test String", list[0].TestString);
Assert.AreEqual(57, list[0].IntProperty);
Assert.AreEqual(12.35f, list[0].NullableSingle);
Assert.AreEqual("Test String", list[1].TestString);
Assert.AreEqual(57, list[1].IntProperty);
Assert.IsNull(list[1].NullableSingle);
Assert.AreEqual("Test String", list[2].TestString);
Assert.AreEqual(57, list[2].IntProperty);
Assert.AreEqual(56.19f, list[2].NullableSingle);
Assert.AreEqual("Test String", list[3].TestString);
Assert.AreEqual(57, list[3].IntProperty);
Assert.IsNull(list[3].NullableSingle);
}
public void DeserializeLastColumnNullableSingle()
{
var csv = "TestString,IntProperty,NullableSingle\n" +
"Test String,57,12.35\n" +
"Test String,57,\n" +
"Test String,57,56.19\n" +
"Test String,57,\n" +
"\n";
// Let's specifically allow null
var settings = new CSVSettings();
settings.AllowNull = true;
settings.NullToken = string.Empty;
settings.IgnoreEmptyLineForDeserialization = true;
// Deserialize back from a CSV string - should not throw any errors!
var list = CSV.Deserialize <TestClassLastColumnNullableSingle>(csv, settings).ToList();
Assert.AreEqual(4, list.Count);
Assert.AreEqual("Test String", list[0].TestString);
Assert.AreEqual(57, list[0].IntProperty);
Assert.AreEqual(12.35f, list[0].NullableSingle);
Assert.AreEqual("Test String", list[1].TestString);
Assert.AreEqual(57, list[1].IntProperty);
Assert.IsNull(list[1].NullableSingle);
Assert.AreEqual("Test String", list[2].TestString);
Assert.AreEqual(57, list[2].IntProperty);
Assert.AreEqual(56.19f, list[2].NullableSingle);
Assert.AreEqual("Test String", list[3].TestString);
Assert.AreEqual(57, list[3].IntProperty);
Assert.IsNull(list[3].NullableSingle);
}
public void TestDanglingFields()
{
var source = "Name,Title,Phone,Dangle\n" +
"JD,Doctor,x234,\n" +
"Janitor,Janitor,x235,\n" +
"\"Dr. Reed, " + Environment.NewLine + "Eliot\",\"Private \"\"Practice\"\"\",x236,\n" +
"Dr. Kelso,Chief of Medicine,x100,\n" +
",,,";
// Skip header row
var settings = new CSVSettings()
{
HeaderRowIncluded = false
};
// Convert into stream
using (var cr = CSVReader.FromString(source, settings))
{
var i = 0;
foreach (var line in cr)
{
Assert.AreEqual(4, line.Length);
switch (i)
{
case 0:
Assert.AreEqual("Name", line[0]);
Assert.AreEqual("Title", line[1]);
Assert.AreEqual("Phone", line[2]);
Assert.AreEqual("Dangle", line[3]);
break;
case 1:
Assert.AreEqual("JD", line[0]);
Assert.AreEqual("Doctor", line[1]);
Assert.AreEqual("x234", line[2]);
Assert.AreEqual("", line[3]);
break;
case 2:
Assert.AreEqual("Janitor", line[0]);
Assert.AreEqual("Janitor", line[1]);
Assert.AreEqual("x235", line[2]);
Assert.AreEqual("", line[3]);
break;
case 3:
Assert.AreEqual("Dr. Reed, " + Environment.NewLine + "Eliot", line[0]);
Assert.AreEqual("Private \"Practice\"", line[1]);
Assert.AreEqual("x236", line[2]);
Assert.AreEqual("", line[3]);
break;
case 4:
Assert.AreEqual("Dr. Kelso", line[0]);
Assert.AreEqual("Chief of Medicine", line[1]);
Assert.AreEqual("x100", line[2]);
Assert.AreEqual("", line[3]);
break;
case 5:
Assert.AreEqual("", line[0]);
Assert.AreEqual("", line[1]);
Assert.AreEqual("", line[2]);
Assert.AreEqual("", line[3]);
break;
default:
Assert.IsTrue(false, "Should not get here");
break;
}
i++;
}
}
}
public void DeserializeReadOnlyProperty()
{
var csv = "TestString,DateProperty,ReadOnlySingle\n" +
"Test String,2020-01-01,12.35\n" +
"Test String,2001-03-18,\n" +
"Test String,1997-05-04,56.19\n" +
"Test String,1993-06-03,\n" +
"\n";
// Let's specifically allow null
var settings = new CSVSettings
{
AllowNull = true,
NullToken = string.Empty,
IgnoreEmptyLineForDeserialization = true
};
// Deserialize back from a CSV string - should throw an error because "ReadOnlySingle" is read only
var ex = Assert.Throws <Exception>(() =>
{
var testClassHasReadOnlyProperties = CSV.Deserialize <TestClassHasReadOnlyProperty>(csv, settings).ToList();
});
Assert.AreEqual("The column header 'ReadOnlySingle' matches a read-only property. To ignore this exception, enable IgnoreReadOnlyProperties and IgnoreHeaderErrors.", ex.Message);
Assert.AreEqual(typeof(Exception), ex.GetType());
// Now do the same thing while ignoring read-only properties
settings.IgnoreReadOnlyProperties = true;
settings.IgnoreHeaderErrors = true;
var list = CSV.Deserialize <TestClassHasReadOnlyProperty>(csv, settings).ToList();
Assert.AreEqual(4, list.Count);
Assert.AreEqual("Test String", list[0].TestString);
Assert.AreEqual(new DateTime(2020, 1, 1), list[0].DateProperty);
Assert.IsNull(list[0].ReadOnlySingle);
Assert.AreEqual("Test String", list[1].TestString);
Assert.AreEqual(new DateTime(2001, 3, 18), list[1].DateProperty);
Assert.IsNull(list[1].ReadOnlySingle);
Assert.AreEqual("Test String", list[2].TestString);
Assert.AreEqual(new DateTime(1997, 5, 4), list[2].DateProperty);
Assert.IsNull(list[2].ReadOnlySingle);
Assert.AreEqual("Test String", list[3].TestString);
Assert.AreEqual(new DateTime(1993, 6, 3), list[3].DateProperty);
Assert.IsNull(list[3].ReadOnlySingle);
// Return this array back to a string
settings.LineSeparator = "\n";
var backToCsv = CSV.Serialize(list, settings);
Assert.AreEqual("TestString,DateProperty,ReadOnlySingle\n" +
"Test String,2020-01-01T00:00:00.0000000,\n" +
"Test String,2001-03-18T00:00:00.0000000,\n" +
"Test String,1997-05-04T00:00:00.0000000,\n" +
"Test String,1993-06-03T00:00:00.0000000,\n", backToCsv);
// Try forcing all dates to be text-qualified
settings.LineSeparator = "\n";
settings.DateTimeFormat = "yyyy-MM-dd";
settings.ForceQualifierTypes = new [] { typeof(DateTime) };
backToCsv = CSV.Serialize(list, settings);
Assert.AreEqual("TestString,DateProperty,ReadOnlySingle\n" +
"Test String,\"2020-01-01\",\n" +
"Test String,\"2001-03-18\",\n" +
"Test String,\"1997-05-04\",\n" +
"Test String,\"1993-06-03\",\n", backToCsv);
}
