SchemaMapper

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:

• Excel worksheets (.xls, .xlsx)
• Flat files (.csv, .txt)
• Access databases (.mdb, .accdb)
• Web pages (.htm, .html)
• JSON files (.json)
• XML files (.xml)
• Powerpoint presentations (.ppt, .pptx)
• Word documents (.doc, .docx)
• Relational databases (SQL Server, Oracle, MySQL, SQLite, SQL Server compact)

And it can export data into different destination types:

• Flat files (.csv, .txt)
• XML files (.xml)
• Relational databases (SQL Server, Oracle, MySQL)

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:

• Microsoft Office Interop libraries to import tables from Word and Powerpoint / Clean Excel files
• Json.Net library to import JSON
• HtmlAgilityPack to import tables from HTML
• Microsoft Access database engine to import data from Excel worksheets and Access databases.
• .NET framework 4.5.2
• MySQL .NET connector to import and export data to MYSQL databases
• Oracle Data Provider for .NET to import and export data to Oracle databases
• System.Data.SQLite to import data from SQLite databases
• SQL Server Compact 4.0 SP1 redistributable to import data from SQL Server CE databases

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:

• Converters: It reads data from external files into DataSet
• DataCleaners: Cleans files before importing
• SchemaMapping: Changes the imported data structure to a unified schema

• Exporters: export tables to external sources

Wiki

• Import data from multiple files into one SQL table step by step guide
• Storing SchemaMapper information into XML
• Using FileCleaner namespace to ignore reading additional empty cells in Excel

NuGet Package

• NuGet Gallery | SchemaMapper

External links

• Adding MySQL, SQLite, and Oracle support to SchemaMapper
• Announcing SchemaMapper a C# data integration class library

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

}