locked
Incorrect zip codes in CSV file RRS feed

  • Question

  • I'm trying to reconcile using CSV files to import data. The lead data includes east coast Zip Codes with a preceding zero that gets stripped out when the excel file is saved as a CSV. It seems impractical with large data imports to hand enter the preceding zero after the import.


    Eric Hula - CRM Administrator

    Monday, May 14, 2012 6:52 PM

Answers

  • I am using the default field. I was inspired by your experiment and ran the same in my sandbox org. It came in perfect just like you said. I found the problem (I think). I was given a CSV file for the data import. Even though I changed the zip code column into a text field, it failed to make the change while in CSV format.  These steps successfully resolved it.

    1. Save the CSV file as an XLS
    2. Change the zip code column to Text
    3. Manually add the preceding zeros
        a. if there is a way to add the zeros all at the same time, I would love to know!
    4. Save the XLS file
    5. This time use Save As to convert back to CSV
    6. Import into CRM

    Thanks for giving me the inspiration to find the problem!


    Eric Hula - CRM Administrator

    • Marked as answer by Eric Hula Monday, May 14, 2012 9:23 PM
    Monday, May 14, 2012 9:22 PM

All replies

  • The problem is not related to CRM, but to the way Excel enteprets your csv file.

    Instead of just opening the CSV file as an excel file.. try open excel and importing the csv file.. then stating that the zip code column is a text area rather than a number.


    Rune Daub Senior Consultant - Dynateam CRM http://www.xrmmanagement.com

    • Proposed as answer by RuneDaub Monday, May 14, 2012 7:01 PM
    • Unproposed as answer by Eric Hula Monday, May 14, 2012 7:24 PM
    Monday, May 14, 2012 7:01 PM
  • I may not have effectively communicated the problem. I will try to clarify. If you open the file in a text editor, the preceding zeros are there. When I import the CSV file into CRM, it's not Excel that is stripping out the zero, it's CRM itself. I mentioned Excel because it has the same behavior. I apologize for the confusion.

    Eric Hula - CRM Administrator

    Monday, May 14, 2012 7:57 PM
  • Are you using the default zip code field? because that is a nChar field and should not strip 0's.

    I have imported multiple times to this field with proceeding 0's and never lost one yet.. It is treated as a text string if you import to default CRM Zip field.


    Rune Daub Senior Consultant - Dynateam CRM http://www.xrmmanagement.com

    Monday, May 14, 2012 8:03 PM
  • I am using the default field. I was inspired by your experiment and ran the same in my sandbox org. It came in perfect just like you said. I found the problem (I think). I was given a CSV file for the data import. Even though I changed the zip code column into a text field, it failed to make the change while in CSV format.  These steps successfully resolved it.

    1. Save the CSV file as an XLS
    2. Change the zip code column to Text
    3. Manually add the preceding zeros
        a. if there is a way to add the zeros all at the same time, I would love to know!
    4. Save the XLS file
    5. This time use Save As to convert back to CSV
    6. Import into CRM

    Thanks for giving me the inspiration to find the problem!


    Eric Hula - CRM Administrator

    • Marked as answer by Eric Hula Monday, May 14, 2012 9:23 PM
    Monday, May 14, 2012 9:22 PM