Import data from multiple files into one SQL table step by step guide

In this page, i will describe how to use SchemaMapper to import data from multiple files with different schemas into one SQL table.

In this example, the files contains the credentials of SQL Server instances saved by many users and the goal is to stored them all inside on table in a SQL database.

Source files

Flat file

• File extension: .txt
• Columns: server, user, pass
• FilePath: D:\SchemaMapperTest\Password_Test.txt

Excel file

• File extension: .xlsx
• Columns: SQL Instance, username, password, AddedBy
• FilePath: D:\SchemaMapperTest\Password_Test.xlsx

Access database

• File extension: .accdb
• Table name: Passwords
• Columns: ID, Server Name, Login, Password, AddedDate
• FilePath: D:\SchemaMapperTest\Password_Test.accdb

Expected SQL destination table

• Schema: dbo
• Table name: Passwords
• Columns:
  • [User_Name] nvarchar(255), [Password] nvarchar(255), [Server_Name] nvarchar(255)
  • [AddedDate] DateTime (contains current date)
  • [UserAndPassword] nvarchar(255) (concatenate user and password using a vertical bar |)

Initializing SchemaMapper class

To initialize the SchemaMapper class you should follow these steps:

1. Add SchemaMapperDLL as reference and import the SchemaMapping and Converters namespaces:

using SchemaMapperDLL.Classes.Converters;
using SchemaMapperDLL.Classes.SchemaMapping;

2. Create a SchemaMapper class and pass the Destination schema and table names as parameters:

SchemaMapper smPasswords = new SchemaMapper("dbo","Passwords");

3. Define destination Columns within the SchemaMapper class:

//Define Server_Name , User_Name, Password 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 AddedDate column and fill it with a fixed value = Date.Now
SchemaMapper_Column smAddedDate = new SchemaMapper_Column("AddedDate", SchemaMapper_Column.ColumnDataType.Date,DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
   
//Define UserAndPassword column with and expression = [User_Name] + '|' + [Password]
SchemaMapper_Column smUserPasswordCol = new SchemaMapper_Column("UserAndPassword",SchemaMapper_Column.ColumnDataType.Text,true,"[User_Name] + '|' + [Password]");

//Add columns to SchemaMapper
smPasswords.Columns.Add(smServerCol);
smPasswords.Columns.Add(smUserCol);
smPasswords.Columns.Add(smPassCol);
smPasswords.Columns.Add(smAddedDate);
smPasswords.Columns.Add(smUserPasswordCol);

4. Now, you should add all possible inputs for each destination columns

//Add all possible input Columns Names for each Column
smServerCol.MappedColumns.AddRange(new[] {"server","SQL Instance","Server Name"});
smUserCol.MappedColumns.AddRange(new[] { "username", "user", "Login"});
smPassCol.MappedColumns.AddRange(new[] { "Password","pass", "password" });

5. All unwanted columns should be added to the IgnoredColumns list

//Sys_SheetName and Sys_ExtraFields are an auto generated columns while reading Excel file
smPasswords.IgnoredColumns.AddRange(new[] { "ID", "AddedBy", "AddedDate", "Sys_Sheetname", "Sys_ExtraFields"});

Converting files into DataTables with unified schema

1. Now we should convert files into DataTable objects

//Excel file
DataTable dtExcel;
DataTable dtText;
DataTable dtAccess;

//Excel worksheet
using (SchemaMapperDLL.Classes.Converters.MsExcelImport smExcel = new SchemaMapperDLL.Classes.Converters.MsExcelImport(@"D:\SchemaMapperTest\Password_Test.xlsx"))
{
   //Read Excel
   smExcel.BuildConnectionString();
   var lst = smExcel.GetSheets();
   //Read only from the first worksheet and consider the first row as header
   dtExcel = smExcel.GetTableByName(lst.First(), true, 0);
}

//Flat file
using (SchemaMapperDLL.Classes.Converters.FlatFileImportTools smFlat = new SchemaMapperDLL.Classes.Converters.FlatFileImportTools(@"D:\SchemaMapperTest\Password_Test.txt",true,0))
{

   //Read flat file structure
   smFlat.BuildDataTableStructure();
   //Import data from flat file
   dtText = smFlat.FillDataTable();

}

//Access database

using (SchemaMapperDLL.Classes.Converters.MsAccessImport smAccess = new SchemaMapperDLL.Classes.Converters.MsAccessImport(@"D:\SchemaMapperTest\Password_Test.accdb"))
{

//Build connection string and retrieve Access metadata
smAccess.BuildConnectionString();
smAccess.getSchemaTable();
//Read data from Passwords table 
dtAccess = smAccess.GetTableByName("Passwords");
}

2. After reading data from files, we need to change the tables structure to match the destination table structure:

smPasswords.ChangeTableStructure(ref dtExcel);
smPasswords.ChangeTableStructure(ref dtText);
smPasswords.ChangeTableStructure(ref dtAccess );

Importing to SQL

1. First we have to create an SQL Server exporter class:

SchemaMapperDLL.Classes.Exporters.SqlServerExport exp = new SchemaMapperDLL.Classes.Exporters.SqlServerExport();

2. To create the Destination Table we used the following command:

string connectionstring = @"Data Source=.\SQLINSTANCE;Initial Catalog=tempdb;integrated security=SSPI;";
exp.CreateDestinationTable(smPasswords,con);

3. At the end, we have to insert data into SQL. There are two methods to achieve that:

Insert using Bulk insert method

exp.InsertUsingSQLBulk(smPasswords, dtExcel, connectionstring);
exp.InsertUsingSQLBulk(smPasswords, dtText, connectionstring);
exp.InsertUsingSQLBulk(smPasswords, dtAccess , connectionstring);

Insert using stored procedure with table variable parameter

exp.InsertToSQLUsingStoredProcedure(smPasswords, dtExcel, connectionstring);
exp.InsertToSQLUsingStoredProcedure(smPasswords, dtText, connectionstring);
exp.InsertToSQLUsingStoredProcedure(smPasswords, dtAccess , connectionstring);

Result