public void CsvTableReader_Parsing()
{
CsvTableReader reader;
string table =
@"Col1,Col2,Col3
10,true,25.20
no,10";
reader = new CsvTableReader(new CsvReader(table));
Assert.AreEqual(3, reader.ColumnMap.Count);
Assert.AreEqual(0, reader.ColumnMap["Col1"]);
Assert.AreEqual(1, reader.ColumnMap["Col2"]);
Assert.AreEqual(2, reader.ColumnMap["Col3"]);
Assert.AreEqual(3, reader.Columns.Count);
Assert.AreEqual("Col1", reader.Columns[0]);
Assert.AreEqual("Col2", reader.Columns[1]);
Assert.AreEqual("Col3", reader.Columns[2]);
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());
}
public void CsvTableReader_EmptyTable()
{
CsvTableReader reader;
string table = "";
reader = new CsvTableReader(new CsvReader(table));
Assert.Empty(reader.ColumnMap);
Assert.Null(reader.ReadRow());
Assert.Null(reader.ReadRow());
}
public void CsvTableReader_EmptyTable()
{
CsvTableReader reader;
string table = "";
reader = new CsvTableReader(new CsvReader(table));
Assert.AreEqual(0, reader.ColumnMap.Count);
Assert.IsNull(reader.ReadRow());
Assert.IsNull(reader.ReadRow());
}
public void CsvTableReader_NoRows()
{
CsvTableReader reader;
string table = "Col1,Col2,Col3";
reader = new CsvTableReader(new CsvReader(table));
Assert.Equal(3, reader.ColumnMap.Count);
Assert.Equal(0, reader.ColumnMap["Col1"]);
Assert.Equal(1, reader.ColumnMap["Col2"]);
Assert.Equal(2, reader.ColumnMap["Col3"]);
Assert.Null(reader.ReadRow());
Assert.Null(reader.ReadRow());
}
public void CsvTableWriter_BlankRow()
{
string path = Path.GetTempFileName();
try
{
using (var writer = new CsvTableWriter(new string[] { "Col0", "Col1", "Col2" }, new FileStream(path, FileMode.Create), Encoding.UTF8))
{
writer.Set("Col0", "(0,0)");
writer.Set("Col1", "(1,0)");
writer.Set("Col2", "(2,0)");
writer.WriteRow();
writer.WriteRow();
writer.Set("Col0", "(0,2)");
writer.Set("Col1", "(1,2)");
writer.Set("Col2", "(2,2)");
writer.WriteRow();
}
using (var reader = new CsvTableReader(new FileStream(path, FileMode.Open), Encoding.UTF8))
{
Assert.True(reader.ColumnMap.ContainsKey("Col0"));
Assert.True(reader.ColumnMap.ContainsKey("Col1"));
Assert.True(reader.ColumnMap.ContainsKey("Col2"));
Assert.NotNull(reader.ReadRow());
Assert.Equal("(0,0)", reader["Col0"]);
Assert.Equal("(1,0)", reader["Col1"]);
Assert.Equal("(2,0)", reader["Col2"]);
Assert.NotNull(reader.ReadRow());
Assert.Equal("", reader["Col0"]);
Assert.Equal("", reader["Col1"]);
Assert.Equal("", reader["Col2"]);
Assert.NotNull(reader.ReadRow());
Assert.Equal("(0,2)", reader["Col0"]);
Assert.Equal("(1,2)", reader["Col1"]);
Assert.Equal("(2,2)", reader["Col2"]);
Assert.Null(reader.ReadRow());
}
}
finally
{
File.Delete(path);
}
}
public void CsvTableWriter_Basic()
{
string path = Path.GetTempFileName();
try
{
using (var writer = new CsvTableWriter(new string[] { "Col0", "Col1", "Col2" }, new FileStream(path, FileMode.Create), Encoding.UTF8))
{
Assert.AreEqual(0, writer.GetColumnIndex("Col0"));
Assert.AreEqual(1, writer.GetColumnIndex("Col1"));
Assert.AreEqual(2, writer.GetColumnIndex("Col2"));
Assert.AreEqual(-1, writer.GetColumnIndex("Col3"));
writer.Set("Col0", "(0,0)");
writer.Set("Col1", "(1,0)");
writer.Set("Col2", "(2,0)");
writer.WriteRow();
writer.Set("Col0", "(0,1)");
writer.Set("Col1", "(1,1)");
writer.Set("Col2", "(2,1)");
writer.WriteRow();
}
using (var reader = new CsvTableReader(new FileStream(path, FileMode.Open), Encoding.UTF8))
{
Assert.IsTrue(reader.ColumnMap.ContainsKey("Col0"));
Assert.IsTrue(reader.ColumnMap.ContainsKey("Col1"));
Assert.IsTrue(reader.ColumnMap.ContainsKey("Col2"));
Assert.IsNotNull(reader.ReadRow());
Assert.AreEqual("(0,0)", reader.Parse("Col0", (string)null));
Assert.AreEqual("(1,0)", reader.Parse("Col1", (string)null));
Assert.AreEqual("(2,0)", reader.Parse("Col2", (string)null));
Assert.IsNotNull(reader.ReadRow());
Assert.AreEqual("(0,1)", reader.Parse("Col0", (string)null));
Assert.AreEqual("(1,1)", reader.Parse("Col1", (string)null));
Assert.AreEqual("(2,1)", reader.Parse("Col2", (string)null));
Assert.IsNull(reader.ReadRow());
}
}
finally
{
Helper.DeleteFile(path);
}
}
public void CsvTableReader_RowEnumeration()
{
CsvTableReader reader;
string table =
@"Col1,Col2,Col2
1,10
2,20
3,30
4,40
";
reader = new CsvTableReader(new CsvReader(table));
Assert.Equal(2, reader.ColumnMap.Count);
var count = 0;
foreach (var row in reader.Rows())
{
count++;
Assert.Equal(count, int.Parse(row[0]));
Assert.Equal(count * 10, int.Parse(row[1]));
}
Assert.Null(reader.ReadRow());
Assert.Equal(4, count);
}
public void CsvTableReader_Parsing()
{
CsvTableReader reader;
string table =
@"Col1,Col2,Col3
10,true,25.20
no,10
""Hello """"World""""!"",BAR";
reader = new CsvTableReader(new CsvReader(table));
Assert.Equal(3, reader.ColumnMap.Count);
Assert.Equal(0, reader.ColumnMap["Col1"]);
Assert.Equal(1, reader.ColumnMap["Col2"]);
Assert.Equal(2, reader.ColumnMap["Col3"]);
Assert.Equal(3, reader.Columns.Count);
Assert.Equal("Col1", reader.Columns[0]);
Assert.Equal("Col2", reader.Columns[1]);
Assert.Equal("Col3", reader.Columns[2]);
Assert.NotNull(reader.ReadRow());
Assert.Equal("10", reader.GetColumn("Col1"));
Assert.Equal("true", reader.GetColumn("Col2"));
Assert.Equal("25.20", reader.GetColumn("Col3"));
Assert.Equal("10", reader["Col1"]);
Assert.Equal("true", reader["Col2"]);
Assert.Equal("25.20", reader["Col3"]);
Assert.Null(reader["not-there"]);
Assert.Equal("10", reader[0]);
Assert.Equal("true", reader[1]);
Assert.Equal("25.20", reader[2]);
Assert.Null(reader[100]);
Assert.NotNull(reader.ReadRow());
Assert.Equal("no", reader["Col1"]);
Assert.Equal("10", reader["Col2"]);
Assert.NotNull(reader.ReadRow());
Assert.Equal(@"Hello ""World""!", reader["Col1"]);
Assert.Equal("BAR", reader["Col2"]);
Assert.Null(reader.ReadRow());
Assert.Null(reader.ReadRow());
}
public void CsvTableWriter_Basic()
{
using (var tempFolder = new TempFolder())
{
string path = Path.Combine(tempFolder.Path, "test.csv");
using (var writer = new CsvTableWriter(new string[] { "Col0", "Col1", "Col2" }, new FileStream(path, FileMode.Create), Encoding.UTF8))
{
Assert.Equal(0, writer.GetColumnIndex("Col0"));
Assert.Equal(1, writer.GetColumnIndex("Col1"));
Assert.Equal(2, writer.GetColumnIndex("Col2"));
Assert.Equal(-1, writer.GetColumnIndex("Col3"));
writer.Set("Col0", "(0,0)");
writer.Set("Col1", "(1,0)");
writer.Set("Col2", "(2,0)");
writer.WriteRow();
writer.Set("Col0", "(0,1)");
writer.Set("Col1", "(1,1)");
writer.Set("Col2", "(2,1)");
writer.WriteRow();
}
using (var reader = new CsvTableReader(new FileStream(path, FileMode.Open), Encoding.UTF8))
{
Assert.True(reader.ColumnMap.ContainsKey("Col0"));
Assert.True(reader.ColumnMap.ContainsKey("Col1"));
Assert.True(reader.ColumnMap.ContainsKey("Col2"));
Assert.NotNull(reader.ReadRow());
Assert.Equal("(0,0)", reader["Col0"]);
Assert.Equal("(1,0)", reader["Col1"]);
Assert.Equal("(2,0)", reader["Col2"]);
Assert.NotNull(reader.ReadRow());
Assert.Equal("(0,1)", reader["Col0"]);
Assert.Equal("(1,1)", reader["Col1"]);
Assert.Equal("(2,1)", reader["Col2"]);
Assert.Null(reader.ReadRow());
}
}
}
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());
}
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());
}
public void CsvTableReader_MissingColumns()
{
CsvTableReader reader;
string table =
@"Col1,Col2,Col2
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(100, reader.Parse("ColX", 100));
}
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);
}
