public DataTable ReadExcel() { using (SchemaMapper.Converters.MsExcelImport smExcel = new SchemaMapper.Converters.MsExcelImport(@"G:\Passwords.xlsx")) { //Read Excel smExcel.BuildConnectionString(); var lst = smExcel.GetSheets(); DataTable dt = smExcel.GetDataTable(lst.First(), true, 0); return(dt); } }
private void Form1_Load(object sender, EventArgs e) { SchemaMapper.SchemaMapping.SchemaMapper smPasswords = new SchemaMapper.SchemaMapping.SchemaMapper("dbo", "Passwords"); //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); //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" }); //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" }); //Excel file DataTable dtExcel; DataTable dtText; DataTable dtAccess; //Excel worksheet using (SchemaMapper.Converters.MsExcelImport smExcel = new SchemaMapper.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.GetDataTable(lst.First(), true, 0); } //Flat file using (SchemaMapper.Converters.FlatFileImportTools smFlat = new SchemaMapper.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 (SchemaMapper.Converters.MsAccessImport smAccess = new SchemaMapper.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.GetDataTable("Passwords"); } smPasswords.ChangeTableStructure(ref dtExcel); smPasswords.ChangeTableStructure(ref dtText); smPasswords.ChangeTableStructure(ref dtAccess); string connectionstring = @"Data Source=vaio\dataserver;Initial Catalog=tempdb;integrated security=SSPI;"; SchemaMapper.Exporters.SqlServerExport expSQL = new SchemaMapper.Exporters.SqlServerExport(connectionstring); expSQL.CreateDestinationTable(smPasswords); expSQL.InsertUsingSQLBulk(smPasswords, dtExcel); expSQL.InsertUsingSQLBulk(smPasswords, dtText); expSQL.InsertUsingSQLBulk(smPasswords, dtAccess); }