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 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 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 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 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 async Task SylvanDeDupeReuse() { using var tr = TestData.GetTextReader(); var opts = new CsvDataReaderOptions { StringFactory = pool }; using var dr = await CsvDataReader.CreateAsync(tr, opts); while (await dr.ReadAsync()) { for (int i = 0; i < dr.FieldCount; i++) { var s = dr.GetString(i); } } }
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 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 async Task BinaryValues() { using (var reader = File.OpenText("Data\\Binary.csv")) { var schema = new TypedCsvSchema(); schema.Add(1, typeof(byte[])); var opts = new CsvDataReaderOptions() { Schema = schema }; var csv = await CsvDataReader.CreateAsync(reader, opts); csv.Read(); var vals = new object[2]; csv.GetValues(vals); var expected = Encoding.ASCII.GetBytes(BinaryValue1); Assert.Equal(expected, (byte[])vals[1]); csv.Read(); csv.GetValues(vals); expected = Encoding.ASCII.GetBytes(BinaryValue2); Assert.Equal(expected, (byte[])vals[1]); } }
public async Task NoHeadersWithSchema() { var schema = new ExcelHeaders(); var opts = new CsvDataReaderOptions { HasHeaders = false, Schema = schema }; using (var reader = File.OpenText("Data\\DataOnly.csv")) { var csv = await CsvDataReader.CreateAsync(reader, opts); Assert.Equal(4, csv.FieldCount); Assert.True(csv.HasRows); Assert.Equal(0, csv.RowNumber); Assert.Equal("C", csv.GetName(2)); Assert.Equal(3, csv.GetOrdinal("D")); Assert.Equal("A", csv.GetName(0)); Assert.Throws <IndexOutOfRangeException>(() => csv.GetOrdinal("Id")); Assert.True(await csv.ReadAsync()); 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(await csv.ReadAsync()); 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(await csv.ReadAsync()); } }