How to migrate Microsoft excel data to MS SQL database in MII 12.0
In one of our requirements,we needed to migrate huge data from Microsoft excel to MS SQL database.One single excel sheet data needed to insert to many tables of MS SQL.
Microsoft excel and MS SQL.
- Creating a Microsoft Excel SAP xMII UDS Instance.
Creating an Excel SAP xMII UDS Instance from SAP MII.
Requirement is to migrate data from Microsoft excel sheet to MS SQL data base. One excel sheet data to be migrated to more than one tables of SQL. Here I would like to illustrate this with a small example.Suppose there are three rows and many columns in the excel which will be migrated to two tables.
This section gives step by step approach on how to do data migration from Microsoft excel to MS SQL database in MII.
Here are the Steps:
Step 1: Working on Microsoft Excel
1. Select all the rows and do unhide by right click on the excel. Select all the columns and do unhide.
2. Save the excel sheet.
Step 2: Creating a Microsoft Excel SAP xMII UDS Instance
1. Go to Start->Programs->SAP xMII->UDS->Admin Console.
2. From the SAP xMII UDS Admin Console, select File and choose Add.New window appears to create the UDS instance.
3. Enter the instance name.The name can not contains the spaces.
4. Select a Server type from dropdown list.
5. Enter a Port no and choose OK.
6. SAP xMII UDS instance is created with default parameters.
7. Select the appropriate SAP xMII UDS instance created before.e.g. Here I have created TestExcel.
8. Check the Runtime Mode. It should be Exe.
9. Ensure that the Threading Model in the SAP xMII UDS Admin Console is set to STA. Else we will face problem to migrate data from excel.
10. From Settings, click on Connection String.
11. Select Microsoft Jet 4.0 OLE DB Provider on the Provider tab.
12. Go to Advanced tab, select Read/Write from Access Permissions.
13. Choose “ALL” tab. Double-click the Extended Properties name.
14. Enter the value “Excel 8.0; HDR=NO” and choose OK.If HDR=YES, MII can read only 64 characters and it will eliminate the characters which is more than 64.Dot (.) will be replaced by pound sign (#).
15. Go to Connection tab, select the excel file which we will migrate and click on Test Connection.
16. If everything is set up properly, a Test Connection Succeeded message appears.
17. Choose Save on the SAP xMII UDS Admin Console.
18. Start the UDS instance.
19. Now the excel file is locked and ready for migration.
20. To change anything in excel, we need to stop the UDS instance.
Step 3: Creating an Excel SAP xMII UDS Instance from SAP MII
1. In order to use the SAP xMII UDS instance from SAP MII, it must be created in SAP MII as well.
2. Go to Data Services | Data Servers.
3. Clear the Show Only Enabled Servers check box.
4. Select OLEDBConnector in the list on the left.
5. Click Copy.
6. Enter a name for the data server. It does not have to be the same name as the UDS instance.
7. Select the Enabled check box.
8. Enter a pound sign (#) in the Date Prefix and Date Suffix fields.
9. Enter the IP address of the data server machine.
10. Enter the port number that was entered in the SAP xMII UDS Admin Console for the SAP xMII UDS instance.
11. Choose Save.
Step 4: Transaction flow
1. Create the transaction for requirement. Simple transaction is shown below for explanation.
2. Transaction has one SQL_Query_0 block. This will fetch data from the excel.
3. Write a SQL query only with [Param.1].Same query is configured for the SQL_Query_0 action block.
4. Map Param.1 to SQL query like this: “Select * From [TabName$firstCell:lastCell]” as shown in below figure.
5. Here A1 is the first cell and BO3 is the last cell of the excel.
6. Define three local variable called as TestName, insertistquery and insertsecqry.
7. ColumnRepeater action block is configured to repeat on the columns from query as shown in below figure.
8. Repeater_0 block is configured to repeat on the rows.
9. As the requirement is first two rows will be inserted two a single table and third row will be inserted to a different table. So Switch_0 action block has been used.
10. Click on Link of Switch_0 action block and linked InputValue to Repeater_0.Currentitem as shown in figure.
11. Assignment_0 block for assigning the local variable (TestName) to first row value when case is one.
12. qryInsertPropertyDefinitions block inserts the data to SQL table when case is two. Here we have linked the local variable (TestName) to appropriate parameter.
13. IsSuccess block contains check for insert query when case is three.
14. Assignment_2 block for assigning the local variable (insertistquery) incrementing by 1 if insertion is successed.
15. String_List_To_XML_Parser block is used to get the coma separated values in xml format.
16. Repeater_1 block is configured to repeat on the values from String_List_To_XML_Parser.
17. qryInsertMaterialClassPropertyGroupMap_Tdcp_0 block is configured to insert the data to another table.
18. IsSuccess_1 block contains check for insert query.
19. Assignment_1 block for assigning the local variable (insertsecqry) incrementing by 1 if insertion is successed.
20. transaction_call_0 block calls a transaction to insert error log message to a table if insertion is not successed.