3 Set Up Import/Export

Prior to processing any data, you must set up the system to import and export data.

This chapter contains these topics:

• Section 3.1, "Setting Up Import/Export,"

• Section 3.2, "Restricting a User to an IFS Folder and Setting Default CCSID,"

• Section 3.3, "Troubleshooting File Character Translation,"

• Section 3.4, "Troubleshooting Spreadsheet Formatting,"

3.1 Setting Up Import/Export

To use Import/Export features, you must have access to a shared folder in the IFS on your System i server. You can then map a network drive to access the shared IFS folder from a Windows PC.

Additionally, there are settings for Import/Export that you access from the User Display Preferences program (P00923) which make Import/Export more convenient to use.

This section includes the following tasks:

• To create an IFS folder using JD Edwards export programs

• To create an IFS folder using IBM commands

• To change the CCSID of the IFS folder

• To share an IFS folder using IBM iSeries Access for Windows

• To share an IFS folder using an IBM System i API

• To map a network drive on your Windows machine

• To use a shared folder on your own Windows machine

• To set up Import/Export preferences

3.1.1 Action Codes for Import/Export

Interactive programs normally use Action Codes such as I-Inquire, A-Add, C-Change, and D-Delete. When a program is enabled for Interactive Import, the system uses the Action Code F-Import and when a program is enabled for Interactive Export, the system uses the Action Code T-Export.

To determine if the program supports Action Codes for Import/Export review the 00/II or 00/IE UDCs. Beginning with JD Edwards release A9.3 Web Enablement, interactive programs which do not support Import/Export will not show the Import/Export Action Code Icons.

If you are not using Web Enablement, to review the Action Codes available for interactive Import or Export program use F24 from an interactive application program. From the Available Function/Options window, use F10 to call program P0031W and display a window showing active Action Codes.

3.1.2 Before You Begin

• JD Edwards World recommends that you review Action Code Security and the security on the IFS before making Import/Export available to users.

• Ensure that you have proper authority before creating an IFS folder.

• You must have a User Display Preferences record before you can set up Import/Export preferences.

To create an IFS folder using JD Edwards export programs

JD Edwards World recommends that you create or use a user folder in the /home folder on the IFS. Each user should have a folder under the /home folder that matches their User Profile Name.

JD Edwards World also recommends that you use this method, because you can restrict users to specific default folders as well as assign the Coded Character Set ID (CCSID) you want to the IFS folders. See Section 3.2, "Restricting a User to an IFS Folder and Setting Default CCSID" for more information.

See Section 4.1, "Exporting Data Interactively" for more information.

1. On Directory Search, enter a leading forward slash and the name of the folder in the Directory Path field.

   For example, enter /DIRECTORY.

2. Choose New Directory (F9).

   The Directory Search program creates the new folder.

Figure 3-1 Directory Search screen

Description of "Figure 3-1 Directory Search screen"

To create an IFS folder using IBM commands

JD Edwards World recommends that you create or use a user folder in the /home folder on the IFS. Each user should have a folder under the /home folder that matches their User Profile Name.

On the System i command line, enter CRTDIR DIR (<Directory Path>) DTAAUT(*RWX) OBJAUT(*ALL).

Replace <Directory Path> with the path and name of the IFS folder in single quotes. For example, you can enter CRTDIR DIR replacing (<Directory Path>) with ('/home/UserID').

Note:

The System i creates the IFS folder with data using the CCSID of the System i job. Usually this is an Extended Binary Coded Decimal Interchange Code (EBCDIC) CCSID such as 37. Because most computers, other than IBM System i and mainframes, use American Standard Code for Information Interchange (ASCII), you should change the folder CCSID attribute to an appropriate ASCII CCSID.

To change the CCSID of the IFS folder

On the System i command line, enter CHGATR OBJ(<Directory Path>) ATR(*CCSID) VALUE(437).

