CsvDataReader GetTypedReader()
{
var tr = File.OpenText("Data\\Types.csv");
var schema = new TypedCsvSchema();
schema.Add("Byte", typeof(byte));
schema.Add("Int16", typeof(short));
schema.Add("Int32", typeof(int));
schema.Add("Int64", typeof(long));
schema.Add("Char", typeof(char));
schema.Add("String", typeof(string));
schema.Add("Bool", typeof(bool));
schema.Add("Float", typeof(float));
schema.Add("Double", typeof(double));
schema.Add("DateTime", typeof(DateTime));
schema.Add("Decimal", typeof(decimal));
schema.Add("Guid", typeof(Guid));
var opts = new CsvDataReaderOptions()
{
Schema = schema
};
return(CsvDataReader.Create(tr, opts));
}
public void SqlTVPSimple2Csv()
{
using var csvText = GetData(); // Gets a TextReader over a large-ish CSV dataset
var conn = GetConnection();
conn.Open();
var schema = GetSchema(conn, "Simple2");
var reader = File.OpenText("Data/Simple2Data.csv");
var dataReader = CsvDataReader.Create(reader, new CsvDataReaderOptions {
Schema = schema
});
using var cmd = conn.CreateCommand();
cmd.CommandText = "InsertSimple2";
cmd.CommandType = CommandType.StoredProcedure;
var param = new SqlParameter()
{
ParameterName = "data",
SqlDbType = SqlDbType.Structured
};
var paramData = dataReader;
param.Value = paramData;
cmd.Parameters.Add(param);
cmd.ExecuteNonQuery();
}
public void Sync()
{
using (var reader = File.OpenText("Data\\Simple.csv"))
{
var csv = CsvDataReader.Create(reader);
Assert.Equal(4, csv.FieldCount);
Assert.True(csv.HasRows);
Assert.Equal(0, csv.RowNumber);
Assert.Equal("Id", csv.GetName(0));
Assert.Equal("Name", csv.GetName(1));
Assert.Equal("Value", csv.GetName(2));
Assert.Equal("Date", csv.GetName(3));
Assert.True(csv.Read());
Assert.Equal(1, csv.RowNumber);
Assert.Equal("1", csv[0]);
Assert.Equal("John", csv[1]);
Assert.Equal("Low", csv[2]);
Assert.Equal("2000-11-11", csv[3]);
Assert.True(csv.Read());
Assert.Equal(2, csv.RowNumber);
Assert.Equal("2", csv[0]);
Assert.Equal("Jane", csv[1]);
Assert.Equal("High", csv[2]);
Assert.Equal("1989-03-14", csv[3]);
Assert.False(csv.Read());
}
}
public void Test1()
{
using var csvz = CsvZipPackage.Create("db.zip");
{
var entry = csvz.CreateEntry("states");
var data = CsvDataReader.Create(new StringReader("Code,Name\r\nOR,Oregon\r\nWA,Washington\r\nCA,California\r\nAK,Alaska\r\nHI,Hawaii"));
entry.WriteData(data);
entry = csvz.CreateEntry("postal");
data = CsvDataReader.Create(new StringReader("State,PostalCode\r\nOR,97123\r\nCA,90210\r\nOR97701"));
entry.WriteData(data);
}
var c = csvz.Entries.Count();
var e = csvz.FindEntry("states");
Assert.Equal("states", e.Name);
Assert.Equal(5, e.RowCount);
Assert.Equal(2, e.ColumnCount);
Assert.Equal(77, e.Length);
var postalEntry = csvz.FindEntry("postal");
var ps = postalEntry.GetColumnSchema();
Assert.NotNull(ps);
var dr = postalEntry.GetDataReader();
while (dr.Read())
{
var state = dr.GetString(0);
var zip = dr.GetString(1);
}
}
public async Task NextResultAsync()
{
using var tr = File.OpenText("Data\\Binary.csv");
var csv = CsvDataReader.Create(tr);
Assert.False(await csv.NextResultAsync());
Assert.False(await csv.ReadAsync());
}
public void NextResult()
{
using var tr = File.OpenText("Data\\Binary.csv");
var csv = CsvDataReader.Create(tr);
Assert.False(csv.NextResult());
Assert.False(csv.Read());
}
public void SylvanSchemaDeDupe()
{
var pool = new StringPool();
using var tr = TestData.GetTextReader();
using var dr = CsvDataReader.Create(tr, new CsvDataReaderOptions { Schema = TestData.TestDataSchema, StringFactory = pool.GetString });
ProcessData(dr);
}
public void AutoDetect1()
{
using var tr = new StringReader("A;B;C;D;E\n1;2;3;4;5\n");
var csv = CsvDataReader.Create(tr);
Assert.Equal(5, csv.FieldCount);
Assert.Equal("A", csv.GetName(0));
Assert.Equal("D", csv.GetName(3));
}
public void AutoDetect2()
{
using var tr = new StringReader("A|B,(b)|C|D|E\n1|2|3|4|5\n");
var csv = CsvDataReader.Create(tr);
Assert.Equal(5, csv.FieldCount);
Assert.Equal("A", csv.GetName(0));
Assert.Equal("D", csv.GetName(3));
}
public static DbDataReader GetTypedData()
{
var reader = File.OpenText("Data\\Schema.csv");
return(CsvDataReader.Create(reader, new CsvDataReaderOptions()
{
Schema = DataSchema.Instance
}));
}
public void CustomSchema()
{
using var sr = TestData.GetTextReader();
var schema = new TypedCsvSchema();
schema.Add("UID", typeof(int));
var csv = CsvDataReader.Create(sr, new CsvDataReaderOptions {
Schema = schema
});
}
public void MiscCoverage()
{
using var tr = new StringReader("Id,Name,Value");
var csv = CsvDataReader.Create(tr);
Assert.False(csv.HasRows);
Assert.Equal(0, csv.Depth);
Assert.False(csv.IsClosed);
Assert.Equal(-1, csv.RecordsAffected);
}
public void SqlTVPSample()
{
using var csvText = GetData(); // Gets a TextReader over a large-ish CSV dataset
var csb =
new SqlConnectionStringBuilder
{
DataSource = @"(LocalDb)\MSSqlLocalDb",
InitialCatalog = "Test",
IntegratedSecurity = true
};
using var conn = new SqlConnection(csb.ConnectionString);
conn.Open();
var cmd = conn.CreateCommand();
cmd.CommandText = "select top 0 * from Feature";
var reader = cmd.ExecuteReader();
var tableSchema = reader.GetColumnSchema();
reader.Close();
var csvSchema = new Schema(tableSchema);
var options = new CsvDataReaderOptions
{
Delimiter = '|',
Schema = csvSchema,
};
DbDataReader dataReader = CsvDataReader.Create(csvText, options);
cmd.CommandText = "begin tran";
cmd.ExecuteNonQuery();
cmd.CommandText = "InsertFeatures";
cmd.CommandType = CommandType.StoredProcedure;
var param = new SqlParameter()
{
ParameterName = "data",
SqlDbType = SqlDbType.Structured
};
dataReader = new BoundedDataReader(dataReader, 100);
var paramData = dataReader;
param.Value = paramData;
cmd.Parameters.Add(param);
cmd.ExecuteNonQuery();
cmd.CommandText = "commit tran";
cmd.CommandType = CommandType.Text;
cmd.Parameters.Clear();
cmd.ExecuteNonQuery();
}
public void Quoted2()
{
var reader = new StringReader("a,b,c\n1,\"\"\"2\"\", (two)\",3");
var csv = CsvDataReader.Create(reader);
Assert.Equal(3, csv.FieldCount);
csv.Read();
Assert.Equal("1", csv.GetString(0));
Assert.Equal("\"2\", (two)", csv.GetString(1));
Assert.Equal("3", csv.GetString(2));
}
//[Benchmark]
public void SylvanSelect()
{
using var tr = TestData.GetTextReader();
using var dr = CsvDataReader.Create(tr);
while (dr.Read())
{
var id = dr.GetInt32(0);
var name = dr.GetString(10);
var val = dr.GetInt32(20);
}
}
public void BufferTooSmall()
{
var opts = new CsvDataReaderOptions()
{
BufferSize = 128
};
using var tr = File.OpenText("Data\\Binary.csv");
var csv = CsvDataReader.Create(tr, opts);
csv.Read();
Assert.Throws <CsvRecordTooLargeException>(() => csv.Read());
}
public void DupeHeaderFix()
{
var data = "a,b,c,d,e,e";
var fixSchema = new ExcelHeaders();
var opts = new CsvDataReaderOptions {
Schema = fixSchema
};
var csv = CsvDataReader.Create(new StringReader(data), opts);
Assert.Equal(6, csv.FieldCount);
}
public void SylvanRead()
{
using var tr = GetData();
var options = new CsvDataReaderOptions {
Delimiter = '|'
};
using var dr = CsvDataReader.Create(tr);
while (dr.Read())
{
var id = dr.GetString(0);
}
}
public void Enumerator()
{
using (var reader = File.OpenText("Data\\Simple.csv"))
{
var csv = CsvDataReader.Create(reader);
int c = 0;
foreach (IDataRecord row in csv)
{
c++;
Assert.Same(row, csv);
}
Assert.Equal(2, c);
}
}
public void GetColumnSchema()
{
using (var reader = File.OpenText("Data\\Simple.csv"))
{
var csv = CsvDataReader.Create(reader);
var cols = csv.GetColumnSchema();
var names = new[] { "Id", "Name", "Value", "Date" };
for (int i = 0; i < cols.Count; i++)
{
Assert.Equal(names[i], cols[i].ColumnName);
Assert.Equal(typeof(string), cols[i].DataType);
}
}
}
public void MissingFieldTest()
{
using var tr = new StringReader("a,b,c\n1,2,3\n4,5\n6,7,8\n");
var csv = CsvDataReader.Create(tr);
Assert.True(csv.Read());
Assert.True(csv.Read());
Assert.Equal("4", csv.GetString(0));
Assert.Equal("5", csv.GetString(1));
Assert.Equal("", csv.GetString(2));
Assert.False(csv.IsDBNull(0));
Assert.False(csv.IsDBNull(1));
Assert.True(csv.IsDBNull(2));
Assert.Throws <ArgumentOutOfRangeException>(() => csv.GetString(-1));
Assert.Throws <ArgumentOutOfRangeException>(() => csv.GetString(3));
}
public void GetSchemaTable()
{
using (var reader = File.OpenText("Data\\Simple.csv"))
{
var csv = CsvDataReader.Create(reader);
var schema = csv.GetSchemaTable();
var names = new[] { "Id", "Name", "Value", "Date" };
for (int i = 0; i < schema.Rows.Count; i++)
{
var row = schema.Rows[i];
Assert.Equal(names[i], (string)row["ColumnName"]);
Assert.Equal(typeof(string), (Type)row["DataType"]);
}
}
}
public void Date1()
{
using var tr = new StringReader("Date\n20200803\n20200804\n20200805\n");
var opts = new CsvDataReaderOptions()
{
DateFormat = "yyyyMMdd"
};
var csv = CsvDataReader.Create(tr, opts);
Assert.True(csv.Read());
Assert.Equal(new DateTime(2020, 8, 3), csv.GetDateTime(0));
Assert.True(csv.Read());
Assert.Equal(new DateTime(2020, 8, 4), csv.GetDateTime(0));
Assert.True(csv.Read());
Assert.Equal(new DateTime(2020, 8, 5), csv.GetDateTime(0));
}
public void LineEndings()
{
using var tr = new StringReader("Id\r1\r\n2\n3\n\r");
var csv = CsvDataReader.Create(tr);
Assert.Equal("Id", csv.GetName(0));
Assert.True(csv.Read());
Assert.Equal("1", csv.GetString(0));
Assert.True(csv.Read());
Assert.Equal("2", csv.GetString(0));
Assert.True(csv.Read());
Assert.Equal("3", csv.GetString(0));
Assert.True(csv.Read());
Assert.Equal("", csv.GetString(0));
Assert.False(csv.Read());
}
public void Boolean3()
{
using var tr = new StringReader("Bool\nT\nF\nX\n");
var opts = new CsvDataReaderOptions()
{
TrueString = null,
FalseString = "f",
};
var csv = CsvDataReader.Create(tr, opts);
Assert.True(csv.Read());
Assert.True(csv.GetBoolean(0));
Assert.True(csv.Read());
Assert.False(csv.GetBoolean(0));
Assert.True(csv.Read());
Assert.True(csv.GetBoolean(0));
}
public void Boolean1()
{
using var tr = new StringReader("Bool\nT\nF\nX\n");
var opts = new CsvDataReaderOptions()
{
TrueString = "t",
FalseString = "f",
};
var csv = CsvDataReader.Create(tr, opts);
Assert.True(csv.Read());
Assert.True(csv.GetBoolean(0));
Assert.True(csv.Read());
Assert.False(csv.GetBoolean(0));
Assert.True(csv.Read());
Assert.Throws <FormatException>(() => csv.GetBoolean(0));
}
public void TextReader()
{
using var tr = File.OpenText("Data\\Binary.csv");
var csv = CsvDataReader.Create(tr);
var buf = new char[32];
while (csv.Read())
{
var idx = 0;
int len;
while ((len = (int)csv.GetChars(1, idx, buf, 0, buf.Length)) != 0)
{
idx += len;
}
Assert.True(idx > 0);
}
}
public void SqlBulkLoadSample()
{
using var csvText = GetData(); // Gets a TextReader over a large-ish CSV dataset
var csb =
new SqlConnectionStringBuilder
{
DataSource = @"(LocalDb)\MSSqlLocalDb",
InitialCatalog = "Test",
IntegratedSecurity = true
};
using var conn = new SqlConnection(csb.ConnectionString);
conn.Open();
var cmd = conn.CreateCommand();
cmd.CommandText = "select top 0 * from Feature";
var reader = cmd.ExecuteReader();
var tableSchema = reader.GetColumnSchema();
reader.Close();
var csvSchema = new Schema(tableSchema);
var options = new CsvDataReaderOptions
{
Delimiter = '|',
Schema = csvSchema,
};
DbDataReader dataReader = CsvDataReader.Create(csvText, options);
//dataReader = new EmptyAsNullDataReader(dataReader);
var bcp = new SqlBulkCopy(conn);
bcp.BulkCopyTimeout = 0;
bcp.DestinationTableName = "Feature";
bcp.BatchSize = 50000;
bcp.WriteToServer(dataReader);
}
public void SylvanDataTable()
{
var schema = new TypedCsvSchema();
schema.Add(0, typeof(int));
schema.Add(1, typeof(string));
schema.Add(2, typeof(double?));
schema.Add(3, typeof(DateTime));
schema.Add(4, typeof(DateTime?));
var options = new CsvDataReaderOptions
{
Schema = schema
};
using var tr = GetData();
using var dr = CsvDataReader.Create(tr, options);
var dt = new DataTable();
dt.Load(dr);
}
public void Broken()
{
using (var reader = File.OpenText("Data\\Broken.csv"))
{
var csv = CsvDataReader.Create(reader);
Assert.Equal(2, csv.FieldCount);
Assert.True(csv.HasRows);
Assert.Equal(0, csv.RowNumber);
Assert.Equal("A", csv.GetName(0));
Assert.Equal("B", csv.GetName(1));
Assert.True(csv.Read());
Assert.Equal(1, csv.RowNumber);
Assert.Equal("ab", csv[0]);
Assert.Equal("c", csv[1]);
Assert.True(csv.Read());
Assert.Equal(2, csv.RowNumber);
Assert.Equal("d\"e\"f", csv[0]);
Assert.Equal("gh\"i", csv[1]);
Assert.False(csv.Read());
}
}
