public void CsvTableReader_CaseInsensitivity()
{
CsvTableReader reader;
string table =
@"Col1,Col2,Col3
10,true,25.20
no,10";
reader = new CsvTableReader(new CsvReader(table));
Assert.IsNotNull(reader.ReadRow());
Assert.AreEqual(10, reader.Parse("col1", 0));
Assert.AreEqual(true, reader.Parse("col2", false));
Assert.AreEqual(25.20, reader.Parse("col3", 0.0));
Assert.IsNotNull(reader.ReadRow());
Assert.AreEqual(false, reader.Parse("col1", true));
Assert.AreEqual(10, reader.Parse("col2", 0));
Assert.IsNull(reader.ReadRow());
Assert.IsNull(reader.ReadRow());
}
private async Task Execute(string ecfTableName, Func <CsvTableReader, EcfTableWriter, string[], Task <int> > action)
{
if (ShouldExportTable(ecfTableName, out var ecfFile))
{
// Report status
Console.WriteLine($"[Extracting] [{ecfTableName}] Start...");
// Open Edoosys file for import
using var csvReaderStream = new FileStream(_config.EcfExport.SourceFileName, FileMode.Open, FileAccess.Read, FileShare.None);
// Create CSV Reader for Edoosys file
using var csvReader = new CsvReader(csvReaderStream, Encoding.UTF8, true);
// The Edoosys file is semicolon separeted
csvReader.Configuration.Separator = ';';
// Create CSV Table Reader for Edoosys file
var csvTableReader = new CsvTableReader(csvReader, new CsvConverterResolver());
// Generate ECF file name
var ecfFileName = Path.ChangeExtension(Path.Combine(_config.EcfExport.TargetFolderName, ecfTableName), "csv");
// Create ECF file for export
using var ecfWriterStream = new FileStream(ecfFileName, FileMode.Create, FileAccess.ReadWrite, FileShare.None);
// Create ECF Writer
using var ecfWriter = new CsvWriter(ecfWriterStream, Encoding.UTF8);
// Call table specific action
var ecfRecordCounter = await action(csvTableReader, new EcfTableWriter(ecfWriter), ecfFile?.Headers);
// Inc counters
_recordCounter += ecfRecordCounter;
_tableCounter++;
// Report status
Console.WriteLine($"[Extracting] [{ecfTableName}] {ecfRecordCounter} record(s) extracted");
}
}
private async Task <int> ExportSubjects(CsvTableReader csvTableReader, EcfTableWriter ecfTableWriter, string[] ecfHeaders)
{
var ecfCache = new HashSet <string>();
var ecfRecordCounter = 0;
await csvTableReader.ReadHeadersAsync();
if (ecfHeaders != null && ecfHeaders.Length > 0)
{
await ecfTableWriter.WriteHeadersAsync(ecfHeaders);
}
else
{
await ecfTableWriter.WriteHeadersAsync(
EcfHeaders.Id,
EcfHeaders.Code);
}
while (await csvTableReader.ReadAsync() > 0)
{
for (int i = 1; i < 20; i++)
{
var subject = new ExportSubject(_config, csvTableReader, $"Fach{i}");
if (!string.IsNullOrEmpty(subject.Id) && !ecfCache.Contains(subject.Id))
{
ecfTableWriter.SetValue(EcfHeaders.Id, subject.Id);
ecfTableWriter.SetValue(EcfHeaders.Code, subject.Code);
await ecfTableWriter.WriteAsync();
ecfCache.Add(subject.Id);
ecfRecordCounter++;
}
}
}
return(ecfRecordCounter);
}
public void CsvMapper_MapOneRowCsvToObject_Generic_ShouldWork()
{
const string CsvText = "Name,Year,Description\nJohn Smith,2015,No desription here";
var csv = new CsvTableReader();
var csvRow = csv.ReadToTable(CsvText).Skip(1).ToArray()[0];
var mapping = new ColumnIndexToPropertyNameMapping
{
Mapping = new Dictionary <string, int>
{
{ "Name", 0 },
{ "Year", 1 },
{ "Description", 2 }
}
};
var result = csvRow.MapToObjectProperties <NameYearDescriptionSampleObject>(mapping);
Assert.AreEqual("John Smith", result.Name, "Name should match.");
Assert.AreEqual(2015, result.Year, "Year should match.");
Assert.AreEqual("No desription here", result.Description, "Description should match.");
}
public async Task TestJsonValues()
{
var csvData =
"A;B" + Environment.NewLine +
"42;\"{\"\"IntValue\"\":42,\"\"StrValue\"\":\"\"Forty-Two\"\"}\"" + Environment.NewLine +
"5;\"{\"\"IntValue\"\":5,\"\"StrValue\"\":\"\"Five\"\"}\"";
using var csvReader = new CsvReader(csvData);
var csvTableReader = new CsvTableReader(csvReader);
Assert.NotNull(csvTableReader);
csvTableReader.AddConverter <CustomType>(new CustomTypeConverter());
await csvTableReader.ReadHeadersAsync();
Assert.Equal(2, csvTableReader.Headers.Count);
Assert.Equal("A", csvTableReader.Headers[0]);
Assert.Equal("B", csvTableReader.Headers[1]);
await csvTableReader.ReadAsync();
Assert.Equal(42, csvTableReader.GetValue <int>("A"));
var o1 = csvTableReader.GetValue <CustomType>("B");
Assert.Equal(42, o1.IntValue);
Assert.Equal("Forty-Two", o1.StrValue);
await csvTableReader.ReadAsync();
Assert.Equal(5, csvTableReader.GetValue <int>("A"));
var o2 = csvTableReader.GetValue <CustomType>("B");
Assert.Equal(5, o2.IntValue);
Assert.Equal("Five", o2.StrValue);
}
public void CsvTableReader_DuplicateColumns()
{
CsvTableReader reader;
string table =
@"Col1,Col2,Col2
10,true,25.20
no,10";
reader = new CsvTableReader(new CsvReader(table));
Assert.Equal(2, reader.ColumnMap.Count);
Assert.Equal(0, reader.ColumnMap["Col1"]);
Assert.Equal(1, reader.ColumnMap["Col2"]);
Assert.NotNull(reader.ReadRow());
Assert.Equal("10", reader["Col1"]);
Assert.Equal("true", reader["Col2"]);
Assert.NotNull(reader.ReadRow());
Assert.Equal("no", reader["Col1"]);
Assert.Equal("10", reader["Col2"]);
Assert.Null(reader.ReadRow());
Assert.Null(reader.ReadRow());
}
public void CsvTableReader_DuplicateColumns()
{
CsvTableReader reader;
string table =
@"Col1,Col2,Col2
10,true,25.20
no,10";
reader = new CsvTableReader(new CsvReader(table));
Assert.AreEqual(2, reader.ColumnMap.Count);
Assert.AreEqual(0, reader.ColumnMap["Col1"]);
Assert.AreEqual(1, reader.ColumnMap["Col2"]);
Assert.IsNotNull(reader.ReadRow());
Assert.AreEqual(10, reader.Parse("Col1", 0));
Assert.AreEqual(true, reader.Parse("Col2", false));
Assert.IsNotNull(reader.ReadRow());
Assert.AreEqual(false, reader.Parse("Col1", true));
Assert.AreEqual(10, reader.Parse("Col2", 0));
Assert.IsNull(reader.ReadRow());
Assert.IsNull(reader.ReadRow());
}
private async Task <int> ExportSchoolClasses(CsvTableReader csvTableReader, EcfTableWriter ecfTableWriter, string[] ecfHeaders)
{
var ecfCache = new HashSet <string>();
var ecfRecordCounter = 0;
await csvTableReader.ReadHeadersAsync();
if (ecfHeaders != null && ecfHeaders.Length > 0)
{
await ecfTableWriter.WriteHeadersAsync(ecfHeaders);
}
else
{
await ecfTableWriter.WriteHeadersAsync(
EcfHeaders.Id,
EcfHeaders.Code);
}
while (await csvTableReader.ReadAsync() > 0)
{
var schoolClass = new CsvExportSchoolClass(csvTableReader);
if (!string.IsNullOrEmpty(schoolClass.Id) && !ecfCache.Contains(schoolClass.Id))
{
ecfTableWriter.TrySetValue(EcfHeaders.Id, schoolClass.Id);
ecfTableWriter.TrySetValue(EcfHeaders.Code, schoolClass.Code);
await ecfTableWriter.WriteAsync();
ecfCache.Add(schoolClass.Id);
ecfRecordCounter++;
}
}
return(ecfRecordCounter);
}
private static int Import(string tableName, string csvPath, string scriptPath)
{
const int blockLines = 1000;
try
{
using (var csvReader = new CsvTableReader(csvPath, Encoding.UTF8))
{
using (var writer = new StreamWriter(scriptPath, false, Encoding.UTF8))
{
int cRowsWritten = 0;
for (var row = csvReader.ReadRow(); row != null; row = csvReader.ReadRow())
{
writer.Write("insert into {0} (", tableName);
for (int i = 0; i < csvReader.Columns.Count; i++)
{
if (i > 0)
{
writer.Write(", ");
}
writer.Write(csvReader.Columns[i]);
}
writer.Write(") values (");
for (int i = 0; i < csvReader.Columns.Count; i++)
{
if (i > 0)
{
writer.Write(", ");
}
writer.Write(SqlHelper.Literal(row[i]));
}
writer.WriteLine(")");
cRowsWritten++;
if (cRowsWritten % blockLines == 0)
{
writer.WriteLine("go"); // Write a "go" every [blockLines] lines
}
}
if (cRowsWritten > 0 && cRowsWritten % blockLines != 0)
{
writer.WriteLine("go"); // Terminate with a "go" if necessary
}
}
}
}
catch (Exception e)
{
Program.Error("Error ({0}): {1}", e.GetType().Name, e.Message);
return(1);
}
return(0);
}
private async Task <int> ExportStudentSubjects(CsvTableReader csvTableReader, EcfTableWriter ecfTableWriter, string[] ecfHeaders)
{
var ecfRecordCounter = 0;
await csvTableReader.ReadHeadersAsync();
if (ecfHeaders != null && ecfHeaders.Length > 0)
{
await ecfTableWriter.WriteHeadersAsync(ecfHeaders);
}
else
{
await ecfTableWriter.WriteHeadersAsync(
EcfHeaders.StudentId,
EcfHeaders.SchoolClassId,
EcfHeaders.SubjectId,
EcfHeaders.TeacherId);
}
while (await csvTableReader.ReadAsync() > 0)
{
var student = new CsvExportStudent(csvTableReader);
var schoolClass = new CsvExportSchoolClass(csvTableReader);
if (csvTableReader.TryGetValue("Alle Lehrkräfte (Kürzel) mit Fach", out var value))
{
var csvLineParser = new CsvLineParser(',');
var subValues = csvLineParser.Read(value);
csvLineParser.Configuration.Separator = ' ';
foreach (var subValue in subValues)
{
if (!string.IsNullOrEmpty(subValue))
{
var subValueParts = csvLineParser.Read(subValue.Trim());
if (subValueParts.Length == 2)
{
var teacherCode = subValueParts[0];
var subjectCode = subValueParts[1];
if (!string.IsNullOrEmpty(subjectCode))
{
ecfTableWriter.TrySetValue(EcfHeaders.StudentId, student.Id);
ecfTableWriter.TrySetValue(EcfHeaders.SchoolClassId, schoolClass.Id);
ecfTableWriter.TrySetValue(EcfHeaders.SubjectId, subjectCode);
ecfTableWriter.TrySetValue(EcfHeaders.TeacherId, teacherCode);
await ecfTableWriter.WriteAsync();
ecfRecordCounter++;
}
}
}
}
}
}
return(ecfRecordCounter);
}