Replace <Directory Path> with the path and name of the IFS folder in single quotes. Replace 437, if desired, with another appropriate ASCII CCSID.

Note:

Users can create their own subfolders within the part of the folder structure to which they have access. The System i creates new subfolders using the CCSID of the System i job, or you can specify the CCSID on DREAM Writer Version ZJDE0001 for Form ID P00923T. See Section 3.2, "Restricting a User to an IFS Folder and Setting Default CCSID" for more information.

To share an IFS folder using IBM iSeries Access for Windows

You can create IFS folders with an EBCDIC or ASCII CCSID. Oracle highly recommends that you set up folders for Import/Export with ASCII CCSIDs. See Section 3.2, "Restricting a User to an IFS Folder and Setting Default CCSID" for more information.

1. Use iSeries Navigator to allow sharing for the new folder. Under the /home folder, use the following path under My Connections:

   <System i>|File Systems|Integrated File System|Root|Home|<UserID>

   Replace <System i> with the name or IP address of your System i and <UserID> with the name of the IFS folder.

2. Right click on the <UserID>folder.

3. Choose New Share from the Sharing menu.

4. On the General tab, change the Access to Read/Write.

   Figure 3-2 Create i5/OS NetServer File Share - Jded screen

   
   Description of "Figure 3-2 Create i5/OS NetServer File Share - Jded screen"
   
   

To share an IFS folder using an IBM System i API

If you do not have IBM iSeries Access for Windows installed on your system, you can share an IFS directory using a call to the Add File Server Share (QZLSADFS) API. See the following IBM iSeries NetServer System API reference for more details:

http://www-03.ibm.com/servers/eserver/iseries/netserver/apidoc.html#hdrzlsaddi

1. On the System i command line, enter CALL QZLSADFS and choose Command Entry Prompt (F4).

2. Enter the following parameters:

Parameter	Explanation
<UserID>	Share name. Use the name of the folder.
<Directory Path>	Path name. Specify the directory path in single quotes, such as '/home/UserID'
<Length>	Length of path name. Enter as X'99999999' replacing the number nine with the actual length of the path name in hexadecimal.
<CCSID>	CCSID Encoding of path name. Enter X'00000000' to use the job default. JD Edwards World recommends that you use IFS folders with an ASCII CCSID in the folder properties to eliminate the need to specify Text Conversion on file share properties. See Section 3.2, "Restricting a User to an IFS Folder and Setting Default CCSID" for more information.
<Text Description>	Text description. Enter the folder name.
<Permissions>	Permissions. Enter X'00000002' to allow read/write access.
<Maximum Users>	Maximum users. Enter X'FFFFFFFF' to specify no maximum.
<Error Code>	Error Code. Enter X'00000000'

Using the UserIDTest sample folder, the format for the call with parameters is as follows:

CALL PGM(QZLSADFS) PARM(USERIDTEST '/HOME/USERIDTEST' X'00000010' X'00000000' USERIDTEST X'00000002' X'FFFFFFFF' X'00000000')

You must have the IOSYSCFG special authority to perform this command.

To map a network drive on your Windows machine

To make Import/Export more convenient to use, each user can map a Windows drive to their IFS folder.

1. In Windows Explorer, from the Tools menu, choose Map Network Drive.

2. Enter or choose a letter in Drive field.

   In the following example, the letter Q is the Drive letter.

3. Enter the directory path for the IFS folder in the Folder field and press Finish.

   Use back slashes in Windows. In the following example, you can replace System i with the network machine name or IP Address of your AS/400, iSeries or System i5 server. \home\UserID is the directory path to your shared IFS folder.

   Note:

   You must share any folder that you specify in the directory path. You typically do not need to specify the Root folder in the directory path when mapping to a shared folder.

4. The system might prompt you to enter your System i User ID and password. When doing so, you might need to enter the network Domain or IP address where the System i resides to qualify the User ID. Enter the password in lowercase.

Figure 3-3 Map Network Drive screen

