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()); } }