SAP Knowledge Base Article - Public

2087490 - Data Imports & Exports: How do I open my User Directory in Excel without losing leading zero's in userids or usernames? - Platform

Symptom

How do I open my User Directory in Excel without losing leading zero's in userids or usernames?

Environment

SAP SuccessFactors HXM Suite

Resolution

Many companies use an HRIS system to generate data files for import into SuccessFactors. These files often contain leading zeroes, maybe in your usernames, userids, or employee ID's. Opening a csv data file that contains leading zeroes with Excel will strip the zeroes by default.

Warning! Re-importing this data will cause duplicate users to be created in the SuccessFactors Application. For instance, if the userid for an employee is 000123, and you open your file in excel without maintaining leading zeros, the userid becomes 123. This is an entirety new user account, userid 123 is NOT the same as 000123, so if you were to re-import this file you may end up with 2 users 123 AND 000123. If there is already a valid active user 123 in your system, this import would likely overwrite the existing users information with the information from user 000123.

Note: Once a user record is in the system, admin have to either use the DRM/DRTM tool to purge these unwanted records to reuse those userids. 

  1. Open the *.csv file in Notepad and save the file in a format that isn't recognized by Excel (such as a *.txt file). (You may need to right click on the file and choose Open With > Notepad). If desired it may also be beneficial to associate *.csv files with Notepad instead of Excel.
     
  2. Open Excel, select the text file, then click Open
     
  3. The Excel text import wizard will appear
     
  4. Select Delimited, then click Next
     
  5. Select the check box for Comma, then click Next
     
  6. The following screen allows you to select the formatting you would like to apply to the columns of the document. In the document preview window, select the first column then scroll all the way to the right, hold down Shift and click on the last column header (all the columns will turn black). Then from the upper right choose the text radio button. Click Finish.
     
  7. Once you have finalized your edits in the user directory, in Excel save the file as a CSV (comma delimited).

    This will preserve your leading zero's!

    =============

    IT IS NOT RECOMMENDED THAT YOU OPEN ANY USER DATA FILE CONTAINING LEADING ZEROES!!
    This should be an automated process between your HRIS system and SF. Simply opening these files in Excel will wipe out the zeroes.

    In the event that you do have to make changes manually follow these procedures:
     
    1. Export the CSV file from SF Admin Center
       
    2. Save the file to your desktop
       
    3. Open Excel, DO NOT OPEN THE USER DATA FILE!!!
       
    4. Create a new blank workbook
       
    5. Click ‘Data’
       
    6. Hover over ‘Import External Data’
       
    7. Click ‘Import Data’. You want to import from a text file. Your‘Text Import Wizard’ may appear in some versions of excel.
       
    8. Verify that the ‘Delimited’ radio button is selected then click ‘Next’
       
    9.  Put a check mark in the box to the left of  ‘Comma’, since the delimiter in your file are commas.
       
    10. Click ‘Next’
       
    11. The first column should already be highlighted as seen in the example to the right (If it is not highlighted then click once on the column header labeled ‘General’ over the first column.)
       
    12. Using the horizontal scroll bar at the bottom, scroll all the way to the right.
       
    13.  Hold down the ‘Shift’ key and click the last column header. This should highlight all columns
       
    14. Click the ‘Text’ radio button. You should immediately see all column headers change to ‘Text’
       
    15. Click ‘Finish’
       
    16. Click ‘OK’

    17. Now you are able to make edits to the file
       
    18. When you have completed all updates you must save the file as a .CSV (Comma Separated Value) Refer to the next screenshot for a reference on how this is done:

Keywords

leading zeros, excel, userid, user, id , KBA , sf data import files , LOD-SF-PLT-UIM , Employee Import Issues , How To

Product

SAP SuccessFactors HCM Suite all versions