Description of "Figure 3-3 Map Network Drive screen"

To use a shared folder on your own Windows machine

The IFS supports the iSeries NetClient file system (QNTC) which allows access to data and objects that are stored on remote servers running System i NetServer. The remote server can be another user's Windows machine or your own Windows machine. This allows you to export data to and import data from your own hard drive. You access your hard drive through a shared folder name and your Windows machine name or IP Address.

The directory path can contain subfolders below your shared folder.

1. In Windows Explorer, create and share a folder for import/export files.

2. Ensure your permissions for this folder are for Read, Write, and Execute.

3. Allow *Public to have only the Read permission.

4. In the Path Name field on the Import/Export parameter screen specify the directory path as one of the following:

   • /QNTC/YourPCsComputerName/SharedFolderName

   • /QNTC/YourPCsIPAddress/SharedFolderName

5. Ensure your System i User ID and password match your Windows network User ID and password in order for this functionality to work.

To set up Import/Export preferences

Navigation

From Master Directory (G), choose Hidden Selection 27

From Advanced & Technical Operations (G9), choose Security & System Admin

From Security & System Administration (G94), choose Security Officer

From Security Officer (G9401), choose User Display Preferences

Setting up User Display Preferences can make using Import/Export more convenient. If you do not set up preferences for Import/Export for a user, the system uses default values.

1. On User Display Preferences, enter your user ID in the following field:

   • User ID

2. Choose Import/Export Preferences (F6).

   Figure 3-4 PC Import/Export Preferences screen

   
   Description of "Figure 3-4 PC Import/Export Preferences screen"
   
   

3. On Import/Export Preferences, complete the following fields:

   • Separator Character

   • IFS Path

   For example, you can enter an IFS path as /home/UserID.

Field	Explanation
Separator Character	The character that a spreadsheet, such as Excel will recognize as a data field separator when importing a Comma Separated Value's (CSV) file into a spreadsheet. The system imports each data field into a separate column. If left blank, the system enters a comma (,) as the default if the Decimal Format Character (ULDECF) Field on the User Display Preferences is a period (.). If the Decimal Format Character is comma, the system enters a semicolon (;). Screen-specific information The most common separator character in North America is comma (,). The Separator Character cannot be the same as the Decimal Format Character on User Display Preferences. For countries that use comma (,) as the Decimal Format Character, you can use a semicolon (;) for the Import/Export Separator Character.
IFS Path	The string defining the path to the import/export file on the Integrated File System. For Example: /home/UserID/ Screen-specific information If you use your own IFS folder, enter the IFS Path. You do not need to specify the ROOT folder in this path string. The system enters this path as the default on all Import/Export parameter windows. You can set the IFS Directory Restriction processing option for program P00923T to restrict users to this IFS folder.

3.2 Restricting a User to an IFS Folder and Setting Default CCSID

The functionality for Import/Export uses the root (/) file system on the IFS. Other products, such as Seagull Software or IBM WebSphere, might exist on the IFS which you do not want users to access. IBM recommends general users access the default folder /home. JD Edwards World recommends that you restrict users to the /home folder or a subfolder under /home.

To access a folder, users must have *RX (read, execute) access to all folders in the directory path. In addition, you should set the properties to share each folder in the path. JD Edwards World recommends that you set the root (/) and /home folders for *PUBLIC to *RX access. Then, set up a subfolder for each user under /home with full access (*RWX - read, write, execute) and exclude *PUBLIC. See Section 3.2.1, "IFS Security Example" for more information.

Note:

You must test your system to ensure that *RX access by *PUBLIC to the root (/) and /home folders does not affect the function of other software that exists on the IFS.

You can also set up folders that team members can share. In this case, you set up a sub-directory under /home with full access for all team members and exclude *PUBLIC access. You then set up subfolders under the team folder for each team member with full access. See Section 3.2.1, "IFS Security Example" for more information.

3.2.1 IFS Security Example

