/// <summary>
/// Импортирует массив данныех из CSV файла
/// </summary>
/// <param name="path">Путь к файлу</param>
/// <returns>Успех выполнения операции</returns>
public bool Import(String path)
{
try
{
log.Info("Import CSV file...");
var timer = new Stopwatch();
timer.Start();
dataTable = new DataTable();
using (var streamReader = new StreamReader(path))
using (var reader = new CsvReader(streamReader))
{
reader.ValueSeparator = ';';
reader.ReadHeaderRecord();
dataTable.Fill(reader);
}
timer.Stop();
log.Info(String.Format("Import complete! Csv file contains {0} rows. Elapsed time: {1} ms",
dataTable.Rows.Count, timer.Elapsed.Milliseconds));
return true;
}
catch (Exception ex)
{
log.Error("Can't read file!", ex);
return false;
}
}
public static void WriteTable(TableFileBase tableFile)
{
string path = "./Assets/Resources/Tables/" + tableFile.GetType().Name + ".csv";
string[] lines = File.ReadAllLines(path, Encoding.Default);
lines[0] = lines[0].Replace("\r\n", "\n");
StringReader rdr = new StringReader(string.Join("\n", lines));
List<string> titles = new List<string>();
using (var reader = new CsvReader(rdr))
{
HeaderRecord header = reader.ReadHeaderRecord();
for (int i = 0; i < header.Count; ++i)
{
titles.Add(header[i]);
}
}
File.Delete(path);
using (StreamWriter SourceStream = new StreamWriter(path, false, System.Text.Encoding.Default))
{
CsvWriter writer = new CsvWriter(SourceStream);
writer.WriteRecord(titles.ToArray());
foreach (var record in tableFile.Records)
{
writer.WriteRecord(record.Value.GetRecordStr());
}
}
}
public ImportImpl(StreamReader reader, string file, TaskModel taskModel, Action<string> output, IAsyncDatabaseCommands databaseCommands)
{
this.taskModel = taskModel;
this.output = output;
this.databaseCommands = databaseCommands;
csvReader = new CsvReader(reader);
header = csvReader.ReadHeaderRecord();
entity = Path.GetFileNameWithoutExtension(file);
sw = Stopwatch.StartNew();
enumerator = csvReader.DataRecords.GetEnumerator();
}
public ImportImpl(StreamReader reader, string file, TaskModel taskModel, Action<string> output, IAsyncDatabaseCommands databaseCommands)
{
this.taskModel = taskModel;
this.output = output;
this.databaseCommands = databaseCommands;
csvReader = new CsvReader(reader);
header = csvReader.ReadHeaderRecord();
entity = Inflector.Pluralize(CSharpClassName.ConvertToValidClassName(Path.GetFileNameWithoutExtension(file)));
if (entity.Length > 0 && char.IsLower(entity[0]))
entity = char.ToUpper(entity[0]) + entity.Substring(1);
sw = Stopwatch.StartNew();
enumerator = csvReader.DataRecords.GetEnumerator();
}
//Lat.Long.DataPoints.2
//lat long eventdate street vomit blood urine humanfouling dogfouling graffiti
public IEnumerable<AnitSocialV1> GetAntiSocialDataV1()
{
var sourceFile = @"C:\ldn\Lat.Long.DataPoints.2.csv";
var reader = new Kent.Boogaart.KBCsv.CsvReader(File.OpenRead(sourceFile));
reader.ReadHeaderRecord();
DataRecord row;
AnitSocialV1 antiSocial;
while ((row = reader.ReadDataRecord()) != null)
{
if (row.Count != 10) continue;
antiSocial = null;
try
{
//lat long eventdate street vomit blood urine humanfouling dogfouling graffiti
antiSocial = new AnitSocialV1
{
EventDate = DateTime.Parse(row["eventdate"]),
StreetName = row["street"],
Location = new Location
{
Latitude = double.Parse(row["lat"]),
Longitude = double.Parse(row["long"]),
},
Vomit = row["vomit"] == "0" ? false : true,
Blood = row["blood"] == "0" ? false : true,
Urine = row["urine"] == "0" ? false : true,
HumanFouling = row["humanfouling"] == "0" ? false : true,
DogFouling = row["dogfouling"] == "0" ? false : true,
Graffiti = row["graffiti"] == "0" ? false : true,
};
}
catch
{
continue;
}
if (antiSocial != null)
yield return antiSocial;
}
}
private void ParserTableStr(string content)
{
StringReader rdr = new StringReader(content);
using (var reader = new CsvReader(rdr))
{
HeaderRecord header = reader.ReadHeaderRecord();
while (reader.HasMoreRecords)
{
DataRecord data = reader.ReadDataRecord();
if (data[0].StartsWith("#"))
continue;
SkillInfoTableRecord record = new SkillInfoTableRecord(data);
Records.Add(record.Id, record);
}
}
}
/// <summary>
/// Преобразует CSV файл в список.
/// </summary>
/// <param name="filename">Имя файла</param>
/// <returns>Список строк</returns>
public static List<string[]> CSVtoList(string filename)
{
List<string[]> list = new List<string[]>();
using (var reader = new CsvReader(filename))
{
reader.ValueSeparator = Char.Parse(";");
foreach (string[] record in reader.DataRecordsAsStrings)
{
list.Add(record);
}
reader.Close();
}
return list;
}
public IEnumerable<AnitSocial> GetAntiSocialData()
{
var sourceFile = @"C:\ldn\antisocial.csv";
var reader = new Kent.Boogaart.KBCsv.CsvReader(File.OpenRead(sourceFile));
reader.ReadHeaderRecord();
DataRecord row;
AnitSocial antiSocial;
while ((row = reader.ReadDataRecord()) != null)
{
if (row.Count != 10) continue;
antiSocial = null;
try
{
antiSocial = new AnitSocial
{
EventDate = DateTime.Parse(row["eventdate"]),
StreetName = row["street"],
Location = new Location
{
Latitude = double.Parse(row["lat"]),
Longitude = double.Parse(row["long"]),
X = double.Parse(row["x"]),
Y = double.Parse(row["y"])
},
Vomit = row["vomit"] == "NO" ? false : true,
Blood = row["blood"] == "NO" ? false : true,
Urine = row["urine"] == "NO" ? false : true,
HumanFouling = row["humanfouling"] == "NO" ? false : true,
};
}
catch
{
continue;
}
if(antiSocial != null)
yield return antiSocial;
}
}
public Problem Read(string csvFilePath)
{
var nodes = new List<NodeInfo>();
var p = new Problem();
using (var reader = new CsvReader(csvFilePath))
{
reader.ReadHeaderRecord();
foreach (var record in reader.DataRecords)
{
nodes.Add(new NodeInfo
{
Id = int.Parse(record["Id"]),
X = float.Parse(record["X"], CultureInfo.InvariantCulture),
Y = float.Parse(record["Y"], CultureInfo.InvariantCulture),
Demand = float.Parse(record["Demand"], CultureInfo.InvariantCulture),
ReadyTime = float.Parse(record["ReadyTime"], CultureInfo.InvariantCulture),
DueDate = float.Parse(record["DueDate"], CultureInfo.InvariantCulture),
ServiceTime = float.Parse(record["ServiceTime"], CultureInfo.InvariantCulture)
});
}
}
p.SetNodes(nodes);
return p;
}
public Dictionary<string, string> CreateUsersFromCSV(string csvPath)
{
var users = new List<User>();
var passwords = new Dictionary<string, string>();
var db = GetDbContext();
using (var reader = new CsvReader(csvPath))
{
reader.ReadHeaderRecord();
foreach (var record in reader.DataRecords)
{
var role = (int) Enum.Parse(typeof (Role), record.GetValueOrNull("Role") ?? "Student");
var password = record.GetValueOrNull("Password") ?? RandomPassword();
var user = new User
{
Username = record.GetValueOrNull("Username"),
Password = EncryptPassword(password),
Email = record.GetValueOrNull("Email"),
Name = record.GetValueOrNull("Name") ?? string.Empty,
OpenId = record.GetValueOrNull("OpenId") ?? string.Empty,
Deleted = false,
IsApproved = true,
ApprovedBy = GetCurrentUser().Id,
CreationDate = DateTime.Now,
};
user.UserRoles.Add(new UserRole {RoleRef = role});
users.Add(user);
passwords.Add(user.Username, password);
}
}
db.Users.InsertAllOnSubmit(users);
db.SubmitChanges();
foreach (var user in users)
{
SendEmail("admin@iudico", user.Email, "Iudico Notification", "Your account has been created:\nUsername: "******"\nPassword: " + passwords[user.Username]);
}
_LmsService.Inform(UserNotifications.UserCreateMultiple, users);
return passwords;
}
void reportBackgroundWorker_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
{
DataTable dt = new DataTable();
// Kent Boogaart developed an awesome CSV library available on Codeplex that we'll use to
// parse the CSV data so we can turn it into a DataTable to display in a DataGrid
CsvReader csvReader = new CsvReader(new StringReader(reportData));
HeaderRecord headerRecord = csvReader.ReadHeaderRecord();
foreach (string col in headerRecord.Values)
{
dt.Columns.Add(col);
}
foreach (DataRecord dataRecord in csvReader.DataRecords)
{
DataRow dataRow = dt.NewRow();
foreach (string col in dataRecord.HeaderRecord.Values)
{
dataRow[col] = dataRecord[col];
}
dt.Rows.Add(dataRow);
}
dgReportData.ItemsSource = dt.DefaultView;
pbStatus.Value = 0;
}
private void ProcessFile(SPListItem item)
{
DataTable dtErrors = new DataTable();
string uploadedFileName = item.File.Name;
string logFileName = "Error_Log_" + item.ID.ToString() + "_" + uploadedFileName;
if (uploadedFileName.ToLower().EndsWith("csv"))
{
try
{
using (CsvReader csvReader = new CsvReader(item.File.OpenBinaryStream()))
{
try
{
item["Status"] = "In Progress";
item.Update();
csvReader.ReadHeaderRecord();
DataSet dsBulkUpload = new DataSet();
int ret = DataExtensions.Fill(dsBulkUpload, csvReader, "BulkUpload");
if (ret > 0)
{
dtErrors = this.ProcessData(dsBulkUpload, (UploadTypes)Enum.Parse(typeof(UploadTypes), item["LoadType"].ToString(), true));
}
string fileContent = string.Empty;
string columnContent = string.Empty;
for (int i = 0; i < dtErrors.Columns.Count; i++)
{
fileContent += (fileContent != string.Empty ? "," : string.Empty) + dtErrors.Columns[i].ColumnName;
}
for (int row = 0; row < dtErrors.Rows.Count; row++)
{
for (int column = 0; column < dtErrors.Columns.Count; column++)
{
columnContent += (columnContent != string.Empty ? "," : string.Empty) + dtErrors.Rows[row][column].ToString();
}
fileContent += "\n" + columnContent;
columnContent = string.Empty;
}
SPFile file = fldErrorLogs.Files.Add(logFileName, ASCIIEncoding.ASCII.GetBytes ( fileContent));
file.Update();
SPFieldUrlValue urlValue = new SPFieldUrlValue();
urlValue.Description = logFileName;
urlValue.Url = file.ServerRelativeUrl ;
item["Status"] = "Completed";
item["Url"] = urlValue.ToString();
item.Update();
}
catch (Exception ex)
{
}
}
}
catch (Exception ex)
{
}
}
else
{
}
}
void item_Click(object sender, EventArgs e)
{
ToolStripMenuItem item = (ToolStripMenuItem)sender;
string fileName = string.Empty;
Action action = (Action)item.OwnerItem.Tag;
Output output = (Output)item.Tag;
try
{
string connectionString = Helper.FixConnectionString(this.Parent.Connection.ConnectionString, this.Parent.Connection.ConnectionTimeout);
using (IRepository repository = new DBRepository(connectionString))
{
var generator = new Generator(repository);
using (ImportOptions imo = new ImportOptions(this.Parent.Name))
{
imo.SampleHeader = generator.GenerateTableColumns(this.Parent.Name);
imo.Separator = System.Globalization.CultureInfo.CurrentCulture.TextInfo.ListSeparator.ToCharArray()[0];
if (imo.ShowDialog() == DialogResult.OK)
{
switch (output)
{
case Output.Editor:
// create new document
ServiceCache.ScriptFactory.CreateNewBlankScript(Microsoft.SqlServer.Management.UI.VSIntegration.Editors.ScriptType.SqlCe);
break;
case Output.File:
SaveFileDialog fd = new SaveFileDialog();
fd.AutoUpgradeEnabled = true;
fd.Title = "Save generated database script as";
fd.Filter = "SQL Server Compact Script (*.sqlce)|*.sqlce|SQL Server Script (*.sql)|*.sql|All Files(*.*)|";
fd.OverwritePrompt = true;
fd.ValidateNames = true;
if (fd.ShowDialog() == DialogResult.OK)
{
fileName = fd.FileName;
}
break;
default:
break;
}
switch (action)
{
case Action.Csv:
using (var reader = new CsvReader(imo.FileName))
{
reader.ValueSeparator = imo.Separator;
HeaderRecord hr = reader.ReadHeaderRecord();
if (generator.ValidColumns(this.Parent.Name, hr.Values))
{
foreach (DataRecord record in reader.DataRecords)
{
generator.GenerateTableInsert(this.Parent.Name, hr.Values, record.Values);
}
}
}
break;
default:
break;
}
switch (output)
{
case Output.Editor:
// insert SQL script to document
EnvDTE.TextDocument doc = (EnvDTE.TextDocument)ServiceCache.ExtensibilityModel.Application.ActiveDocument.Object(null);
doc.EndPoint.CreateEditPoint().Insert(generator.GeneratedScript);
doc.DTE.ActiveDocument.Saved = true;
break;
case Output.File:
if (!string.IsNullOrEmpty(fileName))
{
System.IO.File.WriteAllText(fileName, generator.GeneratedScript);
}
break;
case Output.Clipboard:
Clipboard.Clear();
Clipboard.SetText(generator.GeneratedScript, TextDataFormat.UnicodeText);
break;
default:
break;
}
}
}
}
}
catch (System.Data.SqlServerCe.SqlCeException sqlCe)
{
Connect.ShowErrors(sqlCe);
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
public Dictionary<string, string> CreateUsersFromCSV(string csvPath)
{
var users = new List<User>();
var passwords = new Dictionary<string, string>();
var db = this.GetDbContext();
using (var reader = new CsvReader(csvPath))
{
reader.ReadHeaderRecord();
foreach (var record in reader.DataRecords)
{
var username = record.GetValueOrNull("Username");
if (string.IsNullOrEmpty(username))
{
throw new ArgumentNullException("Username", "Invalid username");
}
if (this.UsernameExists(username))
{
continue;
}
var user = this.CreateCSVUser(record);
var password = user.Password;
user.Password = this.EncryptPassword(user.Password);
users.Add(user);
passwords.Add(user.Username, password);
}
}
db.Users.InsertAllOnSubmit(users);
db.SubmitChanges();
foreach (var user in users)
{
if (!string.IsNullOrEmpty(user.Email))
{
var message = "Your account has been created:\nUsername: "******"\nPassword: "******"admin@iudico", user.Email, "Iudico Notification", message);
}
}
this.lmsService.Inform(UserNotifications.UserCreateMultiple, users);
return passwords;
}
//TODO: Wait on http://www.telerik.com/community/forums/silverlight/gridview/lightweight-datatable-add-column-after-rows-have-been-added.aspx
///// <summary>
///// Adds a column to the DataTable.
///// </summary>
///// <param name="newColumnUniqueName">New name of the column unique.</param>
//public ImportColumn AddColumn(string newColumnUniqueName)
//{
// //Add the new column at the end
// var newColumn = new ImportColumn { ColumnName = newColumnUniqueName };
// DataTable.Columns.Add(newColumn);
// return newColumn;
//}
private static DataTable ReadInCSVData(CsvReader reader)
{
var dataTable = new DataTable();
try
{
var headerRecord = reader.ReadHeaderRecord();
foreach (var headerName in headerRecord.Values)
dataTable.Columns.Add(new ImportColumn { ColumnName = headerName, DataType = typeof(string) });
//Add 3 extra columns
for (var i = 0; i < 3; i++)
dataTable.Columns.Add(new ImportColumn { ColumnName = "Extra Column " + i, DataType = typeof(string) });
foreach (var record in reader.DataRecords)
{
var newRow = dataTable.NewRow();
var headerIndex = 0;
foreach (var headerKey in headerRecord.Values)
{
var cleanedString = record.Values[headerIndex].Replace('�', ' ');
newRow[headerKey] = cleanedString;
headerIndex++;
}
dataTable.Rows.Add(newRow);
}
}
catch (ArgumentException)
{
MessageBox.Show("The data needs to be in CSV format.", "Error importing data.", MessageBoxButton.OK);
return null;
}
return dataTable;
}
/// <summary>
/// Creates a table in <paramref name="this"/> and populates it with data read from <paramref name="csvReader"/>.
/// </summary>
/// <remarks>
/// <para>
/// The name of the table created and added to <paramref name="this"/> is <see cref="DefaultTableName"/>. All records from <paramref name="csvReader"/> will be read and added to the table.
/// </para>
/// <para>
/// <paramref name="csvReader"/> must have a <see cref="HeaderRecord"/>, which is used to populate the column names of the <see cref="DataTable"/>.
/// </para>
/// </remarks>
/// <param name="this">
/// The <see cref="DataSet"/>.
/// </param>
/// <param name="csvReader">
/// The <see cref="CsvReader"/>.
/// </param>
/// <returns>
/// The number of rows added to the <see cref="DataTable"/> (and therefore the number of data records read from <paramref name="csvReader"/>).
/// </returns>
public static int Fill(this DataSet @this, CsvReader csvReader)
{
return @this.Fill(csvReader, DefaultTableName);
}
/// <summary>
/// Populates <paramref name="this"/> with data read from <paramref name="csvReader"/>.
/// </summary>
/// <remarks>
/// <para>
/// If <paramref name="this"/> has columns defined, those columns will be used when populating the data. If no columns have been defined, <paramref name="csvReader"/> must have a
/// <see cref="HeaderRecord"/>, which is then used to define the columns for <paramref name="this"/>. If any data record has more values than can fit into the columns defined on
/// <paramref name="this"/>, an exception is thrown.
/// </para>
/// </remarks>
/// <param name="this">
/// The <see cref="DataTable"/>.
/// </param>
/// <param name="csvReader">
/// The <see cref="CsvReader"/>.
/// </param>
/// <param name="maximumRecords">
/// The maximum number of records to read and add to <paramref name="this"/>.
/// </param>
/// <returns>
/// The number of rows added to <paramref name="this"/> (and therefore the number of data records read from <paramref name="csvReader"/>).
/// </returns>
public static int Fill(this DataTable @this, CsvReader csvReader, int? maximumRecords)
{
@this.AssertNotNull("@this");
csvReader.AssertNotNull("csvReader");
exceptionHelper.ResolveAndThrowIf(maximumRecords.GetValueOrDefault() < 0, "maximumRecordsMustBePositive");
if (@this.Columns.Count == 0)
{
// table has no columns, so we need to use the CSV header record to populate them
exceptionHelper.ResolveAndThrowIf(csvReader.HeaderRecord == null, "noColumnsAndNoHeaderRecord");
foreach (var columnName in csvReader.HeaderRecord)
{
@this.Columns.Add(columnName);
}
}
var remaining = maximumRecords.GetValueOrDefault(int.MaxValue);
var buffer = new DataRecord[16];
while (remaining > 0)
{
var read = csvReader.ReadDataRecords(buffer, 0, Math.Min(buffer.Length, remaining));
if (read == 0)
{
// no more data
break;
}
for (var i = 0; i < read; ++i)
{
var record = buffer[i];
exceptionHelper.ResolveAndThrowIf(record.Count > @this.Columns.Count, "moreValuesThanColumns", @this.Columns.Count, record.Count);
var recordAsStrings = new string[record.Count];
record.CopyTo(recordAsStrings, 0);
@this.Rows.Add(recordAsStrings);
}
remaining -= read;
}
return maximumRecords.GetValueOrDefault(int.MaxValue) - remaining;
}
/// <summary>
/// Populates <paramref name="this"/> with data read from <paramref name="csvReader"/>.
/// </summary>
/// <remarks>
/// <para>
/// All records from <paramref name="csvReader"/> will be read and added to the table.
/// </para>
/// <para>
/// If <paramref name="this"/> has columns defined, those columns will be used when populating the data. If no columns have been defined, <paramref name="csvReader"/> must have a
/// <see cref="HeaderRecord"/>, which is then used to define the columns for <paramref name="this"/>. If any data record has more values than can fit into the columns defined on
/// <paramref name="this"/>, an exception is thrown.
/// </para>
/// </remarks>
/// <param name="this">
/// The <see cref="DataTable"/>.
/// </param>
/// <param name="csvReader">
/// The <see cref="CsvReader"/>.
/// </param>
/// <returns>
/// The number of rows added to <paramref name="this"/> (and therefore the number of data records read from <paramref name="csvReader"/>).
/// </returns>
public static int Fill(this DataTable @this, CsvReader csvReader)
{
return @this.Fill(csvReader, null);
}
/// <summary>
/// Creates a table in <paramref name="this"/> and populates it with data read from <paramref name="csvReader"/>.
/// </summary>
/// <remarks>
/// <para>
/// <paramref name="csvReader"/> must have a <see cref="HeaderRecord"/>, which is used to populate the column names of the <see cref="DataTable"/>.
/// </para>
/// </remarks>
/// <param name="this">
/// The <see cref="DataSet"/>.
/// </param>
/// <param name="csvReader">
/// The <see cref="CsvReader"/>.
/// </param>
/// <param name="tableName">
/// The name of the table to create and add to <paramref name="this"/>
/// </param>
/// <param name="maximumRecords">
/// The maximum number of records to read and add to the <see cref="DataTable"/>.
/// </param>
/// <returns>
/// The number of rows added to the <see cref="DataTable"/> (and therefore the number of data records read from <paramref name="csvReader"/>).
/// </returns>
public static int Fill(this DataSet @this, CsvReader csvReader, string tableName, int? maximumRecords)
{
@this.AssertNotNull("@this");
tableName.AssertNotNull("tableName");
var table = @this.Tables.Add(tableName);
return table.Fill(csvReader, maximumRecords);
}
public static void ReadContentFile(ConstructFile construct, ref ContentFile contentFile)
{
string path = TableGlobalConfig.Instance.ResTablePath + "/" + construct.Name + ".csv";
if (!string.IsNullOrEmpty(construct.OldName))
{
path = TableGlobalConfig.Instance.ResTablePath + "/" + construct.OldName + ".csv";
}
if (!File.Exists(path))
{
CreateNewFile(construct);
return;
}
string[] lines = File.ReadAllLines(path, Encoding.Default);
if (lines.Length == 0)
return;
contentFile.ContentRow.Clear();
lines[0] = lines[0].Replace("\r\n", "\n");
StringReader rdr = new StringReader(string.Join("\n", lines));
using (var reader = new CsvReader(rdr))
{
HeaderRecord header = reader.ReadHeaderRecord();
//string curGourpName = "";
//string curClassName = "";
var columnTitles = construct.GetColumnTitles();
while (reader.HasMoreRecords)
{
DataRecord data = reader.ReadDataRecord();
//if (data[0].StartsWith("###"))
//{
// curGourpName = data[0].TrimStart('#');
// continue;
//}
//if (data[0].StartsWith("##"))
//{
// curClassName = data[0].TrimStart('#');
// continue;
//}
ContentRow clumn = ReadRow(construct, data, contentFile);
contentFile.AddContentRow(clumn);
}
if (IsHeaderMatch(construct, header))
{
contentFile.WriteFlag = false;
}
else
{
contentFile.WriteFlag = true;
}
contentFile.IsInit = false;
TableContentManager.Instance.AddContentFile(contentFile);
}
}
public void ImportData(object sender, ExecutedRoutedEventArgs e)
{
var menuInfo = ValidateMenuInfo(sender);
if (menuInfo == null) return;
try
{
using (IRepository repository = Helpers.DataConnectionHelper.CreateRepository(menuInfo.DatabaseInfo))
{
var generator = Helpers.DataConnectionHelper.CreateGenerator(repository, menuInfo.DatabaseInfo.DatabaseType);
ImportDialog imo = new ImportDialog();
imo.SampleHeader = generator.GenerateTableColumns(menuInfo.Name);
imo.Separator = System.Globalization.CultureInfo.CurrentCulture.TextInfo.ListSeparator.ToCharArray()[0];
if (imo.ShowModal() == true)
{
if (!string.IsNullOrWhiteSpace(imo.File) && System.IO.File.Exists(imo.File))
{
using (var reader = new CsvReader(imo.File, System.Text.Encoding.UTF8))
{
reader.ValueSeparator = imo.Separator;
HeaderRecord hr = reader.ReadHeaderRecord();
if (generator.ValidColumns(menuInfo.Name, hr.Values))
{
int i = 1;
foreach (DataRecord record in reader.DataRecords)
{
generator.GenerateTableInsert(menuInfo.Name, hr.Values, record.Values, i);
i++;
}
}
}
OpenSqlEditorToolWindow(menuInfo, generator.GeneratedScript);
Helpers.DataConnectionHelper.LogUsage("TableImport");
}
}
}
}
catch (System.IO.IOException iox)
{
EnvDTEHelper.ShowError(iox.Message);
}
catch (Exception ex)
{
Helpers.DataConnectionHelper.SendError(ex, menuInfo.DatabaseInfo.DatabaseType, false);
}
}
public void ImportData(object sender, ExecutedRoutedEventArgs e)
{
var menuInfo = ValidateMenuInfo(sender);
if (menuInfo != null)
{
try
{
using (IRepository repository = RepoHelper.CreateRepository(menuInfo.Connectionstring))
{
var generator = RepoHelper.CreateGenerator(repository, string.Empty);
ImportDialog imo = new ImportDialog();
imo.SampleHeader = generator.GenerateTableColumns(menuInfo.Name);
imo.Separator = System.Globalization.CultureInfo.CurrentCulture.TextInfo.ListSeparator.ToCharArray()[0];
imo.Owner = Application.Current.MainWindow;
if (imo.ShowDialog() == true)
{
using (var reader = new CsvReader(imo.File, System.Text.Encoding.UTF8))
{
reader.ValueSeparator = imo.Separator;
HeaderRecord hr = reader.ReadHeaderRecord();
if (generator.ValidColumns(menuInfo.Name, hr.Values))
{
int i = 1;
foreach (DataRecord record in reader.DataRecords)
{
generator.GenerateTableInsert(menuInfo.Name, hr.Values, record.Values, i);
i++;
}
}
}
OpenSqlEditorToolWindow(menuInfo, generator.GeneratedScript);
}
}
}
catch (Exception ex)
{
MessageBox.Show(Helpers.DataConnectionHelper.ShowErrors(ex));
}
}
}
public ImportDataVM()
{
#region Setup Commands
//When the SelectFileCommand is executed read the CSV data
SelectFileCommand = new RelayCommand<FileInfo>(fileInfo =>
{
IsBusy = true;
//use the CSVReader to read in the data
using (var csv = new CsvReader(fileInfo.OpenRead()))
DataTable = ReadInCSVData(csv);
IsBusy = false;
});
TryGeocodeCommand = new RelayCommand(TryGeocode);
//When the ImportDataCommand is executed save the datatable to the database
ImportDataCommand = new RelayCommand(SaveDataTableToDatabase);
#endregion
}
private void ParserTableContent(string content)
{
using (var rdr = new StringReader(content))
using (var reader = new CsvReader(rdr))
{
HeaderRecord header = reader.ReadHeaderRecord();
const string tableName = "SkillStepTable";
int lastId = -1;
while (reader.HasMoreRecords)
{
DataRecord data = reader.ReadDataRecord();
var r = new SkillStepTableRecord();
if (data[0].StartsWith("#"))
continue;
Records.Add(data[0], r);
}
}
}
private async Task ImportAsync(StreamReader streamReader, string name)
{
using (var csvReader = new CsvReader(streamReader))
{
var header = csvReader.ReadHeaderRecord();
var entity =
Inflector.Pluralize(CSharpClassName.ConvertToValidClassName(Path.GetFileNameWithoutExtension(name)));
if (entity.Length > 0 && char.IsLower(entity[0]))
entity = char.ToUpper(entity[0]) + entity.Substring(1);
var totalCount = 0;
var batch = new List<RavenJObject>();
var columns = header.Values.Where(x => x.StartsWith("@") == false).ToArray();
foreach (var record in csvReader.DataRecords)
{
batch.Clear();
var document = new RavenJObject();
string id = null;
RavenJObject metadata = null;
foreach (var column in columns)
{
if (string.IsNullOrEmpty(column))
continue;
if (string.Equals("id", column, StringComparison.OrdinalIgnoreCase))
{
id = record[column];
}
else if (string.Equals(Constants.RavenEntityName, column, StringComparison.OrdinalIgnoreCase))
{
metadata = metadata ?? new RavenJObject();
metadata[Constants.RavenEntityName] = record[column];
id = id ?? record[column] + "/";
}
else if (string.Equals(Constants.RavenClrType, column, StringComparison.OrdinalIgnoreCase))
{
metadata = metadata ?? new RavenJObject();
metadata[Constants.RavenClrType] = record[column];
id = id ?? record[column] + "/";
}
else
{
document[column] = SetValueInDocument(record[column]);
}
}
metadata = metadata ?? new RavenJObject {{"Raven-Entity-Name", entity}};
document.Add("@metadata", metadata);
metadata.Add("@id", id ?? Guid.NewGuid().ToString());
batch.Add(document);
totalCount++;
if (batch.Count >= BatchSize)
{
await FlushBatch(batch);
}
}
if (batch.Count > 0)
{
await FlushBatch(batch);
}
Report(String.Format("Imported {0:#,#;;0} documents", totalCount));
}
}
/// <summary>
/// Imports the entities.
/// </summary>
/// <param name="roleId">The current role id.</param>
/// <param name="importDestination">The import destination.</param>
/// <param name="dataCSV">The data CSV.</param>
/// <returns></returns>
public bool ImportEntities(Guid roleId, ImportDestination importDestination, byte[] dataCSV)
{
var businessAccount = ObjectContext.Owner(roleId).FirstOrDefault();
if (businessAccount == null)
throw new AuthenticationException("Invalid attempted access logged for investigation.");
//row index, column index, DataCategory, value
var rows = new List<ImportRow>();
using (var csv = new CsvReader(new MemoryStream(dataCSV)))
{
var headerRecord = csv.ReadHeaderRecord();
var categories = headerRecord.Values.Select(v => (DataCategory)Enum.Parse(typeof(DataCategory), v)).ToArray();
//setup a Tuple (DataRecord) for each row
var rowIndex = 0;
foreach (var dataRecord in csv.DataRecords)
{
rows.Add(ImportRowTools.ExtractCategoriesWithValues(rowIndex, categories, dataRecord));
rowIndex++;
}
}
#region Load the necessary associations
//If the destination is Locations or RecurringServices
//Load clients associations with names
Client[] clientAssociations = null;
if (importDestination == ImportDestination.Locations || importDestination == ImportDestination.RecurringServices)
clientAssociations = LoadClientAssociations(roleId, businessAccount, rows, importDestination == ImportDestination.RecurringServices);
IEnumerable<ServiceTemplate> serviceProviderServiceTemplates = null;
//If the destination is Clients or RecurringServices
//Load ServiceProvider ServiceTemplates (and sub data)
if (importDestination == ImportDestination.Clients || importDestination == ImportDestination.RecurringServices)
serviceProviderServiceTemplates = GetServiceProviderServiceTemplates(roleId).ToArray();
//If the destination is RecurringServices, load location associations
Location[] locationAssociations = null;
if (importDestination == ImportDestination.RecurringServices)
locationAssociations = LoadLocationAssociations(roleId, rows);
//If the destination is Locations
//Load region associations
Region[] regionAssociations = null;
if (importDestination == ImportDestination.Locations)
regionAssociations = LoadCreateRegionAssociations(roleId, businessAccount, rows);
#endregion
switch (importDestination)
{
case ImportDestination.Clients:
foreach (var row in rows)
{
var newClient = ImportRowTools.CreateClient(businessAccount, row);
if (!serviceProviderServiceTemplates.Any())
throw new Exception("This account does not have any service templates yet");
//Add the available services
foreach (var serviceTemplate in serviceProviderServiceTemplates)
newClient.ServiceTemplates.Add(serviceTemplate.MakeChild(ServiceTemplateLevel.ClientDefined));
this.ObjectContext.Clients.AddObject(newClient);
}
break;
case ImportDestination.Locations:
foreach (var row in rows)
{
var clientAssocation = GetClientAssociation(clientAssociations, row);
var regionAssociation = GetRegionAssociation(regionAssociations, row);
var newLocation = ImportRowTools.CreateLocation(businessAccount, row, clientAssocation, regionAssociation);
this.ObjectContext.Locations.AddObject(newLocation);
}
break;
case ImportDestination.RecurringServices:
{
foreach (var row in rows)
{
//Get the service template associaton
var serviceTemplateCell = row.GetCell(DataCategory.ServiceType);
if (serviceTemplateCell == null)
throw ImportRowTools.Exception("ServiceType not set", row);
var clientAssocation = GetClientAssociation(clientAssociations, row);
var serviceTemplateName = serviceTemplateCell.Value;
var clientServiceTemplate = clientAssocation.ServiceTemplates.FirstOrDefault(st => st.Name == serviceTemplateName);
//If the Client does not have the available service add it to the client
if (clientServiceTemplate == null)
{
var serviceProviderServiceTemplate = serviceProviderServiceTemplates.FirstOrDefault(st => st.Name == serviceTemplateName);
if (serviceProviderServiceTemplate == null)
throw ImportRowTools.Exception(String.Format("ServiceType '{0}' not found on this provider", serviceTemplateName), row);
clientServiceTemplate = serviceProviderServiceTemplate.MakeChild(ServiceTemplateLevel.ClientDefined);
clientAssocation.ServiceTemplates.Add(clientServiceTemplate);
}
//Get the location associaton
var locationAssociation = GetLocationAssociation(locationAssociations.Where(l => l.ClientId == clientAssocation.Id), row);
var newRecurringService = ImportRowTools.CreateRecurringService(businessAccount, row, clientServiceTemplate, clientAssocation, locationAssociation);
this.ObjectContext.RecurringServices.AddObject(newRecurringService);
}
}
break;
}
this.ObjectContext.SaveChanges();
return true;
}
//antisocial.top.csv
public IEnumerable<AnitSocialV2> GetAntiSocialDataV2()
{
var sourceFile = @"C:\ldn\antisocial.top.csv";
var reader = new Kent.Boogaart.KBCsv.CsvReader(File.OpenRead(sourceFile));
reader.ReadHeaderRecord();
DataRecord row;
AnitSocialV2 antiSocial;
while ((row = reader.ReadDataRecord()) != null)
{
if (row.Count != 4) continue;
antiSocial = null;
try
{
//lat long eventdate street vomit blood urine humanfouling dogfouling graffiti
antiSocial = new AnitSocialV2
{
Location = new Location
{
Latitude = double.Parse(row["lat"]),
Longitude = double.Parse(row["long"]),
},
Category = row["Antisocial"],
Radius = float.Parse(row["Radius"]),
};
}
catch
{
continue;
}
if (antiSocial != null)
yield return antiSocial;
}
}
/// <summary>
/// Creates a table in <paramref name="this"/> and populates it with data read from <paramref name="csvReader"/>.
/// </summary>
/// <remarks>
/// <para>
/// All records from <paramref name="csvReader"/> will be read and added to the table.
/// </para>
/// <para>
/// <paramref name="csvReader"/> must have a <see cref="HeaderRecord"/>, which is used to populate the column names of the <see cref="DataTable"/>.
/// </para>
/// </remarks>
/// <param name="this">
/// The <see cref="DataSet"/>.
/// </param>
/// <param name="csvReader">
/// The <see cref="CsvReader"/>.
/// </param>
/// <param name="tableName">
/// The name of the table to create and add to <paramref name="this"/>
/// </param>
/// <returns>
/// The number of rows added to the <see cref="DataTable"/> (and therefore the number of data records read from <paramref name="csvReader"/>).
/// </returns>
public static int Fill(this DataSet @this, CsvReader csvReader, string tableName)
{
return @this.Fill(csvReader, tableName, null);
}
public IEnumerable<Graffiti> GetGraffitiData()
{
var sourceFile = @"C:\ldn\graffiti.csv";
var reader = new Kent.Boogaart.KBCsv.CsvReader(File.OpenRead(sourceFile));
reader.ReadHeaderRecord();
DataRecord row;
while ((row = reader.ReadDataRecord()) != null)
{
yield return new Graffiti
{
EventDate = DateTime.Parse(row["eventdate"]),
StreetName = row["street"],
Location = new Location
{
Latitude = double.Parse(row["long"]),
Longitude = double.Parse(row["lat"]),
X = double.Parse(row["x"]),
Y = double.Parse(row["y"])
}
};
}
}
