1. 

   shem, Member

   I am running a report which retrieves a column, lets call it "Age", which contains only digits, but yet is stored as a Varchar.
   When I export to excel, I see that each of the cells in the "Age" column has a green triangle in the top left of the cell indicating that it is stored as Text.
   Is there any excel export option I could use to cause any column output which contains only digits to be exported to excel as a number?
2. 

   myDBR Team, Key Master

   You can cast the VARCHAR to INT/UNSIGNED so the Excel export treats it as a number.

   —
   myDBR Team
3. 

   shem, Member

   That won't be good enough for me.
   I am passing a complete query into the myDBR report, and I don't know ahead of time which fields are being output.
   Thus I was hoping there would be some export option that could look at all output data and based on which pieces of data are just digits to export them as numbers.
4. 

   myDBR Team, Key Master

   Not sure how your queries work, but if you are querying Age-column, you should know it's a number even if you store it as varchar.

   myDBR will treat numbers as numbers and strings as strings. Not all strings that contain all digits are numbers.

   --
   myDBR Team
5. 

   shem, Member

   So I used CAST to convert the string to an UNSIGNED.
   So when I exported the report to excel it indeed stored it as a number.
   However, now with commas e.g. 23,456
   How can I get it to export to excel as a number and yet display without commas?
6. 

   myDBR Team, Key Master

   The thousad separator and the decimal separator come from the user preferences. Numbers, dates and times are formatted based on user preference.

   If you want the formatting to be different from the user preference, use a column style:

   select 'dbr.colstyle', 'Age', '%d';

   Alternatively, if you want to fully override the user preferences, use dbr.localization

   select 'dbr.localization', 'thousand_separator', '';

   --
   myDBR Team

Reply

You must log in to post.