public Table_Names(OleDbDataReader reader, CensusFileType fileType)
{
switch (fileType)
{
case CensusFileType.SummaryTwo:
SORT_ID = (int)reader[0];
TABLE_CODE = (string)reader[1];
TABLE_NAME = (string)reader[2];
CELL_COUNT = (string)reader[3];
break;
case CensusFileType.Redistricting:
case CensusFileType.AdvanceGroupQuarters:
case CensusFileType.DemographicProfile:
case CensusFileType.SummaryOne:
case CensusFileType.IslandAreas_DPSF:
case CensusFileType.AIANSummaryFile:
case CensusFileType.SF1CongressionalDistricts113:
case CensusFileType.IslandAreas_IASF:
case CensusFileType.IslandAreasDetailedCrossTabulations:
case CensusFileType.IslandAreas_PUMS:
case CensusFileType.Stateside_PUMS:
throw new NotImplementedException("The Table Categories table is not included in this specific dataset.");
default:
throw new ArgumentOutOfRangeException(nameof(fileType), fileType, null);
}
}
public static string GetGeoType(CensusFileType censusFileType)
{
switch (censusFileType)
{
case CensusFileType.Redistricting:
return("PL_Geo_Header");
case CensusFileType.DemographicProfile:
return("Geo_Header");
case CensusFileType.SummaryOne:
case CensusFileType.SF1CongressionalDistricts113:
return("GEO_HEADER_SF1");
case CensusFileType.SummaryTwo:
return("SF2_GeoHeader");
case CensusFileType.UrbanAreaUpdate:
case CensusFileType.AdvanceGroupQuarters:
case CensusFileType.IslandAreas_DPSF:
case CensusFileType.AIANSummaryFile:
case CensusFileType.IslandAreas_IASF:
case CensusFileType.IslandAreasDetailedCrossTabulations:
case CensusFileType.IslandAreas_PUMS:
case CensusFileType.Stateside_PUMS:
return(null);
default:
throw new ArgumentOutOfRangeException(nameof(censusFileType), censusFileType, null);
}
}
public Iterations_NOTES(OleDbDataReader reader, CensusFileType fileType)
{
switch (fileType)
{
case CensusFileType.SummaryTwo:
if (reader[0] != DBNull.Value)
{
SORT_ID = (int?)reader[0];
}
if (reader[1] != DBNull.Value)
{
NOTE = (string)reader[1];
}
break;
case CensusFileType.Redistricting:
case CensusFileType.AdvanceGroupQuarters:
case CensusFileType.DemographicProfile:
case CensusFileType.SummaryOne:
case CensusFileType.IslandAreas_DPSF:
case CensusFileType.AIANSummaryFile:
case CensusFileType.SF1CongressionalDistricts113:
case CensusFileType.IslandAreas_IASF:
case CensusFileType.IslandAreasDetailedCrossTabulations:
case CensusFileType.IslandAreas_PUMS:
case CensusFileType.Stateside_PUMS:
throw new NotImplementedException("The Iterations table is not included in this specific dataset.");
default:
throw new ArgumentOutOfRangeException(nameof(fileType), fileType, null);
}
}
public TableSchema(CensusFileType fileType, DataRow row)
{
FileType = fileType;
if (row[0] != DBNull.Value)
{
Catalog = (string)row[0];
}
if (row[1] != DBNull.Value)
{
Schema = (string)row[1];
}
if (row[2] != DBNull.Value)
{
Name = (string)row[2];
}
if (row[3] != DBNull.Value)
{
Type = (string)row[3];
}
if (row[4] != DBNull.Value)
{
Guid = (Guid)row[4];
}
if (row[5] != DBNull.Value)
{
Description = (string)row[5];
}
if (row[6] != DBNull.Value)
{
PropID = (string)row[6];
}
if (row[7] != DBNull.Value)
{
Date_Created = (DateTime?)row[7];
}
if (row[8] != DBNull.Value)
{
Date_Modified = (DateTime?)row[8];
}
}
//public static readonly List<Tuple<string, string, IEnumerable<ZipArchiveEntry>>> RedistrictingDataFiles = (from file in RedistrictingDataArchives
// select file.FullName
// into fullFileName
// let aggregatedFileName = DataFilePaths.Aggregate(fullFileName, (current, dataPath) => current.Replace(dataPath, ""))
// .Trim('\\')
// .Trim()
// let state = aggregatedFileName.Split('\\')[0]
// let fileName = aggregatedFileName.Split('\\')[1]
// let name = fileName.Split('.')[0]
// let archive = ZipFile.OpenRead(fullFileName)
// let entries = archive.Entries
// select new Tuple<string, string, IEnumerable<ZipArchiveEntry>>(name.Replace("2010", ""), state.Replace("_", ""), entries)).ToList();
//public static readonly List<Tuple<string, string, IEnumerable<ZipArchiveEntry>>> DemographicProfileDataFiles = (from file in DemographicProfileDataArchives
// select file.FullName
// into fullFileName
// let aggregatedFileName = DataFilePaths.Aggregate(fullFileName, (current, dataPath) => current.Replace(dataPath, ""))
// .Trim('\\')
// .Trim()
// let state = aggregatedFileName.Split('\\')[0]
// let fileName = aggregatedFileName.Split('\\')[1]
// let name = fileName.Split('.')[0]
// let archive = ZipFile.OpenRead(fullFileName)
// let entries = archive.Entries
// select new Tuple<string, string, IEnumerable<ZipArchiveEntry>>(name.Replace("2010", ""), state.Replace("_", ""), entries)).ToList();
//public static readonly List<Tuple<string, string, IEnumerable<ZipArchiveEntry>>> Summary1DataFiles = (from file in Summary1DataArchives
// select file.FullName
// into fullFileName
// let aggregatedFileName = DataFilePaths.Aggregate(fullFileName, (current, dataPath) => current.Replace(dataPath, ""))
// .Trim('\\')
// .Trim()
// let state = aggregatedFileName.Split('\\')[0]
// let fileName = aggregatedFileName.Split('\\')[1]
// let name = fileName.Split('.')[0]
// let archive = ZipFile.OpenRead(fullFileName)
// let entries = archive.Entries
// select new Tuple<string, string, IEnumerable<ZipArchiveEntry>>(name.Replace("2010", ""), state.Replace("_", ""), entries)).ToList();
//public static readonly List<Tuple<string, string, IEnumerable<ZipArchiveEntry>>> Summary2DataFiles = (from file in Summary2DataArchives
// select file.FullName
// into fullFileName
// let aggregatedFileName = DataFilePaths.Aggregate(fullFileName, (current, dataPath) => current.Replace(dataPath, ""))
// .Trim('\\')
// .Trim()
// let state = aggregatedFileName.Split('\\')[0]
// let fileName = aggregatedFileName.Split('\\')[1]
// let name = fileName.Split('.')[0]
// let archive = ZipFile.OpenRead(fullFileName)
// let entries = archive.Entries
// select new Tuple<string, string, IEnumerable<ZipArchiveEntry>>(name.Replace("2010", ""), state.Replace("_", ""), entries)).ToList();
//public static readonly List<Tuple<string, string, IEnumerable<ZipArchiveEntry>>> CongressionalDistrictDataFiles = (from file in CongressionalDistrictDataArchives
// select file.FullName
// into fullFileName
// let aggregatedFileName = DataFilePaths.Aggregate(fullFileName, (current, dataPath) => current.Replace(dataPath, ""))
// .Trim('\\')
// .Trim()
// let state = aggregatedFileName.Split('\\')[0]
// let fileName = aggregatedFileName.Split('\\')[1]
// let name = fileName.Split('.')[0]
// let archive = ZipFile.OpenRead(fullFileName)
// let entries = archive.Entries
// select new Tuple<string, string, IEnumerable<ZipArchiveEntry>>(name.Replace("2010", ""), state.Replace("_", ""), entries)).ToList();
#endregion Data Files
#endregion Properties
#region Data Retrieval
public static void ProcessData(CensusFileType fileType)
{
switch (fileType)
{
case CensusFileType.Redistricting:
ProcessRedistrictingFileData();
break;
case CensusFileType.DemographicProfile:
ProcessDemographicProfileFileData();
break;
case CensusFileType.SummaryOne:
ProcessSummary1FileData();
break;
case CensusFileType.SummaryTwo:
ProcessSummary2FileData();
break;
case CensusFileType.SF1CongressionalDistricts113:
ProcessCongressionalDistrictsFileData();
break;
case CensusFileType.AdvanceGroupQuarters:
case CensusFileType.IslandAreas_DPSF:
case CensusFileType.AIANSummaryFile:
case CensusFileType.IslandAreas_IASF:
case CensusFileType.IslandAreasDetailedCrossTabulations:
case CensusFileType.IslandAreas_PUMS:
case CensusFileType.Stateside_PUMS:
case CensusFileType.UrbanAreaUpdate:
Console.WriteLine($"{fileType} is not supported as there is no access file outlining the CSV schemas.");
break;
default:
throw new ArgumentOutOfRangeException(nameof(fileType), fileType, null);
}
}
public static string GetName(string fileName, string stateCode, CensusFileType censusFileType)
{
string fileTypeName = censusFileType.GetName();
string fileTypeShortName = censusFileType.GetShortName();
fileName = fileName.Replace($"20101.{censusFileType.GetShortName().ToLowerInvariant()}", "")
.Replace($"2010.{censusFileType.GetShortName().ToLowerInvariant()}", "")
.Replace($".{censusFileType.GetShortName().ToLowerInvariant()}", "");
switch (censusFileType)
{
case CensusFileType.DemographicProfileWithSF1Geos:
return("Part1");
case CensusFileType.Redistricting:
return("PL");
case CensusFileType.SF1CongressionalDistricts113:
case CensusFileType.SummaryOne:
return(fileName.Replace(stateCode, $"{fileTypeShortName}_"));
case CensusFileType.SummaryTwo:
return(fileName.Replace(fileName.Substring(2, 3), "").Replace(stateCode, $"{fileTypeShortName}_Segment_"));
case CensusFileType.UrbanAreaUpdate:
case CensusFileType.AdvanceGroupQuarters:
case CensusFileType.AIANSummaryFile:
case CensusFileType.IslandAreasDetailedCrossTabulations:
case CensusFileType.IslandAreas_DPSF:
case CensusFileType.IslandAreas_IASF:
case CensusFileType.IslandAreas_PUMS:
case CensusFileType.Stateside_PUMS:
return(null);
default:
throw new ArgumentOutOfRangeException(nameof(censusFileType), censusFileType, null);
}
}
public DATA_FIELD_DESCRIPTORS(OleDbDataReader reader, CensusFileType summaryFileType)
{
switch (summaryFileType)
{
case CensusFileType.SummaryOne:
case CensusFileType.SF1CongressionalDistricts113:
if (reader[0] != DBNull.Value)
{
SORT_ID = (int?)reader[0];
}
if (reader[1] != DBNull.Value)
{
SEGMENT = (string)reader[1];
}
if (reader[2] != DBNull.Value)
{
TABLE_NUMBER = (string)reader[2];
}
if (reader[3] != DBNull.Value)
{
FIELD_NAME = (string)reader[3];
}
if (reader[4] != DBNull.Value)
{
FIELD_CODE = (string)reader[4];
}
if (reader[5] != DBNull.Value)
{
DECIMAL = (short)reader[5];
}
break;
case CensusFileType.SummaryTwo:
if (reader[0] != DBNull.Value)
{
SORT_ID = (int)reader[0];
}
if (reader[1] != DBNull.Value)
{
TABLE_NUMBER = (string)reader[1];
}
if (reader[2] != DBNull.Value)
{
SEGMENT = (string)reader[2];
}
if (reader[3] != DBNull.Value)
{
FIELD_NAME = (string)reader[3];
}
if (reader[4] != DBNull.Value)
{
FIELD_CODE = (string)reader[4];
}
break;
case CensusFileType.Redistricting:
case CensusFileType.DemographicProfile:
if (reader[0] != DBNull.Value)
{
FIELD_NAME = (string)reader[0];
}
if (reader[1] != DBNull.Value)
{
FIELD_CODE = (string)reader[1];
}
if (reader[2] != DBNull.Value)
{
SEGMENT = (reader[2] as double?)?.ToString() ?? (reader[2] as decimal?)?.ToString() ?? (string)reader[2];
}
break;
case CensusFileType.AdvanceGroupQuarters:
case CensusFileType.IslandAreas_DPSF:
case CensusFileType.AIANSummaryFile:
case CensusFileType.IslandAreas_IASF:
case CensusFileType.IslandAreasDetailedCrossTabulations:
case CensusFileType.IslandAreas_PUMS:
case CensusFileType.Stateside_PUMS:
throw new NotImplementedException("The Data Field Descriptors table is not included in this specific dataset.");
default:
throw new ArgumentOutOfRangeException(nameof(summaryFileType), summaryFileType, null);
}
}
public GeoHeader_Specifications(OleDbDataReader row, CensusFileType summaryFileType)
{
switch (summaryFileType)
{
case CensusFileType.SummaryTwo:
if (row[0] != DBNull.Value)
{
ID = (int)row[0];
}
if (row[1] != DBNull.Value)
{
FIELD = (string)row[1];
}
if (row[2] != DBNull.Value)
{
DATA_DICTIONARY_REFERENCE = (string)row[2];
}
if (row[3] != DBNull.Value)
{
STARTING_POSITION = (string)row[3];
}
if (row[4] != DBNull.Value)
{
FIELD_SIZE = (string)row[4];
}
if (row[5] != DBNull.Value)
{
DATA_TYPE = (string)row[5];
}
break;
case CensusFileType.Redistricting:
case CensusFileType.DemographicProfile:
if (row[0] != DBNull.Value)
{
FIELD = (string)row[0];
}
if (row[1] != DBNull.Value)
{
DATA_DICTIONARY_REFERENCE = (string)row[1];
}
if (row[2] != DBNull.Value)
{
FIELD_SIZE = (row[2] as double?)?.ToString(CultureInfo.InvariantCulture) ?? (row[2] as decimal?)?.ToString(CultureInfo.InvariantCulture);
}
break;
case CensusFileType.AdvanceGroupQuarters:
case CensusFileType.SummaryOne:
case CensusFileType.IslandAreas_DPSF:
case CensusFileType.AIANSummaryFile:
case CensusFileType.SF1CongressionalDistricts113:
case CensusFileType.IslandAreas_IASF:
case CensusFileType.IslandAreasDetailedCrossTabulations:
case CensusFileType.IslandAreas_PUMS:
case CensusFileType.Stateside_PUMS:
throw new NotImplementedException("The Geography Field Specifications table is not included in this specific dataset.");
default:
throw new ArgumentOutOfRangeException(nameof(summaryFileType), summaryFileType, null);
}
}
public static void ProcessCensusData(CensusFileType censusFileType)
{
SSISApp = new SSISHelper();
string fileTypeName = censusFileType.GetName();
string fileTypeShortName = censusFileType.GetShortName();
var dataArchives = GetDataArchives(censusFileType);
foreach (KeyValuePair <string, IEnumerable <ZipArchiveEntry> > file in dataArchives)
{
Console.WriteLine($"{file.Key} | {file.Value.Count()} files");
foreach (ZipArchiveEntry entry in file.Value)
{
Console.WriteLine($"Processing {entry.FullName}");
Console.WriteLine($"\t{entry.Name} | {entry.CompressedLength} | {entry.Length} | {entry.LastWriteTime}");
bool isGeo = entry.Name.ToLowerInvariant()
.Contains("geo");
string name = isGeo
? GetGeoType(censusFileType)
: GetName(entry.Name, file.Key, censusFileType);
string outputFilePath = $"{Program.OutputPath}\\{entry.Name}";
string tableName = $"[{Program.DefaultDatabase}].[{fileTypeName}].[{name}]";
//SqlHelper.TruncateTable(tableName);
int rowsAffected = 0;
if (!File.Exists(outputFilePath))
{
using (StreamReader sr = new StreamReader(entry.Open()))
{
using (FileStream sw = File.Create(outputFilePath))
{
Console.Write($"Copying file to {outputFilePath}. . . Please wait!");
sr.BaseStream.CopyTo(sw);
}
}
}
if (isGeo)
{
Console.WriteLine($"\rFile copied successfully.\t\t\t\t\t");
Console.Write($"\rProcessing {entry.Name} as a Flat File into {tableName}\t\t\t\t\t");
string mapName = $"{Program.BaseNamespace}.{Program.Namespace}.Mapping.{fileTypeName}_{name}Map";
Type mapType = Type.GetType(mapName);
dynamic map = mapType == null
? null
: Activator.CreateInstance(mapType);
var result = SSISApp.ProcessFlatFile(outputFilePath, map);
Console.WriteLine($"SSIS Flat File Task Result: {result}");
}
else
{
rowsAffected += SqlHelper.BulkCSVInsert(tableName, outputFilePath);
}
Console.WriteLine($"\r{rowsAffected} records affected from processing {entry.Name} into {tableName}\t\t\t\t\t");
if (File.Exists(outputFilePath))
{
File.Delete(outputFilePath);
}
}
}
SSISApp.Save();
}
public static Dictionary <string, IEnumerable <ZipArchiveEntry> > GetDataArchives(CensusFileType censusFileType)
{
switch (censusFileType)
{
case CensusFileType.DemographicProfileWithSF1Geos:
return(DemographicProfileDataArchives);
case CensusFileType.SF1CongressionalDistricts113:
return(CongressionalDistrictDataArchives);
case CensusFileType.SummaryOne:
return(Summary1DataArchives);
case CensusFileType.SummaryTwo:
return(Summary2DataArchives);
case CensusFileType.UrbanAreaUpdate:
return(UrbanUpdateDataArchives);
default:
return(new Dictionary <string, IEnumerable <ZipArchiveEntry> >());
}
}
public AccessFile(string filePath, CensusFileType fileType)
{
FilePath = filePath;
FileType = fileType;
}
