GoldMine SQL Query >> Output to >> Excel problem: When the field value has numbers only then Excel interprets the value as numeric and strips the leading zeros
[STEPS TO PREPARE]
1. Browse to a test contact record where the ACCOUNTNO is known
2. Complete >> Unscheduled Call >> Incoming Call
3. Provide any data but in the ActivityCode enter for example 010
5. Repeat same steps with different values like 'AB', '111', '001', '520' and '000'
[STEPS TO REPLICATE]
1. Tools >> SQL Query
2. Copy and paste the following query (adjusted with the appropriate ACCOUNTNO for the test contact record)
select * from conthist where accountno like '94041300000126900Mar'
3. In the result pane one will see that the Activity Code from the Steps to Prepare are displayed fully and correct for example as 010 or 000
4. In the result pane >> Right Click >> Output to >> Excel
5. Verify the Excel Sheet for the Activity Codes now
>> RESULT: The Activity Codes appear now a numbers and leading 0 were stripped out, this is undesired as Activity Codes may be used for further reporting
How can I get all 3 digits to output to the Excel spreadsheet?
- The behavior is rather triggered by Microsoft Excel and from within GoldMine there are not many choices to 'prevent' this behavior. Microsoft Excel tries simply to interpret any values appearing as numbers like '010' like 10 instead.
- Preference is Workaround A as there is no change in the original Query necessary. Also there should be no disadvantage in using this Workaround A (apart that it requires very few additional steps)
WORKAROUND A: Apply Custom format in Excel
1. Proceed as usual with you SQL Query and Output to >> Excel
2. In the upcoming Excel sheet where the values are now 'changed' into a real numeric format >>
3. Highlight the column
4. Right Click >> Format Cells
5. Select Custom
6. In the Type enter the format 000 >> this means that there should be 3 characters, because the ActivityCode or Result Codes content has at maximum 3 characters so every 'numeric interprested) value with only 1 or 2 character needs to be 'filled' up with leading 0s again, so
a. If the value has already 3 characters interpreted like a numeric for example 110, then nothing will be changed as the format does match already,
b. If the value has currently 2 characters interpreted like a numeric for example 10, then it will be adjusted to 010 again (which matches the original code from within GoldMine)
c. If the value has currently 1 character interpreted like a numeric for example 1, then it will be adjusted to 001 again (which matches the original code from within GoldMine)
This format change will not apply to any non numeric interpreted values like AB in a Codefield
WORKAROUND B: Change the underlying SQL Query
This 2nd workaround would be also possible but might not be such convenient as Workaround A.
- Instead of the current used SQL Query adjust your SQL Query for such fields and add an additional character so that Microsoft Excel will not interpret the values anymore as numeric
- While this will prevent Microsoft Excel to interpret the values as a numeric value
a. It may require each used SQL Query to be adjusted
b. Once you try to replace or modify the columns again in Microsoft Excel (e.g. replace the extra character) the values may be interpreted again as numeric values
- The following is a sample query how this can be done and what the outcome would be
select '_'+ actvcode as Acode,* from conthist where accountno like '94041300000126900Mar'