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.
Solved! Go to Solution.
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.
Apologies i have edited the post to try make it a little clearer.
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
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
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.
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.
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
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;
If this option does not appear, download the Access 2010 (32 bit) driver.
Thank you both for your help