public bool CreateColumnMappingsTable(DbConnection conn) { DbClient.GetCommand(string.Format("DROP TABLE IF EXISTS \"{0}\";", DbConstants.TABLE_ColumnMappings), conn).ExecuteNonQuery(); //create table string createMappingsTableSql = DataClient.GenerateTableSQLFromFields(DbConstants.TABLE_ColumnMappings, SequenceFileReader.Columns); DbClient.GetCommand(createMappingsTableSql, conn).ExecuteNonQuery(); string tableSelect = string.Format("select * from \"{0}\"", DbConstants.TABLE_ColumnMappings); var adapter = DataClient.GetMagicAdapter(conn, DbClient, tableSelect); var table = DataClient.GetMagicTable(conn, DbClient, tableSelect); string colMapDir = GetLocalColumnMappingsDirectory(); if (Directory.Exists(colMapDir)) { _log.Debug("Importing Sequence Files..."); int ixid = 0; foreach (string file in Directory.GetFiles(colMapDir, "Seq*.xls", SearchOption.TopDirectoryOnly)) { //Extract sequence number from filename string localFilename = Path.GetFileName(file); Regex sequenceFormat = new Regex(@"^Seq(\d{1,4})\.xls$"); Match match = sequenceFormat.Match(localFilename); if (!match.Groups[0].Success) { _log.Warn("Malformed filename found in sequence files folder; skipping\n\t" + file); continue; } int seqNo = int.Parse(match.Groups[1].Value); //Read data from file var reader = new SequenceFileReader(file).GetReader(); if (reader == null) { _log.Error("One of the sequence files is missing; skipping\n\t" + file); continue; } DataSet fileData = reader.AsDataSet(false); if (fileData.Tables == null || fileData.Tables.Count == 0) { _log.ErrorFormat("Sequence File had no readable variables: {0} ", file); continue; } else if (fileData.Tables.Count > 1) { _log.WarnFormat("Sequence File had multiple worksheets : {0} ", file); } DataTable firstWorksheet = fileData.Tables[0]; if (firstWorksheet.Rows == null || firstWorksheet.Rows.Count < 2) { _log.ErrorFormat("Sequence File didn't have enough rows: {0} ", file); continue; } else if (firstWorksheet.Rows.Count > 2) { _log.WarnFormat("Sequence File had too many rows: {0} ", file); } //Expected values of row: FILEID,FILETYPE,STUSAB,CHARITER,SEQUENCE,LOGRECNO,... DataRow columnIDRow = firstWorksheet.Rows[0]; DataRow columnNameRow = firstWorksheet.Rows[1]; if (columnIDRow.ItemArray == null || columnIDRow.ItemArray.Length < 7) { _log.Error("One of the sequence files had bad data, skipping\n\t" + file); continue; } //Add data to database for (int i = 6; i < columnIDRow.ItemArray.Length; i++) { //This file has _ separating Table Number and offset, everywhere else doesn't string columnID = columnIDRow[i].ToString().Trim().Replace("_", ""); string columnName = (columnNameRow[i] as string); // ixid, CENSUS_TABLE_ID, NAME, COLNO, SEQNO var toAdd = new object[] { ixid++, columnID, columnName, i, seqNo }; table.Rows.Add(toAdd); } } if ((table != null) && (table.Rows.Count > 0)) { _log.Debug("Saving... (This may take a while)"); adapter.Update(table); table.AcceptChanges(); } else { _log.Error("Could not read any of the sequence files!"); return false; } _log.Debug("Importing Sequence Files... Done!"); return true; } else { _log.Error("Could not find column mappings directory file, table not initialized!"); return false; } }