public static void SylvanWithDapper()
{
var csvData = "Id,Name,Value,CreateDate,ModifiedDate\n1,Hello,1.1,2020-12-15,\n2,World,,2020-12-14,2020-12-15\n";
var schema = new TypedCsvSchema()
.Add(typeof(int))
.Add(typeof(string))
.Add(typeof(double), true)
.Add(typeof(DateTime))
.Add(typeof(DateTime), true);
var options = new CsvDataReaderOptions
{
Schema = schema
};
var reader = new StringReader(csvData);
//var reader = File.OpenText("mydata.csv");
using var csv = CsvDataReader.Create(reader, options);
foreach (MyDataRecord record in csv.GetRecordsDapper <MyDataRecord>())
{
Console.WriteLine($"{record.Name} {record.Value}");
}
}
static void WriteCsv(CsvZipPackage csvz, string csv)
{
Console.WriteLine($"Processing {Path.GetFileName(csv)}: ");
var data = CsvDataReader.Create(csv);
var analyzer = new SchemaAnalyzer();
Console.Write($" Analyzing. ");
var sw = Stopwatch.StartNew();
var result = analyzer.Analyze(data);
Console.WriteLine(sw.Elapsed.ToString());
var schema = result.GetSchema();
var csvSchema = new CsvSchema(schema);
var csvOpts = new CsvDataReaderOptions {
Schema = csvSchema
};
Console.Write($" Writing. ");
data = CsvDataReader.Create(csv, csvOpts);
sw = Stopwatch.StartNew();
var entry = csvz.CreateEntry(Path.GetFileName(csv));
entry.WriteData(data);
Console.WriteLine(sw.Elapsed.ToString());
}
public override int Execute(CommandContext context, AnalyzeSettings settings)
{
var filename = settings.File;
var output = settings.Schema ?? filename + ".schema";
Stream oStream =
output == "."
? Console.OpenStandardOutput()
: File.Create(output);
var tr = new StreamReader(filename);
for (int i = 0; i < settings.Skip; i++)
{
tr.ReadLine();
}
var csv = CsvDataReader.Create(tr);
var a = new SchemaAnalyzer(new SchemaAnalyzerOptions {
AnalyzeRowCount = settings.Lines
});
var re = a.Analyze(csv);
var sb = re.GetSchemaBuilder();
foreach (var col in sb)
{
// TODO: think more about how to handle columns size
col.ColumnSize = null;
}
var schema = sb.Build();
using var tw = new StreamWriter(oStream);
tw.Write(schema.ToString().Replace(",", "," + Environment.NewLine));
return(0);
}
public List <T> GetRecords <T>(MemoryStream stream) where T : ICsvReadable, new()
{
var activate = ActivatorFactory.Create <T>(_activationMethod);
var allRecords = new List <T>();
// 64 should fully cover the values in the dataset.
var stringPool = new StringPool(64);
using (var reader = new StreamReader(stream))
{
var options = new CsvDataReaderOptions
{
HasHeaders = false,
BufferSize = 0x10000,
StringFactory = stringPool.GetString,
};
var csvReader = CsvDataReader.Create(reader, options);
while (csvReader.Read())
{
var record = activate();
record.Read(i => csvReader.GetString(i));
allRecords.Add(record);
}
}
return(allRecords);
}
public static DbDataReader GetTypedData()
{
var reader = File.OpenText("Data\\Schema.csv");
return(CsvDataReader.Create(reader, new CsvDataReaderOptions()
{
Schema = DataSchema.Instance
}));
}
public static DbDataReader GetDataWithSchema(Action <CsvDataReaderOptions> opts = null)
{
var options = new CsvDataReaderOptions {
Schema = Schema
};
opts?.Invoke(options);
return(CsvDataReader.Create(GetTextReader(), options));
}
public void SylvanSelect()
{
using var tr = TestData.GetTextReader();
using var dr = CsvDataReader.Create(tr);
while (dr.Read())
{
var type = dr.GetString(2);
var orderDate = dr.GetDateTime(5);
var id = dr.GetInt32(6);
var profit = dr.GetDecimal(13);
}
}
private void LoadEvidenceCsv()
{
this.loggerWrapper.Info("Reading CDC1 evidence data...");
using (var csv = CsvDataReader.Create("duplicates_base.csv", new CsvDataReaderOptions {
HasHeaders = false
}))
{
while (csv.Read())
{
var exists = this._cdc1Evidence.ContainsKey(csv.GetString(0));
if (!exists)
{
this._cdc1Evidence.Add(csv.GetString(0).ToLower(), csv.GetString(1));
}
else
{
this.loggerWrapper.Warning($"Row with key {csv.GetString(0)}, value {csv.GetString(1)} could not be added, key already exists");
}
}
}
this.loggerWrapper.Info($"CDC1 evidence data loaded ({this._cdc1Evidence.Count} rows)");
}
public override int Execute(
CommandContext context,
SelectSettings settings
)
{
var filename = settings.File;
Stream iStream = filename == "."
? Console.OpenStandardInput()
: File.OpenRead(settings.File);
var tr = new StreamReader(iStream);
for (int i = 0; i < settings.Skip; i++)
{
tr.ReadLine();
}
var opts =
new CsvDataReaderOptions
{
BufferSize = 0x100000,
};
var csv = CsvDataReader.Create(tr, opts);
var data = csv.Select(settings.Columns);
var oStream =
settings.Output == "."
? Console.OpenStandardOutput()
: File.Create(settings.Output);
var tw = new StreamWriter(oStream);
var ww = CsvDataWriter.Create(tw);
ww.Write(data);
return(0);
}
public FastJavaCsvBenchmark()
{
reader = CsvDataReader.Create(new InfiniteDataReader(Record));
writer = new CsvWriter(TextWriter.Null);
}
public static DbDataReader GetData()
{
return(CsvDataReader.Create(GetTextReader()));
}
public void Sylvan()
{
using var tr = TestData.GetTextReader();
using var dr = CsvDataReader.Create(tr);
dr.ProcessStrings();
}
private string ProcessFile(string sourceFile, ref long globalRowCounter, ref int fileCounter)
{
string msg;
lock (SyncRoot)
{
Interlocked.Increment(ref fileCounter);
msg = $"{Path.GetFileName(sourceFile)} ({fileCounter} of {Analysis.Count()})";
}
var csv = CsvDataReader.Create(sourceFile, new CsvDataReaderOptions
{
//StringFactory = new StringPool().GetString,
HasHeaders = true
});
using (var writer = CreateStreamWriter(sourceFile))
{
WriteNewHeader(sourceFile, writer);
long localRowCounter = 0;
long currentRoundedEpoch = 0;
while (csv.Read())
{
var timestamp = csv.GetDateTime(Settings.TimestampColumnName);
var epoch = timestamp.ToEpoch();
var roundedEpoch = Util.RoundToHourEpoch(timestamp);
if (currentRoundedEpoch != roundedEpoch)
{
currentRoundedEpoch = roundedEpoch;
}
WeatherHist match = null;
if (!Settings.UseDirectSQL)
{
LoadEpoch(currentRoundedEpoch);
}
var lat = csv.GetFloat(Settings.LatitudeColumnName);
var lon = csv.GetFloat(Settings.LongitudeColumnName);
var point = new Point(lon, lat);
var hash = GeoHasher.Encode(point, Settings.GeohashMatchPrecision);
if (IsWithinEpochRange(currentRoundedEpoch) &&
!IsDeadDog(currentRoundedEpoch, hash))
{
var matches = EpochManager.Lookup(currentRoundedEpoch, Redirect(hash), false, Settings.UseDirectSQL);
if (matches.Count() == 0)
{
DeepSearch(currentRoundedEpoch, hash, matches);
}
if (matches.Count() > 0)
{
match = SelectBestMatch(point, matches);
MapRedirect(hash, GeoHasher.Reduce(match.Geohash, Settings.GeohashMatchPrecision));
}
}
writer.WriteLine(AppendWeatherData(csv, match));
Interlocked.Increment(ref globalRowCounter);
localRowCounter++;
}
writer.Flush();
writer.Close();
if (localRowCounter == 0)
{
// Nothing was written, remove the empty file
File.Delete(GetOutputFileName(sourceFile));
}
}
Log.Info("Completed " + msg);
return(msg);
}
public override int Execute(
CommandContext context,
ImportSettings settings
)
{
double last = 0.0;
double prog = 0.0;
ProgressTask progress = null;
var mre = new ManualResetEvent(false);
void UpdateProgress(double p)
{
prog = p * 100d;
mre.Set();
}
var task = Task.Run(() =>
{
CsvDataReader csv = null;
var database = settings.Database;
var filename = settings.File;
var loader = GetLoader(settings.Provider);
var tableName = settings.Table ?? Path.GetFileNameWithoutExtension(filename);
Stream s = File.OpenRead(settings.File);
s = s.WithReadProgress(UpdateProgress, 0.001);
var tr = new StreamReader(s);
for (int i = 0; i < settings.Skip; i++)
{
tr.ReadLine();
}
string schemaSpec = null;
if (settings.Schema != null)
{
var schemaFile = settings.Schema;
schemaSpec = File.ReadAllText(schemaFile);
}
else
{
var schemaFile = filename + ".schema";
if (File.Exists(schemaFile))
{
schemaSpec = File.ReadAllText(schemaFile);
}
}
var explicitSchema = schemaSpec == null ? null : Schema.Parse(schemaSpec);
var schema = explicitSchema == null ? CsvSchema.Nullable : new CsvSchema(explicitSchema);
var opts =
new CsvDataReaderOptions
{
BufferSize = 0x100000,
Schema = schema,
};
csv = CsvDataReader.Create(tr, opts);
loader.Load(csv, tableName, database);
mre.Set();
});
// ensures that the progress loop finishes.
task.ContinueWith(t => mre.Set());
AnsiConsole.Progress()
.Columns(new ProgressColumn[] {
new TaskDescriptionColumn(), // Task description
new ProgressBarColumn(), // Progress bar
new PercentageColumn(), // Percentage
new RemainingTimeColumn(), // Remaining time
new SpinnerColumn(),
}
)
.Start(ctx =>
{
progress = ctx.AddTask("Import");
while (!task.IsCompleted)
{
mre.WaitOne();
var inc = prog - last;
last = prog;
progress.Increment(inc);
mre.Reset();
}
if (task.IsFaulted)
{
throw task.Exception;
}
else
{
// make sure it arrives at 100%
if (last < 100d)
{
progress.Increment(100d - last);
}
}
});
return(0);
}
