Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Table Render automatically placing comas in number field

PasqualeBasile
6 - Meteoroid

Hi,

 

I've created a workflow that outputs my data into a table.

 

When i go to render the table into an Excel file, it automatically places my int field (8 characters) into **,***,*** format.

 

Having looking at the properties of the table i cant seem to find a fix for this anywhere, hopefully its just me.

 

EDIT: Is there a way to remove the comas from the field so the output is (12345678) rather than (12,345,678)

 

Thanks for any help provided.

 

 

 

 

 

 

9 REPLIES 9
mceleavey
17 - Castor
17 - Castor

Hi @PasqualeBasile,

 

Could you give a bit more detail around what the problem is?

I'm not sure from your description what's wrong.

If you could provide the workflow, and just some junk data I can have a look.

 

Thanks,

 

M

 

EDIT: I get it now. I'll have a look.



Bulien

PasqualeBasile
6 - Meteoroid

Apologies i have edited the post to try make it a little clearer.

mceleavey
17 - Castor
17 - Castor

Hi @PasqualeBasile,

 

When outputting into Excel it will take the data type and render it according to your default settings within Excel. In this case, it's recognising you're outputting a numeric value and applying the numeric mask by default.

The easiest fix is to convert the number to a string before output, if that's applicable.

The other method is to update an existing Excel sheet with the new data inputting into a named range is pre-formatted. This will then paste the new numbers in whilst retaining the original format. You can then set the numeric format in Excel.

 

Hope this helps,

 

M



Bulien

cpituley
8 - Asteroid

Hi Pasquale,

 

If the issue is number formatting you could try switching the field type or output it as text. If I remember correctly you can feed it through the select tool to do this. 

 

Let me know if that works for you.

 

Kind regards,
Cameron

PasqualeBasile
6 - Meteoroid

Hi M,

 

Thanks for your post, it makes a lot more sense now as to why this is happening.

I tried the second method however as the render tool replaces the file / sheet when run the pre-formatted range is reset.

PasqualeBasile
6 - Meteoroid

Hi Cameron,

 

Thanks for this, the solution works however the field needs to remain a number as the data is used across other reports. 

It being a string throws back errors on VLOOOKUPS for example , where the cell will need to be valued.

cpituley
8 - Asteroid

Hi Pasquale, 

 

Understood. Could you try using the data cleansing tool to remove punctuation? 

 

If that doesn't work, as this post suggests it won't, try converting it to text and then use the formula tool to convert to a value that has a specified number format (not to include commas). Then deselect the text field so you still have just one column with your value. 

 

Hope one of these works.

 

Cheers,

Cameron

mceleavey
17 - Castor
17 - Castor

Actually, you can select "Microsoft Excel Legacy (*.xlsx)" on your output and select "Delete Data & Append" and it will overwrite what's there while retaining the format. You need to name the cell range in Excel and output to that named range as the sheet/range name.

This will allow you to retain the Excel format;

 

Capture.PNG

 

If this option does not appear, download the Access 2010 (32 bit) driver.



Bulien

PasqualeBasile
6 - Meteoroid

Thank you both for your help 

Labels