Rocket Software Homepage
Forum Home Forum Home > AccuTerm Knowledge Base (read only) > File Transfer
  New Posts New Posts RSS Feed - Excel numeric data in exp notation
  FAQ FAQ  Forum Search   Register Register  Login Login

The AccuTerm forum has moved. Go to community.rocketsoftware.com to register for the new Rocket forum.

Forum LockedExcel numeric data in exp notation

 Post Reply Post Reply
Author
Message
anonymous View Drop Down
Groupie
Groupie


Joined: January 15 2004
Location: United States
Status: Offline
Points: 46
Post Options Post Options   Thanks (0) Thanks(0)   Quote anonymous Quote  Post ReplyReply Direct Link To This Post Topic: Excel numeric data in exp notation
    Posted: July 07 2004 at 11:35am
When transferring info from PICK to Excel using FTD, a number column comes up with strange looking characters, i.e. 2.11112E+11 when the number on file is a 16-digit straight numeric field. When I change the format in Excel to "numbers," it drops the 1st zero digit, i.e. 0211111111111 becomes 211111111111.
Back to Top
PSchellenbach View Drop Down
Admin Group
Admin Group

Moderator

Joined: December 15 2003
Location: United States
Status: Offline
Points: 2150
Post Options Post Options   Thanks (0) Thanks(0)   Quote PSchellenbach Quote  Post ReplyReply Direct Link To This Post Posted: July 07 2004 at 11:46am
This is one of those annoying quirks of Excel - it always tries to convert numeric columns into Excel "general number" format. In this case it is converting to scientific notation - which is not what you want! I tested a couple of solutions with AccuTerm 2K2 release 5.2a. Older releases may not work the same so if your not running at least 5.2a, you might want to upgrade.

The Excel converter in release 5.2a examines (at least part of) the column data, and if it finds leading zeros, it forces Excel to import the column as text. If there are no leading zeros, Excel converts the data into its general number format, and if there are lots of digits, this will be in scientific notation. If you pre-format the column and save the workbook, you can export to the existing workbook file using the existing (saved) formatting. Select the column and Format Cells as "custom format". Set the format to #. Adjust the Alignment if desired. Delete all rows of the worksheet except the heading row(s) and save the workbook file. When using FTD, export to the existing workbook file, and specify a starting cell of A2 (of the already created and formatted worksheet). This will presere the formatting for the column and the new data will look normal. For example, use "c:\tmp\test.xls!!A2" for the target file name to export to existing workbook 'test.xls', storing the exported data beginning at cell A2.
Back to Top
 Post Reply Post Reply
  Share Topic   

Forum Jump Forum Permissions View Drop Down

Forum Software by Web Wiz Forums® version 12.03
Copyright ©2001-2019 Web Wiz Ltd.