On 13-Oct-2015 16:08 -0600, gio.cot wrote:
I have an procedure (CLP program) that transfers a file PC excel to
As400
with client access using the FDF; I have a column
that can contain both Alphanumeric or only numerical values....
It happens that, if in this column have a value alone numerical,
example
88888997987987900, when it transfers him/it to As400 it transforms
him/it in8,8889E+16. Do you know how and if it can be obviated to
this problem?
The latter link may be most succinct and cleanest to refer-to
directly, specifically for the inquiry. The former may be of use more
generally, beyond the specific inquiry. The two Links, with both
snippets and reference information recorded for possible search tokens,
mostly included for if\when the links eventually go dead:
[
http://www.ibm.com/support/docview.wss?uid=nas8N1010114]
Reference #: N1010114
Historical Number: 8775601
Title: Undocumented Data Transfer Options: CWBTFR.INI
"Technote (troubleshooting)
Problem(Abstract)
This document explains several data transfer switches available through
a PC INI file called CWBTFR.INI.
...
CWBTFR.INI Switches:
...
ExcelNumericFormatter=%.10G ;format of numeric data when using Excel
file format
From APAR SE20069
[
ftp://public.dhe.ibm.com/as400/products/clientaccess/win32/v5r3m0/servicepack/si23492/readmesp.txt]:
CA400EXP-DTRANSFER EXCEL NUMERIC TO CHAR
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.
..."
[
http://www.ibm.com/support/docview.wss?uid=nas8N1015562]
Reference #: N1015562 Historical Number: 376612299
Title: Override E-Notation for Numeric Conversion in Character Columns
"Technote (troubleshooting)
Problem(Abstract)
This document explains a option for CWBTFR.INI that will allow Data
Transfer users to fine tune the formatting of numeric data that is
converted to character data using the "Data Transfer to iSeries"
property of "Allow Numeric Data in Character Columns to be Converted to
Character Data."
Resolving the problem
Summary
iSeries Access V5R3 APAR SE20069 introduced new function for Data
Transfer that is controlled with a CWBTFR.INI entry. The entry will look
similar to the following:
[Client Access Data Transfer]
ExcelNumericFormatter=%.10G
..."
midrange.com
