locked
Importing data in MDS through web UI RRS feed

  • Question

  • Hello all,

    I am working on a project where end-users are required to import .csv files into MDS. What I would like to have:

    1. I don't want end-users to go through forming packages via SSIS route, and export that package into SQL Studio. In short: I don't want users to brother about SQL server side

    2. I'd like users to browse for a local file via web interface, select the file, press "do" (or something) and the data is exported into MDS

    3. I'd like the new arriving data assigned a unique ID (code filled is filled by the unique ID)

    So far, I am having no luck. Is this really possible to import data into MDS using some kind of custom-made web interface (PHP for example?)

    Tuesday, February 1, 2011 10:44 AM

Answers

  • Hi Porfessor,

    you can execute these SQL commands (in PHP , .NET or other ...)

    http://blog.sqlauthority.com/2008/02/06/sql-server-import-csv-file-into-sql-server-using-bulk-insert-load-comma-delimited-file-into-sql-server/

     

    MM is right, you will have to import your data into staging tables :

    http://sqlblog.com/blogs/mds_team/archive/2010/02/10/staging-examples.aspx

    and then , you can start the staging process with the stor. proc.:

     

    EXEC mdm.udpStagingSweep @UserId=@UserId, @VersionId=@VersionId, @Process=1

     

    http://social.msdn.microsoft.com/Forums/en/sqlmds/thread/3edcde59-374a-4c73-953c-779bc7481163

     



    Xavier Averbouch - Avanade , FRANCE
    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    • Marked as answer by _Professor_ Monday, February 14, 2011 8:30 AM
    Wednesday, February 2, 2011 9:46 AM
  • Hi Porfessor

    the best MS way should still be the SSIS way, as a built and already configured SSIS package is very easy to use, the end user just has to double click on it and to click "Run"



    Xavier Averbouch - Avanade , FRANCE
    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    • Marked as answer by _Professor_ Monday, February 14, 2011 8:29 AM
    Thursday, February 3, 2011 12:45 PM
  • Hi Porfessor,

    in fact, I've already worked on Excel to MDS sample. Here is a sample code :

     

     private void btGetMDSData_Click(object sender, EventArgs e)
        {
          Cursor.Current = Cursors.WaitCursor;
          
          DisplayEntityMembers(c);
          Cursor.Current = Cursors.Default;
        }
    
        private void DisplayEntityMembers(ServiceClient c)
        {
          EntityMembers em = GetEntityMembers(c);
          if (em != null)
          {
            Member[] members = em.Members.ToArray();
            ((Excel.Range)this.Cells[2, 2]).Value2 = "MemberName";
            ((Excel.Range)this.Cells[2, 3]).Value2 = "AttributeName";
            ((Excel.Range)this.Cells[2, 4]).Value2 = "AttributeValue";
    
            for (int i = 0; i < members.Length; i++)
            {
    
              ((Excel.Range)this.Cells[i + 3, 2]).Value2 = members[i].MemberId.Name;
              int cptAtt = 0;
              foreach (CommonMDS_WS.ServiceReference1.Attribute att in members[i].Attributes)
              {
                ((Excel.Range)this.Cells[i + 3, cptAtt + 3]).Value2 = att.Identifier.Name.ToString();
                ((Excel.Range)this.Cells[i + 3, cptAtt + 4]).Value2 = att.Value != null ? att.Value.ToString() : "";
                cptAtt += members[i].Attributes.Count();
              }
            }
          }
        }
    
        private EntityMembers GetEntityMembers(ServiceClient c)
        {
          if (cbModels.SelectedItem != null && cbEntities.SelectedItem != null && cbVersions.SelectedItem != null)
          {
            EntityMembersGetCriteria emGetCrit = new EntityMembersGetCriteria();
            emGetCrit.ModelId = new Identifier() { Name = ((Identifier)cbModels.SelectedItem).Name };
            emGetCrit.EntityId = new Identifier() { Name = ((Identifier)cbEntities.SelectedItem).Name };
            emGetCrit.VersionId = new Identifier() { Name = ((Identifier)cbVersions.SelectedItem).Name };
            //ExportView[] ev = c.ExportViewListGet(new International(), out or);
            EntityMembersInformation emi = new EntityMembersInformation();
            emGetCrit.DisplayType = DisplayType.CodeName;
    
            EntityMembers em = c.EntityMembersGet(intl, emGetCrit, out emi, out or);
            return em;
          }
          else
            MessageBox.Show("Please select model, version and entity in comboboxes");
          return null;
        }
    

     

     



    Xavier Averbouch - Avanade , FRANCE
    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    • Marked as answer by _Professor_ Monday, February 14, 2011 8:29 AM
    Monday, February 7, 2011 9:09 AM

