/// <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"]) } }; } }