Excel dashboard and IDs

Hi everyone,

My forms / cases hold phone numbers and devices serial number scanned from
barcodes. These values should be considered textual numbers, not plain integers.

HTML exports (used for Excel dashboards) expose these values properly : if the
phone numbers starts with 0, the 0 is in the export.

However, importing these values into Excel convert them to plain integers,
stripping leading zeros along the way. It is also an issue with serial numbers as some
of them are considered being integers and others are texts, which leads to issues
in Microsoft Access because of data type mismatch.

How do you usually import numeric ID / serial numbers into Excel ? Can you specify a
value type before hitting Excel ? Is there an option to add a apostrophe for the
values of a certain column so Excel consider them a textual value ? Did I miss an
obvious part of the documentation ?

Many thanks,

··· -- Charles Flèche mHealth Advisor Télécoms Sans Frontières http://www.tsfi.org Première Urgence - Aide Médicale Internationale http://www.pu-ami.org

Hi Charles,

Thanks for checking in. Are you checking the "Preserve Cell Formatting"
checkbox as outlined in step 18d here:
https://confluence.dimagi.com/display/commcarepublic/Tutorial%3A+Create+an+Excel+Dashboard
?

Let us know if this doesn’t work and we can investigate further.

Cheers,

Nate

··· On Tue, Jan 27, 2015 at 6:00 AM, Charles Flèche wrote:

Hi everyone,

My forms / cases hold phone numbers and devices serial number scanned from
barcodes. These values should be considered textual numbers, not plain
integers.

HTML exports (used for Excel dashboards) expose these values properly : if
the phone numbers starts with 0, the 0 is in the export.

However, importing these values into Excel convert them to plain integers,
stripping leading zeros along the way. It is also an issue with serial
numbers as some of them are considered being integers and others are texts,
which leads to issues in Microsoft Access because of data type mismatch.

How do you usually import numeric ID / serial numbers into Excel ? Can you
specify a value type before hitting Excel ? Is there an option to add a
apostrophe for the values of a certain column so Excel consider them a
textual value ? Did I miss an obvious part of the documentation ?

Many thanks,

Charles Flèche

mHealth Advisor

Télécoms Sans Frontières http://www.tsfi.org

Première Urgence - Aide Médicale Internationale http://www.pu-ami.org


You received this message because you are subscribed to the Google Groups
"commcare-users" group.
To unsubscribe from this group and stop receiving emails from it, send an
email to commcare-users+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.


Nate Haduch

Technical Support Analyst
Dimagi, Inc | 585 Massachusetts Ave | Suite 3 | Cambridge, MA 02139
web: http://www.dimagi.com/

Hi Charles,

I also wanted to mention that the Excel option “Text” for Cell Format has
been the key for interpreting these fields as string literals. We believe
this to work for the Excel Dashboard import as well, but do let us know if
you find it to be otherwise.

Cheers,

Nate

··· On Wed, Jan 28, 2015 at 4:05 PM, Nate Haduch wrote:

Hi Charles,

Thanks for checking in. Are you checking the "Preserve Cell Formatting"
checkbox as outlined in step 18d here:
https://confluence.dimagi.com/display/commcarepublic/Tutorial%3A+Create+an+Excel+Dashboard
?

Let us know if this doesn’t work and we can investigate further.

Cheers,

Nate

On Tue, Jan 27, 2015 at 6:00 AM, Charles Flèche mhealth-myanmar@tsfi.org wrote:

Hi everyone,

My forms / cases hold phone numbers and devices serial number scanned
from barcodes. These values should be considered textual numbers, not plain
integers.

HTML exports (used for Excel dashboards) expose these values properly :
if the phone numbers starts with 0, the 0 is in the export.

However, importing these values into Excel convert them to plain
integers, stripping leading zeros along the way. It is also an issue with
serial numbers as some of them are considered being integers and others are
texts, which leads to issues in Microsoft Access because of data type
mismatch.

How do you usually import numeric ID / serial numbers into Excel ? Can
you specify a value type before hitting Excel ? Is there an option to add a
apostrophe for the values of a certain column so Excel consider them a
textual value ? Did I miss an obvious part of the documentation ?

Many thanks,

Charles Flèche

mHealth Advisor

Télécoms Sans Frontières http://www.tsfi.org

Première Urgence - Aide Médicale Internationale http://www.pu-ami.org


You received this message because you are subscribed to the Google Groups
"commcare-users" group.
To unsubscribe from this group and stop receiving emails from it, send an
email to commcare-users+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.


Nate Haduch

Technical Support Analyst
Dimagi, Inc | 585 Massachusetts Ave | Suite 3 | Cambridge, MA 02139
web: http://www.dimagi.com/


Nate Haduch

Technical Support Analyst
Dimagi, Inc | 585 Massachusetts Ave | Suite 3 | Cambridge, MA 02139
web: http://www.dimagi.com/

Hi Nate,

··· On Wednesday 28 January 2015 16:05:06 Nate Haduch wrote: > Thanks for checking in. Are you checking the "Preserve Cell Formatting" > checkbox as outlined in step 18d here:

I do, and my ID numbers are imported as plain integers.

Windows 7 / Excel 2013


Charles Flèche
mHealth Advisor
Télécoms Sans Frontières http://www.tsfi.org
Première Urgence - Aide Médicale Internationale http://www.pu-ami.org

Hi Charles,

I’ve made a support ticket out of this issue to investigate it further.
I’ll follow up with you on there and post our findings back on this thread.

Thanks,

··· -- Nate Haduch

Technical Support Analyst
Dimagi, Inc | 585 Massachusetts Ave | Suite 3 | Cambridge, MA 02139
web: http://www.dimagi.com/

On Thu, Feb 5, 2015 at 6:19 AM, Charles Flèche mhealth-myanmar@tsfi.org wrote:

Hi Nate,

On Wednesday 28 January 2015 16:05:06 Nate Haduch wrote:

Thanks for checking in. Are you checking the “Preserve Cell Formatting”

checkbox as outlined in step 18d here:

I do, and my ID numbers are imported as plain integers.

Windows 7 / Excel 2013

Charles Flèche

mHealth Advisor

Télécoms Sans Frontières http://www.tsfi.org

Première Urgence - Aide Médicale Internationale http://www.pu-ami.org


You received this message because you are subscribed to the Google Groups
"commcare-users" group.
To unsubscribe from this group and stop receiving emails from it, send an
email to commcare-users+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.