5

Our customer ID field is alphanumeric and permits leading zeros. When we export reports to CSV and then open them in Excel the leading zero is removed. Is there any way to get Salesforce to prefix this field with an apostrophe or other mark that would designate the data as text and prevent Excel from automatically removing the leading zeros?

Edit: Salesforce addresses the issue with a workaround that I would rather avoid, if possible.

Edit 2: This field is encrypted and cannot be used in formulas.

  • Your CustomerID field is encrypted. Wow. – Joel Mansford Sep 4 '12 at 21:46
  • It's covered under FERPA (it's really a student ID.) – Mike Chale Sep 5 '12 at 11:02
6

I know when opening a CSV in LibreOffice Calc you can select a column in the preview pane and change its format to "Text". Upon opening the leading zeros appear correctly for any columns you carry that operation out on. I would have assumed Excel has similar functionality.

LibreOffice Calc - Text Import (CSV Open) - Setting column type

2

I had go with the suggestion from @Jon Hazan above, if you must open this CSV in excel for a purpose. However, if you can try to work this data without having to Open in Excel, much better as Excel tends to apply its own transformation when opening files and interpreting values.

The other option if you must use Excel, I would recommend you do a File --> Import instead and let the wizard treat your desired ID values as Text and you can retain your values as you need them. Hope this helps.

1

You could try creating a new Formula field (Type: Text) on your Customer Object that has a value of a single quote prepended to the Customer Id. Then use this field in your report that your exporting:

"'" & Id

or

"'" & CustomerId__c
  • I hadn't thought of that, but our privacy office has mandated this field be encrypted and encrypted fields cannot be used in formulas. I have edited my question to reflect this. – Mike Chale Sep 4 '12 at 15:04
  • Damn, I'm afraid I don't really have a solution in that case. Hopefully someone can come up with one but I guess you're probably going to have to do something external to salesforce. – Jon Hazan Sep 4 '12 at 16:11
1

if you know the field has leading zeros, then you can format that in Excel with 000000 as the format string (depending on how many zero's a value should have).

If there's not a fixed number of characters for the column, then this won't work

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.