Notifications
Clear all

Exported CSV has tab character prior to some values

1 Posts
1 Users
0 Reactions
874 Views
0
Topic starter

When I export my report to CSV, I notice that some of the values are prefixed with a tab character.  Is this a bug?  Why is this happening?

image
1 Answer
0
Topic starter

This is not a bug - it is intentional behavior.  

 

Our CSV report export is tailored primarily for Excel users.  Excel has some bad habits when it comes to displaying values which contain mostly numeric values and/or dates:

- Lengthy numeric values get converted to scientific notation.  This is a problem when you have master data String fields (for example Item Names) where the customer uses long, numeric values.  They get converted to scientific notation, which is incorrect from a user perspective

- Leading zeros are stripped.  Again, this is a problem when the field is actually a string field.

- Excel will sometimes try to interpret values as simple mathematical formulas when they contain - + and so on

 

To prevent this issue, Platform leverages another feature in Excel where CSV values preceded with a tab character will NOT do these kind of automatic conversions.  This has two advantages:

- The user sees the original values, not the nonsensical scientific notation and/or formula evaluation

- If the user imports the CSV, platform simply strips and ignores the leading tabs, so they do no harm there.  

 

So from and end-user perspective, the leading tabs are desirable.  Please note, sometimes Platform may place a tab before a value which doesn't actually need the leading tab, because we can't always determine with 100% certainty whether a value will be evaluated poorly by excel.  We have a set of regexes we apply to make a best guess, and sometimes it is overly aggressive.  However, this should again not be an issue since the end user will see the values properly in excel, and platform uploads will ignore the leading tab.

 

The only scenario we have encountered where this behavior causes issues is when someone is using the report export as a feed to another system, and that system can't strip the leading tabs.  If you have a scenario like this, there is an external reference to disable the formula injection protection at an Ent+Report or Report level.  HOWEVER - keep in mind, disabling this will cause the user-friendly behavior we described above to break, i.e. the users will start getting scientific  notation and other weirdness again.

 

EXTERNAL_REFERENCE:

REF_TYPE: PLT.DisableCsvFormulaInjectionForUIExport

EXTERNAL_VALUE: ReportName~EnterpriseName    or    EnterpriseName

LOCAL_VALUE: true  or  false

 

Description of setting:

If present, disable adding the tab character to String field value on UI export if the value - 

  • Contains all numeric and starts with 0 (because excel removes leading 0)
  • If it contains at least one number and at least one of {space, slash, dash}

    at any position (because excel might convert to date)

  • FORMULA_INJECTION (because excel tries to handle as a formula and combines values)