SchemaMapper is a data integration class library that facilitates data import process from external sources having different schema definitions. It replaces creating many integration services packages by writing few lines of codes.

It imports tabular data from different data sources such as into a destination table with a user defined table schema after mapping columns between source and destination.

SchemaMapper has the ability to read data from:

And it can export data into different destination types:

In addition, it allows users to add new computed and fixed valued columns.

Used technologies

SchemaMapper utilizes from many technologies to read data from different source such as:

Note: The needed Assemblies are found in the Assemblies folder but it is recommended to install the assemblies related to the providers that you want to connect to from the official links (mentioned above) since they can requires more related assemblies

Project details

SchemaMapper is composed of four main namespaces:

Wiki

NuGet Package

Credits

Credits are for Munchy Bytes™

Examples

(1) Converters:

Import data from Excel file (first worksheet)

using (SchemaMapperDLL.Classes.Converters.MsExcelImport smExcel = new SchemaMapperDLL.Classes.Converters.MsExcelImport(@"U:\Passwords.xlsx","",false))
{

   //Read Excel
   smExcel.BuildConnectionString();
   var lst = smExcel.GetSheets();
   DataTable dt = smExcel.GetTableByName(lst.First(), true, 0);
   return dt;
}

Import data from Excel file using paging

using (SchemaMapperDLL.Classes.Converters.MsExcelImport smExcel = new SchemaMapperDLL.Classes.Converters.MsExcelImport(@"U:\Passwords.xlsx", "", false)){

   //Read Excel with pagging
   smExcel.BuildConnectionString();
   var lst = smExcel.GetSheets();

   int result = 1;
   int PagingStart = 1, PagingInterval = 10;

   while (result != 0){

      DataTable dt = smExcel.GetTableByNamewithPaging(lst.First(), PagingStart, PagingInterval, out result, true, 0);

      PagingStart = PagingStart + PagingInterval;

   }

}

Import data from flat file (.txt, .csv)

using (SchemaMapperDLL.Classes.Converters.FlatFileImportTools smFlat = new SchemaMapperDLL.Classes.Converters.FlatFileImportTools(@"U:\Passwords.csv",true,0))
{

   //Read flat file structure
   smFlat.BuildDataTableStructure();
   //Import data from flat file
   DataTable dt = smFlat.FillDataTable();
   int Result = dt.Rows.Count;

}

Import data from word document

using (SchemaMapperDLL.Classes.Converters.MsWordImportTools smWord = new SchemaMapperDLL.Classes.Converters.MsWordImportTools(@"U:\DocumentTable.docx", true, 0))
{

   smWord.ImportWordTablesIntoList(";");
   DataSet ds = smWord.ConvertListToTables(";");

   int ct = ds.Tables.Count;
}

(2) SchemaMapping

Initiate a SchemaMapper class

First you have to imports SchemaMapperDLL.Classes.SchemaMapping namespace.

using SchemaMapperDLL.Classes.SchemaMapping;

public SchemaMapper InitiateTestSchemaMapper(string schema, string table){

   SchemaMapper smResult = new SchemaMapper();

   smResult.TableName = table;
   smResult.SchemaName = schema;

   //Add variables
   smResult.Variables.Add(new Variable("@Today", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")));


   //Define Columns
   SchemaMapper_Column smServerCol = new SchemaMapper_Column("Server_Name", SchemaMapper_Column.ColumnDataType.Text);
   SchemaMapper_Column smUserCol = new SchemaMapper_Column("User_Name", SchemaMapper_Column.ColumnDataType.Text);
   SchemaMapper_Column smPassCol = new SchemaMapper_Column("Password", SchemaMapper_Column.ColumnDataType.Text);

   //Define a column with Fixed Value
   SchemaMapper_Column smFixedValueCol = new SchemaMapper_Column("AddedDate", SchemaMapper_Column.ColumnDataType.Text,"@Today");
   
   //Define a Column with Expression
   SchemaMapper_Column smExpressionCol = new SchemaMapper_Column("UserAndPassword",SchemaMapper_Column.ColumnDataType.Text,true,"[User_Name] + '|' + [Password]");

   //Add columns to SchemaMapper
   smResult.Columns.Add(smServerCol);
   smResult.Columns.Add(smUserCol);
   smResult.Columns.Add(smPassCol);
   smResult.Columns.Add(smFixedValueCol);
   smResult.Columns.Add(smExpressionCol);

   //Add all possible input Columns Names for each Column
   smServerCol.MappedColumns.AddRange(new[] {"server","server name","servername","Server","Server Name","ServerName"});
   smUserCol.MappedColumns.AddRange(new[] { "UserName", "User", "login", "Login", "User name" });
   smPassCol.MappedColumns.AddRange(new[] { "Password","pass", "Pass", "password" });

   //Added columns to ignore if found
   //Sys_SheetName and Sys_ExtraFields is an auto generated column when reading Excel file
   smResult.IgnoredColumns.AddRange(new[] { "Column1", "Sys_Sheetname", "Sys_ExtraFields", "Center Name" });

   //Save Schema Mapper into xml
   smResult.WriteToXml(Environment.CurrentDirectory + "\\SchemaMapper\\1.xml",true);

   return smResult;

}

Change DataTable schema and insert into SQL using stored procedure with Table variable parameter

DataTable dt = ReadExcel();

using (SchemaMapper SM = InitiateTestSchemaMapper("dbo","PasswordsTable"))
{

   bool result  = SM.ChangeTableStructure(ref dt);
   string con = @"Data Source=.\SQLINSTANCE;Initial Catalog=tempdb;integrated security=SSPI;";
 
   using (SchemaMapperDLL.Classes.Exporters.SqlServerExport exp = new SchemaMapperDLL.Classes.Exporters.SqlServerExport(con))
   {
       exp.CreateDestinationTable(SM);
       exp.InsertToSQLUsingStoredProcedure(SM, dtExcel);
   }

}

Change DataTable schema and insert into SQL using BULK Insert

DataTable dt = ReadExcel();

using (SchemaMapper SM = new SchemaMapper(Environment.CurrentDirectory + "\\SchemaMapper\\1.xml"))
{

   bool result  = SM.ChangeTableStructure(ref dt);
   string con = @"Data Source=.\SQLINSTANCE;Initial Catalog=tempdb;integrated security=SSPI;";

   using (SchemaMapperDLL.Classes.Exporters.SqlServerExport exp = new SchemaMapperDLL.Classes.Exporters.SqlServerExport(con))
   {
       exp.CreateDestinationTable(SM );
       exp.InsertUsingSQLBulk(SM, dtExcel);
   }
   
}

Read SchemaMapper class from Saved XML

using (SchemaMapper SM = new SchemaMapper("Environment.CurrentDirectory + "\\SchemaMapper\\1.xml")){

   //write your code here

}