none
Column mapping while importing Excel to sql database table

    Question

  • Hi everyone,

    I request for you kind help.

    I am writing a class library for importing records from a excel or a csv file in to sql database columns. I have the logics to import the data from excel or csv to sql tables. But more on that, I would like to minimize the work for the end user who is using this library. So I would like to provide him feature for column mapping between the columns in the excel file and the sql tables. The user can select the column names of excel file and map it to the columns of sql tables and then the process can go ahead.

    The above description is the module plan. In this I am using the MVC patterns such as Models, Repositories for implementing all the database operations namely DML.

    NOTE : The user can pass any pattern to the ImportServiceClass which is being written for this import purpose.

    Here how can I implement the column mapping structure in Object Oriented Approach? I have designed few methods for implementing this and I can show you better with a neat code logic.

    MODEL:

        public class SADM_Users
        {
            [Key]
            public long SU_Id { get; set; }
            public int SU_Tenant_Id { get; set; }
            public int SU_BU_Id { get; set; }
            public int? SU_Role_Id { get; set; }
            public string SU_User_Code { get; set; }
            public string SU_User_Title { get; set; }
            public string SU_First_Name { get; set; }
            public string SU_Middle_Name { get; set; }
            public string SU_Last_Name { get; set; }
            public DateTime? SU_DOB { get; set; }
            public DateTime? SU_DOJ { get; set; }
            public long? SU_Primary_Position_Id { get; set; }
            public string SU_MasterDataRef { get; set; }
            public long? SU_Primary_Address { get; set; }
            public string SU_Mobile_No { get; set; }
            public string SU_Email_Id { get; set; }
            public string SU_Photo { get; set; }
            public string SU_User_Type { get; set; }
            public bool SU_Isactive { get; set; }
          
            public string SU_Status { get; set; }
          
        }

    Repository

        public class UserRepository : RepositoryBase<SADM_Users>, IUserRepository
        {
           
            public UserRepository(IDatabaseFactory databaseFactory)
                : base(databaseFactory)
            {
               
            }
    
        }
    
        public interface IUserRepository : IRepository<SADM_Users>
        {
    
        }

    DataImportService

        public interface IDataSyncService
        {
            void CanImportFile(string FilePath, DataSyncFileType FileType, out bool CanImport);
            void SetImportFile(string FilePath, DataSyncFileType FileType);
            void MapColumns(string[] TableColumns, string[] FileColumns);
            //void MapColumns(
            bool Import();
        }
    
        public class DataSyncService : IDataSyncService
        {
            private readonly IUserRepository userRepository;
            private readonly IDatabaseFactory databaseFactory;
    
            public DataSyncService()
            {
                databaseFactory = SharedService.GetDatabaseFactory();
                this.userRepository = new UserRepository(databaseFactory);// userRepository;
            }
    
            #region IDataSyncService Members
    
            private string FilePath
            { get; set; }
    
            private DataSyncFileType FileType
            { get; set; }
    
            private string[] MappingTableColumns
            { get; set; }
    
            private string[] MappingFileColumns
            { get; set; }
    
            [Description("Accepts the ImportFilePath As String and ImportFileType As DataSyncFileType and Sets CanImport As Boolean Out Parameter")]
            public void CanImportFile(string FilePath, DataSyncFileType FileType, out bool CanImport)
            {
                bool status = true;
    
                this.FilePath = FilePath;
                this.FileType = FileType;
    
                try
                {
                    if (!File.Exists(this.FilePath))
                        status = false;
    
                    if (this.FileType == DataSyncFileType.Excel)
                    {
                        switch (this.FileType)
                        {
                            case DataSyncFileType.Excel:
                                if (new FileInfo(this.FilePath).Extension != "xls")
                                    status = false;
                                break;
                            case DataSyncFileType.CSV:
                                if (new FileInfo(this.FilePath).Extension != "csv")
                                    status = false;
                                break;
                        }
                    }
                }
                catch (Exception e)
                {
                    throw e;
                }
                finally
                {
                    CanImport = status;
                }
            }
    
            [Description("Accepts the ImportFilePath As String and ImportFileType As DataSyncFileType")]
            public void SetImportFile(string FilePath, DataSyncFileType FileType)
            {
                this.FilePath = FilePath;
                this.FileType = FileType;
            }
    
            [Description("Accepts two ordered list of string arrays. TableColumns[] holds Database table field names and FileColumns[] holds the Excel (or) CSV file column names")]
            public void MapColumns(string[] TableColumns, string[] FileColumns)
            {
                this.MappingTableColumns = TableColumns;
                this.MappingFileColumns = FileColumns;
            }
    
            public bool Import()
            {
                return true;
            }
    
            #endregion
        }

    Yes, the above blocks are my codes. I can understand that I need to write this ServiceUtil in a new Repository for this purpose. Well the above DataSyncService is a calling class and just for understanding.

    Now kindly say me how can I map the entity dynamically to the DataSyncService Repository and map the Excel columns with the EntityModel property fields? to achieve this.

    Any help would be greatly appreciated. Thanx in advance.


    )-(aree

    Saturday, July 07, 2012 2:37 PM

All replies

  • I would encourage you to modify from this MVC sample.

    http://www.codeproject.com/Articles/42628/Using-ASP-NET-MVC-and-the-OpenXML-API-to-Stream-Ex

    chanmm


    chanmm

    Sunday, July 08, 2012 2:10 AM
  • Hi chanmm,

    Good morning. Thanx for your kind reply. Well, I would like to get an idea for making the DataImportService class for importing excel fields to database fields. Here I mean, DataImportService<T> where T : class some thing like this. If I pass the model class to this T Entity, and then by using the entity i would like to do my further operation like column mapping. The great deal here is dynamically perform the operation as the model entity item passed to it.

    And more in detail there may be UserMaster, ProductMaster models in the application. As I am using the models as same as the table names, I am just searching for such logic. Am I clear to you? :)

    Thanx for your interest. And also I appreciate this great work at Code project.


    )-(aree

    Monday, July 09, 2012 5:00 AM
  • I understand now I think  .. you want something like generic or dynamic kind of export I think.

    The closest I know till now is still go through a datatable and loop throught the datatable to export to Excel.

    Sorry for not giving you exactly what you wanted.

    cheers,

    chanmm

     

    chanmm

    Monday, July 09, 2012 6:24 AM
  • Hi everyone,

    Now I am fixed with a new solution. But still I need your help. Here I found a useful library LinqToExcel. This is what I am implementing in my code. see the sample below.

    I would like to discuss with you about my issue in this discussion. I am clear about this LINQ to excel the great deal from google code. well i need to know how to select only the mapped columns from the excel. Here I will explain you more.

    First_Name L_Name role Age
    A1 A2 R1 12
    B1 B2 R2 13
    D1 C2 R3 14

    The above table is sample User of excel file. The class model is as follows

        public class Users
        {
            public string FirstName { get; set; }
            public string LastName { get; set; }
            public string Role { get; set; }
            public int Age { get; set; }
        }

    In this I have added a mapping technique with a generic model as I am in need to develop this excel import for multiple models. Kindly go through this piece of code.

    public class ExcelDocumentReader
        {
            private ExcelQueryFactory Excel { get; set; }
            public string FileName { get; set; }
            public string SheetName { get; set; }
    
            [Description("Initializes the ExcelDocumentReader with the FileName.")]
            public ExcelDocumentReader(string FileName)
            {
                try
                {
                    if ((FileName != null) && (FileName != ""))
                    {
                        this.FileName = System.IO.Path.Combine(System.IO.Directory.GetCurrentDirectory(), @FileName);
                        this.Excel = new ExcelQueryFactory(this.FileName);
                    }
                    else
                        throw new Exception("FileName is Null or Empty");
                }
                catch (Exception ex)
                { throw ex; }
                finally
                { }
            }
    
            [Description("Gets all the Worksheet names.")]
            public IList<string> GetWorkSheets()
            {
                List<string> lstWorkSheets = new List<string>();
                try
                {
                    lstWorkSheets = this.Excel.GetWorksheetNames().ToList();
                }
                catch (Exception ex)
                { throw ex; }
                finally { }
    
                return lstWorkSheets;
            }
    
            [Description("Gets all the column names in the SheetName provided.")]
            public IList<string> GetColumnNames(string SheetName)
            {
                List<string> lstColumns = new List<string>();
                try
                {
                    lstColumns = this.Excel.GetColumnNames(SheetName).ToList();
                }
                catch (Exception ex)
                { throw ex; }
                finally { }
    
                return lstColumns;
            }
    
            [Description("Maps the excel column name with the entity field name for the Entity <T>.")]
            public IEnumerable<T> MapColumn<T>(string TableColumn, string ExcelColumn)
            {
                List<T> excelRows = new List<T>();
                try
                {
                    this.Excel.AddMapping(TableColumn, ExcelColumn);
                }
                catch (Exception ex)
                { throw ex; }
                finally
                { }
    
                return excelRows;
            }
    
            [Description("Read all the records for the entity <T> with the SheetName.")]
            public IEnumerable<T> ReadAll<T>(string SheetName)
            {
                List<T> excelRows = new List<T>();
                try
                {
                    this.SheetName = SheetName;
                    var rows = from sheet in this.Excel.Worksheet<T>(this.SheetName) select sheet;
                    excelRows = rows.ToList();
                }
                catch (Exception ex)
                { throw ex; }
                finally
                { }
    
                return excelRows;
            }
    }

    See the MapColumn<T> function. In this any model can be passed for mapping with the parametres TableColumns(i.e) entity fields and the Excel column headers. The deal here is I wish to select only the mapped columns alone.  for example

    I am calling with the following piece of code.

    MapColumn<T>(new string[] { "FirstName", "Role" }, new string[] { "role", "L_Name" });

    I just mapped the FirstName and of entity class properties with the role and L_Name of excel file. I need to select only these mapped columns passed in to a list. Here I am stuck with this.

    from sheet in this.Excel.Worksheet<T>(this.SheetName).Select( x => new { x.FirstName, x.Role });

    But this is a static approach. I don't know what are the columns passed for mapping into the function and also I am using it as a Generic one NOTE: MapColumns<T>(.....)

    Kindly help me. Any help would be greatly appreciated.


    )-(aree

    Tuesday, July 10, 2012 5:12 AM
  • Hi Hari,

    I will try to involve some senior engineer into this case. Thanks for the patience!

    Bob Shen [MSFT]
    MSDN Community Support | Feedback to us

    Thursday, July 12, 2012 4:38 AM
  • Hi Hari,

    I see someone has asked a similar question in the discussion section for the LinqToExcel project itself.  Perhaps you posted that question there also?  https://groups.google.com/forum/?fromgroups#!topic/linqtoexcel/UKL5ExBDXjA

    That would be the appropriate place to seek help with LinqToExcel, unless someone else in the community has run across the same concern.

    Thanks,

    Cathy Miller

    Monday, July 16, 2012 5:36 PM