Visokio website     Downloads     Video tutorials     KnowledgeBase  
Import: Numeric text/zip codes/IDs with leading zeros - Visokio Forums
Import: Numeric text/zip codes/IDs with leading zeros
  • VoteVote Up1Vote Down sara_llamasoft October 4, 2012 10:22AM
    Hi All -

    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
  • 1 Comment
  •     tjbate October 4, 2012 10:46AM
    Sara - We agree that offering only the all-or-nothing Parsing option to untick Auto-recognise numbers in the File import block (and consequently drop the leading zeros) can mean too much manual work re-typing the truly numeric columns in the incoming data set that (by option) were not converted on import.

    One improvement proposed is too continue to un-tick the default Parsing option and not automatically do the strong typing conversion on import, but to add a new option in Field Organiser to automatically re-type the already-imported (as text-only) fields, i.e. all fileds would be imported from the spreadsheet source file only as Text, but would be automatically be re-typed in the first Field Organiser in the data flow.

    Software will never really be able to distinguish data like Part numbers or Zip Codes from true integer numbers without human intervension, but this way, only the truly mis-typed zip codes and IDs would need to be manually re-typed as Text (and separators removed) inside the first Field Organiser block. This would ensure that leading zeros in Text survived the import process intact.

Welcome!

It looks like you're new here. If you want to get involved, click one of these buttons!

Sign In Apply for Membership