In the following example, all users have personal directories under /home or a sub-folder under /home. The administrator has *RWX access to all directories. You can restrict UserA1 and UserA2 to the /departmentA folder or the /home folder, depending on whether public access files reside in the/home folder. You can restrict User3, who is not part of a department, to the /user3 folder or the /home folder, depending on whether public access files reside in the /home folder.

Figure 3-5 Integrated File System

Description of "Figure 3-5 Integrated File System"

To restrict users to an IFS folder and set default CCSID

Navigation

From Master Directory (G), choose Hidden Selection 27

From Advanced & Technical Operations (G9), choose Run Time Setup

From Run Time Setup (G90), choose DREAM Writer

From DREAM Writer (G81), choose Versions List

You can make the IFS folder more secure by restricting user's access to certain IFS directories. If you do not restrict users to a particular IFS folder, they have access through JD Edwards World screens to all folders under the root (/) folder for which they have authority. Setting the IFS Directory Restriction processing option for Import/Export Preferences restricts users to their default folder or subfolders under their default folder, using JD Edwards World screens.

See Work with DREAM Writer in the JD Edwards World Technical Foundation Guide for more information about setting up DREAM Writers.

1. On Versions List, enter P00923T in the Form field and click Enter:

2. Enter 2 in the Option field next to the ZJDE0001 Version and click Enter.

3. On the DREAM Writer Menu window, enter 1 in the Option field next to Processing Option Value(s) and click Enter.

4. On Processing Options Revisions, enter Y in IFS Directory Restriction processing option.

5. Optionally, enter a CCSID in the IFS Folder Creation processing option.

   The system uses the CCSID when users create their own subfolders. You can enter either an EBCDIC or an ASCII CCSID. JD Edwards World recommends that you enter an ASCII CCSID such as 437 or 1252 in this processing option.

   The system creates folders with an ASCII CCSID folder property. The Export program uses the CCSID property on the IFS folder to determine what CCSID to use when creating files for the IFS.

Note:

If you do not set the IFS Folder Creation processing option, the system creates the IFS folder with data using the CCSID of the System i job. If you need to change the CCSID value, use the IBM CHGATR command.

Note:

Restricting users to their default folder does not change the security settings on the IFS folders. Use this restriction in conjunction with secure IFS folders.

3.3 Troubleshooting File Character Translation

The System i works with IFS folders in either of the following data formats:

• Extended Binary Coded Decimal Interchange Code (EBCDIC)

• American Standard Code for Information Interchange (ASCII)

Most non-IBM systems, including Windows workstations, use the ASCII format. To achieve correct file character translation between the System i and non-IBM systems, Oracle recommends you use the following method:

• Specify an ASCII character set in the IFS folder properties. The translation then occurs automatically when you write the file to the IFS.

If you do not use this method, the non-IBM system is unable to translate the data correctly and your data may not display accurately.

To avoid file character translation issues, perform the following:

• Follow the steps to create IFS folders with an ASCII character set. You do not need to specify Text Conversion on your IFS folder file shares. See "To create an IFS folder using JD Edwards export programs" and Section 3.2, "Restricting a User to an IFS Folder and Setting Default CCSID" for more information.

• Using the WRKUSRPRF command, ensure the users' IBM profile has the correct values in the Language ID and Coded Character Set ID fields. Non-Western languages should contain 65535 in the Coded Character Set ID field.

• Verify the Host code-page value on the emulator session is set properly. Host code page value is used to map EBCIDIC codes from the System i server to appropriate ANSI graphics on the PC. To display characters correctly on the emulator running on the PC, this value needs to be set based on the language used. Access the Communications menu from the emulator session, choose Configure. Use the arrow to display available code-page options. Once selected, click OK.

  Figure 3-6 Configure PC5250

  
  Description of "Figure 3-6 Configure PC5250"
  
  

