http://forums.visokio.com/discussions/tagged/leading_zero/feed.rss Mon, 30 Oct 17 19:12:44 -0400 Tagged with leading_zero - Visokio Forums en-CA http://forums.visokio.com/discussion/1813/import-numeric-textzip-codesids-with-leading-zeros Thu, 04 Oct 2012 10:22:10 -0400 sara_llamasoft 1813@/discussions
I'm attaching a sample Excel file containing some US store locations and the associated IOK. This file contains eastern US locations and therefore some of the zip codes have leading zero, e.g. 01752. It's not appropriate to trim the leading zero(s). First, people just expect that the postal code will retain 5 digits. Without the leading zero, it just looks "funny". More importantly, the first three digits have meaning - they're geographical groupings and therefore also have meaning on their own. All 038's are in New Hampshire, etc. We sometimes build models based on the 3-digit zip, so we'd calculate this incorrectly if we lose the leading zeroes. In the IOK, you'll see my solution, which is to use the TEXTVALUE function to enforce 5 digits. Note that it doesn't matter the type in the Excel file (see the two zip code fields). Omniscope handles them identically whether Excel sees them as text or numbers.

It creates considerable additional work if I fall back on the source file input block and simply not type any fields. My issues would be solved for the zip code fields but then I'd have to re-type what would be many more fields back to their intended types.

It would be very useful to selectively type fields or have another way to say on a field-by-field basis, "I know this looks like a number, but treat it like text and don't trim or otherwise modify it".

This would also apply to order numbers or shipment numbers in other circumstances, which would also typically have a fixed-character format, so there's application beyond US postal codes.

Thanks for your consideration! - Sara
]]>