Cara menggunakan iseries file to excel

Last modified: September 16, 2022

Table of Contents

• 1 Introduction
• 2 Prerequisites
• 3 Preparing the Data Structure, GUI and Modules
• 4 Preparing the Logic for Data Import
• 6 Manually Creating an Import Template
• 6.1 Importing an Excel File
• 7 Automatically Creating an Import Template via an Excel File
• 8 Exporting and Importing a Template
• 9 Read More
• How to import data from Excel in CodeIgniter?
• How do I import a CSV file into codeigniter?
• How do I import an Excel file into iSeries?
• How do I export data from CI?

1 Introduction

Adding large amounts of data to your application (for example, reference data or data from an external application) can be very time-consuming. In order to save time and effort, this process can be automated using the Excel Importer from the Mendix Marketplace. In this how-to, you will set up import templates and import data into your app using this module.

2 Prerequisites

Before starting this how-to, make sure you know how to do the following:

• Create domain models (see How to Create a Basic Data Layer)
• Create overview and detail pages (see How to Create Your First Two Overview and Detail Pages)
• Create menu items (see How to Set Up the Navigation Structure)
• Create microflows (see How to Trigger a Microflow From a Menu Item
• Add Marketplace content to your app (see How to Use Marketplace Content in Studio Pro)
• Secure your applications (see How to Create a Secure App)

3 Preparing the Data Structure, GUI and Modules

Before you can start importing data into your application, you first need to set up the data structure and GUI by following these these steps:

1. Create the following domain model:

   

   Create an enumeration for the OrderStatus attribute with the values of Open, Processing, and Complete.

   Configure the XLSFile object to inherit from the FileDocument object (for more information, see How to Work with Images and Files).

2. Create Overview and Detail pages to manage objects of the Customer and Order types.

3. Create menu items to access the Order and the Customer overview pages.

4. Download the Excel Importer and Mx Model Reflection modules from the Mendix Marketplace (available by clicking the shopping-cart icon in the upper right of Studio Pro).

5. Create menu items for the ExcelImportOverview and the MxObjects_Overview pages (these pages already exist in the _USE_ME folders of the downloaded modules).

6. Configure the Administrator user role to have the Configurator module role for the ExcelImporter module, and the ModelAdministrator module role for the Mx Model Reflection module.

4 Preparing the Logic for Data Import

Because an enumeration is used for the OrderStatus attribute, you need to create a microflow to determine the enumeration value of the attribute based on the input from the Excel file.

1. Create the following microflow and name it IVK_ParseStatus.

2. Set the Return value as follows:

   1 2 3 4

   if $Unformatted = 'open' then MyFirstModule.OrderStatus.Open else if $Unformatted = 'processing' then MyFirstModule.OrderStatus.Processing else if $Unformatted = 'complete' then MyFirstModule.OrderStatus.Complete else MyFirstModule.OrderStatus.Complete

3. Save the microflow.

In order to set up import templates for importing data, your application model metadata should be reflected in the client. This can be achieved by using the Mx Model Reflection module. In this section, you will learn how to do this by following these steps:

1. Click the play button (Run Locally) to start your application.

2. Click View App to open your application in the browser.

3. Log in as an Administrator.

4. Click on the menu item for the MxObjects_Overview in your navigation.

5. Select the module that contains the objects you want to use in your client by checking the box to the left of it – which in this case is MyFirstModule:

6. Click the button next to Synchronize all entities and microflows of checked modules on the left. Now the two objects and the parse microflow from the MyFirstModule module can be seen and used in the client.

6 Manually Creating an Import Template

Before you can import data from an Excel File into your application, you have to set up an import template. In this template, you will configure the objects to which the data should be mapped, if an object is associated to another object, from which row of the Excel file the import should start, and which columns should be imported.

In this section, you will import data from a simple Excel file example, which can be downloaded here:

Based on the structure of the file you want to import, you need to manually set up your template by following these steps:

1. Click the menu item for ExcelImportOverview in the navigation of your app’s home page.

2. Click New Template:

3. Give a name to the template.

4. Click the arrow next to the Mendix object box.

5. Double-click the Customer object:

6. Click the arrow next to the Reference to import objects box.

7. Select the MyFirstModule.Customer_XLSFile association. By setting the association to the XLS file, the XLS file is saved on disk and the imported data is linked to the source file.

8. Make sure Import Action is set to Synchronize objects:

9. In the Connect columns to attributes section, click New to create a mapping from the Excel sheet column to the proper Mendix attribute.

10. Add the column number that corresponds to the column number from the Excel file you want to map.

11. Define the Excel column header as the Caption value.

12. Select Attribute for the Type.

13. Click the arrow next to the Attribute box:

14. Double-click the Attribute to which you want to map the Excel value:

15. Repeat steps 9 to 14 above for each attribute of the Customer object.

    • For the mapping of attribute Name, set the Key value to Yes to prevent a customer from being duplicated.

16. You now need to create mappings for the Order object attributes. However, because the Order object is associated to the Customer object, the mapping setup will be slightly different. Follow these steps for each attribute of the Order object:

    1. Add the column number that corresponds to the column number from the Excel file you want to map.
    2. Define the Excel column header as the Caption value.
    3. Choose Reference for the type.
    4. Click the arrow next to the Reference box, where you can select the association over which the order is linked to the customer – which in this case should Order_Customer.
    5. Click the arrow next to the Attribute box and double-click the Attribute to which you want to map the Excel value.
    6. For the mapping of attribute Number, set the Key value to Yes, only for the associated object in order to prevent orders being duplicated.
    7. Click Save.

17. For the mapping of the OrderStatus attribute, the Excel file value needs to be parsed to an enumeration value. To achieve this, you can use the IVK_ParseStatus microflow (created in the Preparing the Logic for Data Import section above). Click the arrow next to the Parse with box and select the IVK_ParseStatus microflow:

18. Save the import template.

6.1 Importing an Excel File

Now that the template is set up, you can start importing data from an Excel file into your application. You can use the example file you downloaded in the Creating the Import Template section above.

Follow these steps to import the Excel file:

1. Click the menu item for ExcelImportOverview in the navigation of your app’s home page.

2. Go to the Import files tab and click New:

3. Select the template you just created.

4. Click Browse:

5. Select the example Excel file you downloaded and click Save.

6. Click the Excel file under Filename to select it, then click Import file:

7. Click OK when the import has finished.

7 Automatically Creating an Import Template via an Excel File

In the previous steps, you manually added all the columns to your import template. In this section, you will create the same Excel template in an automated way, which you can do this via the specific New template by excelfile button. You can use this same Excel file example:

Follow these steps to create the import template via the Excel file:

1. Click the menu item for ExcelImportOverview in the navigation of your app’s home page.

2. Click New template by excelfile:

3. Select the example Excel file you downloaded.

4. Define the Sheet nr, Header row nr, and Import from row nr:

5. Click Save & next. This will automatically create a row for every header of the Excel file:

6. Back in the top section of the page, click the arrow next to Mendix object and select the Customer object type:

7. Under Connect columns to attributes, click Connect matching attributes:

   This will automatically match attributes of the selected Mendix object that have the same name as the Caption.

8. Complete the template as you did in the Creating the Import Template section above.

8 Exporting and Importing a Template

Once you have completed an Excel template, you can export the template (for example, from a test environment) and import it (for example, into an acceptance environment). When exporting and importing a template, the exact template will be uploaded, which means that columns are generated, the Mendix object is selected, the attributes are connected, and the parse microflows are selected.

Follow these steps to export and import your template:

1. Click the menu item for ExcelImportOverview in the navigation of your app’s home page.

2. Click the Excel template you created in the Creating the Import Template section above to select it, then click Export template and save the file on your computer:

3. Import the file you just downloaded by clicking Import template, selecting the file, and clicking Import:

You have now imported a complete import template.

9 Read More

• Export XML Documents
• Import XML Documents
• Expose a Web Service

Feedback

Was this page helpful?

Glad to hear it! Thank you for your response.

Sorry to hear that. Please tell us how we can improve.

How to import data from Excel in CodeIgniter?

Import Data From Excel & CSV to mysql Using Codeigniter.

Download Codeigniter Latest..

Basic Configurations..

Download phpExcel Library..

Create Library..

Create Database With Table..

Setup Database Credentials..

Make New Controller..

Create model..

How do I import a CSV file into codeigniter?

Codeigniter 4 Import Excel/CSV File Data into MySQL Database Example.

Step 1: Download Codeigniter Project..

Step 2: Configure Error Handling..

Step 3: Generate User Table in Database..

Step 4: Connect to Database..

Step 5: Create Sample CSV File..

Step 6: Create Model..

Step 7: Set Up Controller..

Step 8: Set Up Routes..

How do I import an Excel file into iSeries?

To send a data file to the host system:.

Highlight the Microsoft Excel file (. XLS) in the left hand, client side window and right click. ... .

Select iSeries File Transfer from the pop-up menu..

The XLS File and the XLS Filename. OUTFILE will be parsed, the data file will be created and uploaded to the AS/400..

How do I export data from CI?

1 - Download & Install Codeigniter 4 framework. ... .

2 - Set Global Environment Variable value. ... .

3 - Create Mysql Table. ... .

4 - Create Database connection. ... .

5 - Download PHPSpreadsheet Library in Codeigniter 4. ... .

6 - Create Model Class. ... .

7 - Create Data Seeder Class for Fake data. ... .

8 - Create Controller Class..