SAP Knowledge Base Article - Public

2074122 - Data Import & Export: How do I open my User Directory in Excel without losing leading zero's in userids, assignment IDs or usernames?

Symptom

  • Using Excel to preserve leading zero's in userids or usernames.
  • How do I open my User Directory in Excel without losing leading zero's in userids or usernames?
  • Recieve error: "User does not exist in the database" when updating while using Import Data tool.
  • Receive error in import: "The assignment ID in your import file is not consistent with the existing assignment ID in the system."

Environment

  • SAP SuccessFactors Learning Management System (LMS)
  • SAP SuccessFactors HXM Suite

Resolution

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

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:

Warning! Reimporting 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 entrirely new user account, userid 123 is NOT the same as 000123, so if you were to reimport this file you may end up with 2 users 123 AND 000123. Worse yet, 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 is CANNOT be deleted. There are NO exceptions to this. SuccessFactors does not offer the ability to delete user records once created. You should simply set the incorrect user to INACTIVE.

If the originating file is CSV:

  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) for reference later as this will preserve your leading zero's!
  8. Save and Import the excel file.

 If the originating file is an Excel file:

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

Keywords

SAP SF success factors learning LMS import data imports export exports excel xls xlsx .xls .xlsx leading zero zeroes zero's missing strip inavlid user does not exist in database , KBA , LOD-SF-LMS , Learning Management System , LOD-SF-PLT , Platform Foundational Capabilities , LOD-SF-LMS-ADM , System Admin, Global Variables, References , LOD-SF-PLT-UIM , Employee Import Issues , How To

Product

SAP SuccessFactors HCM Core all versions