Troubleshooting
Problem
This document explains several data transfer switches available through a PC INI file called CWBTFR.INI.
Resolving The Problem
Important Note: This document discusses Client Access Express, IBM iSeries Access, and IBM i Access for Windows products. These names essentially refer to the same product; however, the functionality and name changed over the last several releases. For the purposes of this document, the terms Client Access Express, IBM iSeries Access, and IBM i Access for Windows may be used interchangeably. Where a difference is important, the version of the product is used to identify the differences.
This document describes the various data transfer options that are controlled by the CWBTFR.INI file. These options are typically only documented in an APAR or the readme.txt file of the appropriate IBM i Access for Windows service pack. If the file does not exist, the CWBTFR.INI file can be created using any simple text file editor. The file must reside in the folder where the Windows operating system was installed, which will depend on which Windows operating system is being used. For example, C:\Windows is the default folder on Windows XP and Windows 7.
Note: In a simple single user-at-a-time installation of Windows, the CWBTFR.INI file only exists in the Windows directory; however, in a multiple user installation such as terminal server edition or Citrix, there will be multiple copies of this file. Each user will have a directory, typically "C:\Documents And Settings\USERNAME\WINDOWS" where USERNAME is the actual Windows user ID. If the CWBTFR.INI file is not found in this location the first time an application tries to use it, the file will be copied from the "Windows" directory. From that time forward, the individual user's copy will be used so changes made to the copy of the file in the "Windows" directory made subsequent to the file being copied to the user's Windows directory will not have any effect for that user. Instead, all of the individual user copies of CWBTFR.INI would have to be updated in this multiple user environment.
CWBTFR.INI Switches
| TabsAreTabs=1 or 0; handles how tabs are translated using data transfer From V3R1 APAR SA60791: Conversion of 0x09 ASCII tab characters to 0x40 EBCDIC blanks is the default behavior of the Client Access and iSeries Access data transfer uploads from the PC to the IBM System i system. This is the equivalent to TabsAreTabs=0. Using TabsAreTabs=1 in the CWBTFR.INI file will allow 0x09 ASCII tab characters to be converted to 0x05 EBCDIC tab characters. Note: iSeries Access offers a check box option from the Data Transfer to System i system session to control the tab conversion. From the data transfer session go to the File drop down menu, select Properties, and then choose the option on the Conversion tab to Preserve tabs. | |
| UseFieldNames=1 or 0; handles whether field names are used or alias names are used From V3R1 APAR SA59034: At one time the default behavior of the database server was to send down the alias instead of the field name. A provision was made to allow field names to be viewed, while retaining the existing behavior for consistency. In order to activate the change to use actual field names instead of aliases, a new entry to the CWBTFR.INI file had to be added as UseFieldNames=1. Otherwise the default value equivalent to UseFieldNames=0 would be used. Note: The iSeries Access default behavior is to use field names rather than aliases. iSeries Access Data Transfer download offers a check box option to control whether field names or aliases are used. This option is found under the File drop down menu in the data transfer session, select Properties, go to the Display tab, and check the option to Display field alias instead of field name. | |
| ForceTranslation=1 or 0; handles whether CCSID 65535 data will be converted From V3R1 APAR SA55521: Data transfer allows conversion from iSeries CCSID 65535 to the PC CCSID on transfers to the PC. The OS/400 job CCSID will be used for the conversion. Data transfer will also convert from the PC CCSID to the OS/400 job CCSID for transfers to the i5 or iSeries when fields in the iSeries file are tagged with the 65535 CCSID. Specifying ForceTranslation=1 in the CWBTFR.INI file will force the conversion of CCSID 65535. The default setting is equivalent to ForceTranslation=0, which will result in hexadecimal data in the PC file after the data is downloaded to the PC. Note: A switch was added to the data transfer File drop down menu by selecting Properties and then the Conversion tab to Convert CCSID 65535. The default value is the same as ForceTranslation=0, which does not convert CCSID 65535 data. Also note that adding ForceTranslation=1 to the CWBTFR.INI file will normally change the default setting on the Properties Conversion tab so that Convert CCSID 65535 is checked for all transfers. | |
| AlternateEOL=1 or 0; handles what EOL markers are allowed FromV5R1 APAR SE19871: A change was made to not accept EOL markers other than 0x0D0A to match the industry standard. A flag can be added to the CWBTFR.INI file to allow 0x0D0C to be allowed as an EOL marker. Adding AlternateEOL=1 to CWBTFR.INI will allow data transfer to upload files with an EOL of 0x0D0C. The default behavior would be equivalent to AlternateEOL=0 where only 0x0D0A is allowed as the EOL marker on files that will be uploaded to the iSeries using the data transfer function. | |
| BaseSheetName=custname ;controls the sheet names on Excel spreadsheets From V5R3 APAR SE23668: Some user applications require Microsoft Excel sheet names to be sheet1, sheet2, etc., however iSeries Access uses the file name as sheet names. An entry can be added to the CWBTFR.INI file to customize the sheet names to the desired names. The entry that can be added is BaseSheetName=custname where custname can be any string up to ten valid characters. Excel restricts and does not allow the use of characters \,/,?,[,] or *. Some considerations are: - By activating this feature it is possible to customize the name of the sheets of Excel files generated through the Data Transfer from iSeries GUI ONLY. - This feature is available ONLY for BIFF5, BIFF7 and BIFF8 formats. - Customized names can ONLY be ANSI strings. UNICODE strings are not supported. | |
| ExcelNumericFormatter=%.10G ;format of numeric data when using Excel file format From V5R3 APAR SE20069: Example: A System i file includes a CHAR(10) field that contains a value of 1234567890. The file is downloaded into an Excel (BIFF8) file. The file is opened with Excel, and the cell format for this field is changed from General to Numeric. The file is uploaded back to the System i system with the option checked to "Allow numeric data in character columns to be converted to character data". The resulting data in the operating system file is 1.23457E+0. The data is in scientific notation with the last 2 bytes truncated. This is working as designed; however, the conversion makes the data unusable in this scenario. When Data Transfer asks Excel for the contents of a cell whose format is a number, Excel returns it as a double. For 1234567890, Excel returns 1234567890.0000. Data Transfer then calls a Windows API to format the double as a character, and the result is 1.23457E+009. Because the host field is defined only as CHAR(10), the last 2 bytes are truncated. Even if the field in the operating system file was defined as CHAR(12), the scientific notation would still be used based on the format specification iSeries Access uses. The format specification chosen was done so based on user input. Unfortunately, it does not work for all users. In this example, using format specification ExcelNumericFormatter=%.10G allows the user to specify a format specification for formatting Excel Numbers (which Excel stores as Doubles) when used in conjunction with the "Allow numeric data in character columns to be converted to character data" option of iSeries Access for Windows Data Transfer to System i system. In addition, in this example the field on the host is defined as a CHAR(10) and the numbers in Excel range up to 10 digits (for example, 1234567890). Without this format specification, the numbers are converted to scientific notation with possible rounding and truncation. With this format specification, the numbers are converted to the form 1234567890. The format specification must be in the form: % flags width .precision {h | l | I64 | L} type The maximum length allowed is 20 characters. Only one format specification is allowed. If the Excel spreadsheet has a combination of different numerical value formats (for example, 123456, 1234.56, 123456.1234), an ideal format specification may not be possible. A tool, cwbtftstfmt.exe, is included with iSeries Access and can be used to test different format specifications. This feature was added in iSeries Access V5R3. | |
| TabsToSpacesInAddIn = 1 ;controls how tabs are handled when using the Excel add-in From V5R3 APAR SE27102: Starting in V5R3 service pack SI26600, a new option called TabsToSpacesInAddIn is now supported in the CWBTFR.INI file to allow users to choose whether to convert tab characters to spaces or to preserve the tab characters when using the Excel data transfer add-in. By activating this option, the Excel Add-in download function converts tab characters to spaces. The option is activated in the CWBTFR.INI file (plain text). The file must reside in the Windows directory (which may be c:\windows, c:\winnt, and so on, depending on what version of Windows is installed and how it is installed), and it must contain a section like the following: [Client Access Data Transfer] TabsToSpacesInAddIn = 1 ;controls how tabs are handled when using the Excel add-in The default behavior reverts to leaving tabs as tabs on the Excel add-in. This was changed starting with iSeries Access V5R3. Prior to V5R3 the add-in preserved tabs on downloads just as the data transfer GUI version presently does for xls downloads. DefaultDateFmt From V5R4 APAR SE32745: Use the following option to specify a default date format. It affects the upload and download functions of CWBTF.EXE,RTOPCB.EXE, RFROMPCB.EXE, RXFERPCB.EXE, and the Excel Add-in. The following date indicators are supported: 1 = MDY, 2 = DMY, 3 = YMD, 4 = Julian, 5 = ISO, 6 = USA, 7 = EUR and 8 = JIS NOTE: The date format stored in a DTT or DTF format overrides this setting. [Client Access Data Transfer] DefaultDateFmt = 6 This specifies the default date format as USA. | |
| UseStandardFDF By default, the 'Data Transfer From' application creates enhanced FDF files. If a standard FDF file is required, this must manually be specified each time a transfer request is created. A new CWBTFR.INI switch to customize the default FDF format has been added in V5R4 service pack SI32972 with APAR SE35138. [Client Access Data Transfer] UseStandardFDF=1 This option makes Data Transfer create a standard FDF by default instead of a Enhanced FDF. It applies to the Data Transfer From GUI only. IASP An enhancement has been made to iSeries Access for Windows data transfer that enables you to specify a default IASP for each system connection that you have defined. This enhancement was added starting with V5R4 Service Pack SI27741. The new feature requires that you add a new section to the ini file and then add an entry, one per defined connection, that specifies the default IASP for that connection. An example of the new entries for this file looks similar to the following: [IASP] <iSeriesName>=<RDB entry name> //example of format to use RCHASK50=non_switch 1.2.3.4=iasp_test RCHASCLC=Backup The IASP name in the entries are not case-sensitive and are the relational database name, which may be the same as the IASP name, but could also be a defined with a different name when the IASP was created. Using System i command WRKCFGSTS *DEV *ASP and working with the description on the desired IASP will show the relational database name. If no entry exists in the ini file for a system, *SYSBAS is used. If you have two IASPs that you regularly use and want to avoid the bother of moving backward in the wizard or having to remember to change the database property, you can use this new feature to make a second definition for your system, with a different default IASP. For example, there is a system, RCHASBOX, with IP address 9.9.9.9. Define the system two times in iSeries Navigator. One time, use the name DNS recognizes and one time use the IP address. You could also define the system with more names and define the IP address in the system connection properties in iSeries Navigator. Then, add IASP entries to the cwbtfr.ini file to define default IASPs for these connections: [IASP] RCHASBOX=IASP1 9.9.9.9=IASP2 When you want to transfer data to or from a file in IASP1, you set the system connection to RCHASBOX. Likewise if you want to use IASP2, you set the connection to 9.9.9.9 which resolves to the same physical system but uses a different IASP. Additional information can be found in Technote Data Transfer Enhancement to Define Default Initial IASP. UseDelimitedNames=0 or 1 ;Controls whether field names are returned with quotes when using a SELECT AS clause A change was made starting with V5R4 iSeries Access data transfer where the use of double quotes around the SELECT AS field name ends up in Excel with the field name retaining the double quotes. In V5R3 and previous versions of iSeries Access data transfer, the double quotes were dropped. On IBM i, when you deal with names that contain embedded blanks for special characters, the name must be enclosed in double quotes. For example, run this query on IBM i or with data transfer, and you will get an error: SELECT lstnam AS Customer Name FROM qiws/qcustcdt Run the same statement with the double quotes and it will work correctly: SELECT lstnam AS "Customer Name" FROM qiws/qcustcdt Customer Name is a delimited name. Prior to V5R4 iSeries Access, Data Transfer stripped the quotes off before displaying the data in Excel. V5R4 iSeries Access purposely left the quotes on the field names because of the general change that was made to all the database clients to be more consistent in how they handle delimited names. The reason for this change was for consistency. In order to provide a way to return to the V5R3 data transfer behavior, a option has been added to configure the desired behavior. The new option is as follows: UseDelimitedNames= 0 - Return names without the double quotes (v5r3m0 behavior) 1 - Return names with double-quote delimiters if needed (default option / v5r4m0 behavior) This cwbtfr.ini option was added to V5R4 iSeries Access service pack SI35287. Sample CWBTFR.INI File [Client Access Data Transfer] TabsAreTabs=1 UseFieldNames=1 ForceTranslation=1 AlternateEOL=1 BaseSheetName=custname ExcelNumericFormatter=%.10G TabsToSpacesInAddIn = 1 DefaultDateFmt = 6 UseStandardFDF=1 [IASP] RCHASK50=non_switch 1.2.3.4=iasp_test RCHASCLC=Backup | |
[{"Type":"MASTER","Line of Business":{"code":"LOB57","label":"Power"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SWG60","label":"IBM i"},"Platform":[{"code":"PF012","label":"IBM i"}],"Version":"6.1.0"}]
Historical Number
8775601
Was this topic helpful?
Document Information
Modified date:
18 December 2019
UID
nas8N1010114