All replies

  • Hi

     It would be quite possible to make an PHP or ASPX site(ASPX has a file control you can use directly in the designer to upload files) that could help insert csv data into MDS.

    Much of the functionality you ask for is in the staging enviroment (unique id. etc)

    Just make an a website that takes the CSV file, and inserts it into the staging enviroment. HINT: INSERT INTO MDM.TblStgMember.

    Of course you have to google(bing) around, for the code snippets that do the varoius tasks you ask for.

    /mm

     


    MM
    Tuesday, February 1, 2011 1:00 PM
  • Hi Porfessor,

    you can execute these SQL commands (in PHP , .NET or other ...)

    http://blog.sqlauthority.com/2008/02/06/sql-server-import-csv-file-into-sql-server-using-bulk-insert-load-comma-delimited-file-into-sql-server/

     

    MM is right, you will have to import your data into staging tables :

    http://sqlblog.com/blogs/mds_team/archive/2010/02/10/staging-examples.aspx

    and then , you can start the staging process with the stor. proc.:

     

    EXEC mdm.udpStagingSweep @UserId=@UserId, @VersionId=@VersionId, @Process=1

     

    http://social.msdn.microsoft.com/Forums/en/sqlmds/thread/3edcde59-374a-4c73-953c-779bc7481163

     



    Xavier Averbouch - Avanade , FRANCE
    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    • Marked as answer by _Professor_ Monday, February 14, 2011 8:30 AM
    Wednesday, February 2, 2011 9:46 AM
  • Thanks guys for the replies..

    ...as it seems that customer requires us to use MS solution, so I have to go to ASP.NET way..I'd prefer to go for VB way as I am more familiar with it.

     

    First, I'll try to create a ASP.NET page where I can browse for EXCEL file (we need to use EXCEL, not CSV) and then run a query on it. I'll let you know how it goes..

    Thursday, February 3, 2011 12:29 PM
  • Hi Porfessor

    the best MS way should still be the SSIS way, as a built and already configured SSIS package is very easy to use, the end user just has to double click on it and to click "Run"



    Xavier Averbouch - Avanade , FRANCE
    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    • Marked as answer by _Professor_ Monday, February 14, 2011 8:29 AM
    Thursday, February 3, 2011 12:45 PM
  • Thanks xavave for your reply.

     

    Indeed, this is the easy way but it is client requirements not to bother his personal to go through package creation headache. They want to follow importing excel sheet into the MDM way (which contains only product information)

    Monday, February 7, 2011 7:40 AM
  • Hi Porfessor,

    in fact, I've already worked on Excel to MDS sample. Here is a sample code :

     

     private void btGetMDSData_Click(object sender, EventArgs e)
        {
          Cursor.Current = Cursors.WaitCursor;
          
          DisplayEntityMembers(c);
          Cursor.Current = Cursors.Default;
        }
    
        private void DisplayEntityMembers(ServiceClient c)
        {
          EntityMembers em = GetEntityMembers(c);
          if (em != null)
          {
            Member[] members = em.Members.ToArray();
            ((Excel.Range)this.Cells[2, 2]).Value2 = "MemberName";
            ((Excel.Range)this.Cells[2, 3]).Value2 = "AttributeName";
            ((Excel.Range)this.Cells[2, 4]).Value2 = "AttributeValue";
    
            for (int i = 0; i < members.Length; i++)
            {
    
              ((Excel.Range)this.Cells[i + 3, 2]).Value2 = members[i].MemberId.Name;
              int cptAtt = 0;
              foreach (CommonMDS_WS.ServiceReference1.Attribute att in members[i].Attributes)
              {
                ((Excel.Range)this.Cells[i + 3, cptAtt + 3]).Value2 = att.Identifier.Name.ToString();
                ((Excel.Range)this.Cells[i + 3, cptAtt + 4]).Value2 = att.Value != null ? att.Value.ToString() : "";
                cptAtt += members[i].Attributes.Count();
              }
            }
          }
        }
    
        private EntityMembers GetEntityMembers(ServiceClient c)
        {
          if (cbModels.SelectedItem != null && cbEntities.SelectedItem != null && cbVersions.SelectedItem != null)
          {
            EntityMembersGetCriteria emGetCrit = new EntityMembersGetCriteria();
            emGetCrit.ModelId = new Identifier() { Name = ((Identifier)cbModels.SelectedItem).Name };
            emGetCrit.EntityId = new Identifier() { Name = ((Identifier)cbEntities.SelectedItem).Name };
            emGetCrit.VersionId = new Identifier() { Name = ((Identifier)cbVersions.SelectedItem).Name };
            //ExportView[] ev = c.ExportViewListGet(new International(), out or);
            EntityMembersInformation emi = new EntityMembersInformation();
            emGetCrit.DisplayType = DisplayType.CodeName;
    
            EntityMembers em = c.EntityMembersGet(intl, emGetCrit, out emi, out or);
            return em;
          }
          else
            MessageBox.Show("Please select model, version and entity in comboboxes");
          return null;
        }
    

     

     



    Xavier Averbouch - Avanade , FRANCE
    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    • Marked as answer by _Professor_ Monday, February 14, 2011 8:29 AM
    Monday, February 7, 2011 9:09 AM
  • Hi Xavier,

    Any way I could get the spreasheet that get data from MDS into Excel and Vice versa?

    Thanks in advance. 

    Leeten

    Thursday, February 9, 2012 5:00 AM
  • hi Professor,

    From the MDS web UI we can insert the records only row by row there is no way to import the CSV file with a click from the UI.

    what you can do is to

    Populate the data into a staging table and run the system defined stored procedures to load the data into MDM.

      OR

    Copy the data from the CSV to the excel and Use the MDS add-in for excel and load the Data to MDM with a click.

    Before that  you should connect to the MDM from the excel.

    Please mark as answered OR mark as useful


    s dhwani

    • Proposed as answer by MartinIsti Monday, April 16, 2012 11:36 PM
    Thursday, February 9, 2012 5:43 AM