How Can We Help?
Background
The Import Store Procedure from Excel Operation allows data transfer from an Excel file into a database table via a store procedure.
Steps
- Create an Excel file which has column names the same as in the store procedure input parameters.
- Run the integration on a command button and select the store procedure which you created. While selecting the input mappings, please check the box for only excel file and leave all other store procedure input parameters unchecked. Map only Excel file to a File Attachment control on the form.
Here is what the Excel file looks like:
Figure 1: Excel file with the Column names that correspond to the Store Procedure’s input parameters.
Here is an example of the store procedure:
CREATE PROCEDURE [dbo].[66QA_Get_66_NumberNum]
@Num as numeric,
@Num2 as numeric
AS
BEGIN
Insert into [Table_NumberNum] (Col_Num, Col_Num2) values (@Num, @Num2)
END
Here are the controls in the activityA specific step in a process. It will be the user interface for that step (web form or a screen), for example, the Leave Application (screen interface or web form) will be the first Activity in the Leave Requisition process. The Leave Application Authorization will be the second step or Activity in the Leave Requisition process. One Process can have multiple activities.:
Figure 2: Command Option in the form that contains the SQL Connector Integration and the Attachment Control (i.e. HY9060ExcelFile) used to attach the Excel file in XMWorkspace.
Below are the integration steps in the command option HY9050Object:
Figure 3: Select one of the Integration operations. To go here, right click command option HY050Object, and select XMPro Integration. Click Edit, then Next.
Figure 4: Select a particular Integration to step through.
Figure 5: Select the Connection details as the initializing parameter for the SQL Connector Integration. The connection can be found in the Custom Connections section of the site settings.
Figure 6: List of all SQL objects (including the tables, views, store procedures, etc) in the selected database which the Connection detail is pointing to. Select the store procedure created initially.
Figure 7: Select the SQL operation, in this case Import Stored Procedure from Excel.
Figure 8: Check the ExcelFile as the Input Properties for the Import Store Procedure from Excel operation. Leave the other input properties (i.e. Num, Num2).
Figure 9: Map the Input Properties selected previously to applicable controls in the form. New controls can be created automatically if no controls are suitable.
Figure 10: Save the Integration
Here is the end result in the database table:
Figure 11: The outcome of the Integration. Check that the table has rows that are inserted from the Excel file.
Comments are closed.