public async Task <DataTable> GetCSVTable(ConnectParameters source, ConnectParameters target, string indexDataType) { DataTable result = new DataTable(); if (dt.Rows.Count == 0) { DateTime timeStart = DateTime.Now; DateTime timeEnd; TimeSpan diff; connectionString = target.ConnectionString; dt = new DataTable(); string accessJson = await fileStorageService.ReadFile("connectdefinition", "PPDMDataAccess.json"); _dataDef = JsonConvert.DeserializeObject <List <DataAccessDef> >(accessJson); string referenceJson = await fileStorageService.ReadFile("connectdefinition", "PPDMReferenceTables.json"); _references = JsonConvert.DeserializeObject <List <ReferenceTable> >(referenceJson); string csvJson = await fileStorageService.ReadFile("connectdefinition", "CSVDataAccess.json"); _csvDef = JsonConvert.DeserializeObject <List <CSVAccessDef> >(csvJson); //Console.WriteLine("start reading csv file"); string csvText = await fileStorageService.ReadFile(source.Catalog, source.FileName); timeEnd = DateTime.Now; diff = timeEnd - timeStart; //Console.WriteLine($"Time span, read all definitions files: {diff}"); string dataType = source.DataType.Remove(source.DataType.Length - 1, 1); dataAccess = _dataDef.First(x => x.DataType == dataType); dt = NewDataTable(dataType); tableDataType = dataType; int headerLines = 1; int wellCounter = 0; CSVAccessDef csvAccess = _csvDef.First(x => x.DataType == dataType); Dictionary <string, int> attributes = csvAccess.Mappings.ToDictionary(); Dictionary <string, string> constants = csvAccess.Constants.ToStringDictionary(); byte[] byteArray = Encoding.ASCII.GetBytes(csvText); MemoryStream csvStream = new MemoryStream(byteArray); using (TextFieldParser csvParser = new TextFieldParser(csvStream)) { csvParser.CommentTokens = new string[] { "#" }; csvParser.SetDelimiters(new string[] { "," }); csvParser.HasFieldsEnclosedInQuotes = true; for (int i = 0; i < headerLines; i++) { string line = csvParser.ReadLine(); } while (!csvParser.EndOfData) { wellCounter++; if ((wellCounter % 1000) == 0) { var tmp = wellCounter; } string[] fields = csvParser.ReadFields(); string key = GetDataKey(fields, attributes); string duplicateKey = GetDuplicateKey(fields, attributes); if (string.IsNullOrEmpty(key)) { //Console.WriteLine($"Well {wellCounter} has an empty key"); } else { if (duplicates.ContainsKey(duplicateKey)) { DataRow[] rows = dt.Select(key); rows[0] = InsertCSVRow(rows[0], attributes, fields, constants); rows[0]["ROW_CHANGED_DATE"] = DateTime.Now.ToString("yyyy-MM-dd"); } else { DataRow newRow = dt.NewRow(); newRow = InsertCSVRow(newRow, attributes, fields, constants); newRow["ROW_CREATED_DATE"] = DateTime.Now.ToString("yyyy-MM-dd"); newRow["ROW_CHANGED_DATE"] = DateTime.Now.ToString("yyyy-MM-dd"); dt.Rows.Add(newRow); duplicates.Add(duplicateKey, ""); } } } } timeEnd = DateTime.Now; diff = timeEnd - timeStart; //Console.WriteLine($"Time span, completion: {diff}"); } if (tableDataType == indexDataType) { result = dt; } else { result = GetParentDataTable(indexDataType); } return(result); }
public async Task LoadCSVFile(ConnectParameters source, ConnectParameters target, string fileName) { DateTime timeStart = DateTime.Now; connectionString = target.ConnectionString; string accessJson = await fileStorageService.ReadFile("connectdefinition", "PPDMDataAccess.json"); _dataDef = JsonConvert.DeserializeObject <List <DataAccessDef> >(accessJson); string referenceJson = await fileStorageService.ReadFile("connectdefinition", "PPDMReferenceTables.json"); _references = JsonConvert.DeserializeObject <List <ReferenceTable> >(referenceJson); string csvJson = await fileStorageService.ReadFile("connectdefinition", "CSVDataAccess.json"); _csvDef = JsonConvert.DeserializeObject <List <CSVAccessDef> >(csvJson); //Console.WriteLine("start reading csv file"); string csvText = await fileStorageService.ReadFile(source.Catalog, fileName); DateTime timeEnd = DateTime.Now; TimeSpan diff = timeEnd - timeStart; //Console.WriteLine($"Time span, read all definitions files: {diff}"); string dataType = source.DataType.Remove(source.DataType.Length - 1, 1); dataAccess = _dataDef.First(x => x.DataType == dataType); CSVAccessDef csvAccess = _csvDef.First(x => x.DataType == dataType); Dictionary <string, int> attributes = csvAccess.Mappings.ToDictionary(); Dictionary <string, string> constants = csvAccess.Constants.ToStringDictionary(); Dictionary <string, string> columnTypes = dt.GetColumnTypes(); DbUtilities dbConn = new DbUtilities(); dbConn.OpenWithConnectionString(connectionString); string dataTypeSql = dataAccess.Select; attributeProperties = CommonDbUtilities.GetColumnSchema(dbConn, dataTypeSql); dbConn.CloseConnection(); //Console.WriteLine("Start parsing csv file"); using (TextReader csvStream = new StringReader(csvText)) { var conf = new CsvConfiguration(CultureInfo.InvariantCulture) { //BadDataFound = null }; using (var csv = new CsvReader(csvStream, conf)) { csv.Read(); csv.ReadHeader(); string[] headerRow = csv.HeaderRecord; var attributeMappings = new Dictionary <string, string>(); foreach (var item in attributes) { int colNumber = item.Value; attributeMappings.Add(headerRow[colNumber], item.Key); } List <dynamic> csvRecords = csv.GetRecords <dynamic>().ToList(); timeEnd = DateTime.Now; diff = timeEnd - timeStart; //Console.WriteLine($"Time span, parsed cvs file into dynamic objects: {diff}"); foreach (var row in csvRecords) { DynamicObject newCsvRecord = new DynamicObject(); foreach (var item in row) { if (attributeMappings.ContainsKey(item.Key)) { string dbAttribute = attributeMappings[item.Key]; string value = item.Value; string dataProperty = attributeProperties[dbAttribute]; if (dataProperty.Contains("varchar")) { string numberString = Regex.Match(dataProperty, @"\d+").Value; int maxCharacters = Int32.Parse(numberString); if (value.Length > maxCharacters) { value = value.Substring(0, maxCharacters); } } newCsvRecord.AddProperty(dbAttribute, value); } } FixKey(newCsvRecord); } timeEnd = DateTime.Now; diff = timeEnd - timeStart; //Console.WriteLine($"Time span, fixed dynamic objects: {diff}"); dt = DynamicToDT(newCsvRecords); timeEnd = DateTime.Now; diff = timeEnd - timeStart; //Console.WriteLine($"Time span, transfer from csv to datatable: {diff}"); InsertTableToDatabase(attributes, dataType, target, constants); } } timeEnd = DateTime.Now; diff = timeEnd - timeStart; //Console.WriteLine($"Time span, completion: {diff}"); }