SchemaAnalyzer is interpreting Yes/No/Y/N values as Boolean

[alexandretperez]

Hi everyone,

I would like to check if we have any control on how SchemaAnalyzer interprets column types.

...
var analyzer = new SchemaAnalyzer();
var result = analyzer.Analyze(reader);
options = new ExcelDataReaderOptions { Schema = new ExcelSchema(true, result.GetSchema().GetColumnSchema()) };
...

I have a situation where I'm reading an Excel file which one of the columns can have values as such Yes, No, Canceled, Transferred.

The SchemaAnalyzer interprets the column as string if at least Canceled/Transferred are present, but if only Yes/No are there, the column is interpreted as Boolean.

This inconsistence is bad! I don't think Yes and No values should be intepreted as Boolean at all. The same applies for Y/N.

Columns that should hold one single character A, T, Z, Y, N... also get affected if only Y/N occurrences are there.

The scenarios I covered testing it was with Yes/No/Y/N/0/1. The numbers 0/1 were correctly interpreted as Int32.

Is this a bug or it is by design? If it is by design, can we at least have an option in the SchemaAnalyzer to only consider TRUE/FALSE as real Boolean values?

[MarkPflug]

By design

Sylvan/source/Sylvan.Data/SchemaAnalyzer.cs

Line 706 in f1c461e

static readonly string[] TrueStrings = new[] { "y", "yes", "t", "true" };

[MarkPflug]

Adding the TrueString/FalseString to the schema analyzer options would make sense, or you can handle this yourself as in the following example:

var dat =
	"""
	Id,Flag
	1,y
	2,n
	3,n
	4,y
	""";
var csv = CsvDataReader.Create(new StringReader(dat));
var a = new SchemaAnalyzer();
var result = a.Analyze(csv);
var sb = result.GetSchemaBuilder();

var col = sb[1];
// if it was detected as a boolean, but wasn't "true|false", change it to a string column
if(!StringComparer.OrdinalIgnoreCase.Equals(col.Format, "true|false"))
{
	col.SetType(typeof(string));
}

var schema = sb.Build();
Assert.Equal(typeof(string), schema[1].DataType);

[alexandretperez]

Thanks for sharing an example, it helped me find the solution.

The property Format does not exist in Schema.Column.Builder, only in Schema.Column, so I had to use the result.GetSchema() to acess the Format property and do the checkings, and later use the GetSchemaBuilder() to set the type.