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;
}
}
