[ARCHIVED] Number Format on Data Portal Exports

[roberrol]

I am trying to use the exports from Canvas Data Portal to produce rudimentary statistics, charts and graphs in Tableau. I can export the file, open and convert in Excel, and import in Tableau no problem. However, only part of the data is really usable because of the format it is in. Text responses are no problem (i.e., active versus deleted, etc). Dates are no problem because Excel format "Short Date" works perfect. What I am struggling with are the number formats for course_id, enrollment_term_id, etc. They all are listed as 1.53E+16, an exponent. My question is this: what number format makes the course_id, enrollment_id, etc. actually usable? Is there an exact conversion in Excel? I know how to do custom number formats. Also, is there a scheme doc anywhere that explains the data not just the definition (like the one in Canvas Data Portal)? Thanks. 

Royce 

[James · Accepted solution]

roberrol 

Those are big integers. Excel is showing it in scientific notation because that's how it handles floating point numbers that are really big. If you click on a cell, the full number should show in the formula bar.

The problem is that isn't the correct full number. The actual number was -576447194681847915

There is a definite problem with these big integers using Tableau as well. I wrote an analysis of it here: Use of Tableau Prep and ID fields 

The short version is that if your program cannot handle them as large numbers, then you should treat them as text.