• Ensure the keyboard is set correctly to enter language characters on an emulator screen. This menu can be found from Edit > Preference > Keyboard from the emulator session.

  Figure 3-7 Keyboard Operation screen

  
  Description of "Figure 3-7 Keyboard Operation screen"
  
  

• Using IBM APIs to produce an output PDF document thru the export process, users may be required to provide values in the Graphic Character and Code Page fields in the printer overrides for the version being submitted. An example, for a Russian user, would be 960 and 880 respectively.

• Using BIP APIs to produce an output PDF document thru the export/transformation process, the proper fonts need to be loaded onto the folder for the machine in which BIP API's are running. For more information, contact Oracle JD Edwards World Support.

• When exporting to an XML document, ensure the encoding in the XML document is correct. The encoding value is retrieved from UDC 00/EN based on the CCSID value of the ISF folder where the document will be placed. The UDC 00/EN contains the CCSID with its matching encoding value.

3.4 Troubleshooting Spreadsheet Formatting

CSV files contain only data and no formatting information. When you open a CSV file with a spreadsheet program, such as Microsoft Excel, the spreadsheet program might use an undesirable format to interpret the data. If the data contains account and business unit numbers, the spreadsheet program might format these with a numeric format rather than in a text format.

If the spreadsheet program does not interpret the data in a satisfactory manner, we recommend that you import the CSV file rather than opening the file by double clicking the file or using the spreadsheet program's file menu to open the file. Instead, open the spreadsheet program and use the Data Import function to specify the format of the data for the spreadsheet. An example follows.

3.4.1 Example

Following is an example of an account number formatting issue and the steps you can take to avoid this issue when importing a CSV file into a spreadsheet program, such as Microsoft Excel.

Alternatively, you can export data as an XML file and import data from the XML file into the spreadsheet.

A CSV file contains the information you export from the following screen:

Figure 3-8 T/B by Business Unit screen

Description of "Figure 3-8 T/B by Business Unit screen"

You open the CSV file containing the account information using a spreadsheet. The spreadsheet program processes the account number 10.5000 on the second detail line (line 10) in a numeric format as 10.5 and the account number 10.5050.40 (line 14) in a text format.

Figure 3-9 CSV File screen

Description of "Figure 3-9 CSV File screen"

To specify correct spreadsheet formatting

1. Open Excel and do not specify a file to open.

2. From the Data menu, choose Import External Data and then Import Data.

   Figure 3-10 Select Data Source screen

   
   Description of "Figure 3-10 Select Data Source screen"
   
   

3. On the Select Data Source window, choose the CSV file to import and choose Open.

   Figure 3-11 Text Import Wizard - Step 1 of 3 screen

   
   Description of "Figure 3-11 Text Import Wizard - Step 1 of 3 screen"
   
   

4. On Text Import Wizard - Step 1 of 3, choose the Delimited option and click Next.

   Figure 3-12 Text Import Wizard - 2 of 3 screen

   
   Description of "Figure 3-12 Text Import Wizard - 2 of 3 screen"
   
   

5. On Text Import Wizard - Step 2 of 3, if you are using a comma for the separator character, choose the Comma option under Delimiters. Ensure that the Tab option is clear and click Next.

   Figure 3-13 Text Import Wizard - Step 3 of 3 screen

   
   Description of "Figure 3-13 Text Import Wizard - Step 3 of 3 screen"
   
   

6. On Text Import Wizard - Step 3 of 3, scroll down the Data preview area until Excel recognizes the columns.

7. Select the column containing Account Number, choose the Text option in the Column data format area and click Finish.

   Figure 3-14 Import Data screen

   
   Description of "Figure 3-14 Import Data screen"
   
   

8. On Import Data, click OK.

   Excel imports the CSV file data into a new spreadsheet where all of the account numbers appear as text, rather than a mixture of text and numbers.

Figure 3-15 CSV File (Spreadsheet) screen

Description of "Figure 3-15 CSV File (Spreadsheet) screen"