Mike.G Posted September 5, 2023 Posted September 5, 2023 Affinity Publisher's Data Merge tool has always worked fine for me as recently as last week. Starting today, all currency fields from my Excel template are now being merged into Affinity Publisher incorrectly; all 3-digits leading with an extra 0 and a missing decimals. Examples look like the below: (Excel Data) = (Merged Data displayed in Affinity Publisher) $13.03 = $013 $25.94 = $025 $27.39 = $027 and on... I've uninstalled Publisher 2.1.1 and reinstalled Affinity Publisher 2.0.0, and the Data Merge works fine with older archived files. However my most up-to-date template that I use for Data Merge won't open on the older versions of Publisher because it claims it uses "features from a newer version". I exclusively use Affinity Publisher for it's Data Merge capability to autofill about 200 currency fields in a 40+ page benefits document for clients. How can I fix this to work as flawlessly as it did last week? I've already tried uninstalling and reinstalling all other versions of Publisher, and all either have the same issue or cannot open my current template file.
Mike.G Posted September 5, 2023 Author Posted September 5, 2023 I believe I fixed this issue after turning OFF Hardware Acceleration: Enable OpenCL compute acceleration under Edit > Settings > Performance and restarting Affinity Publisher.
Staff NathanC Posted September 6, 2023 Staff Posted September 6, 2023 Welcome to the forums @Mike.G, I'm quite surprised to hear that disabling Hardware Acceleration has fixed the decimal issue with your data fields, but glad to hear there is a solution. So I can see if the problem can be replicated, could you perhaps upload your .afpub doc prior to merging and excel document data source to the private dropbox link below? https://www.dropbox.com/request/t56w2je5dGnfDqSJF6O3 Many thanks
Mike.G Posted September 6, 2023 Author Posted September 6, 2023 Hi Nathan, I've uploaded a trimmed version of the file to the Dropbox above. It began working correctly yesterday after I turned Hardware Acceleration OFF. However I'm experiencing the same thing this morning, even with HA still turned off. When I Data Merge my spreadsheet into the Affinity Template and choose my TAGS sheet, I'm getting the same output with the incorrect decimal place and added 0 as shown above. All values on the Excel sheet template display $0.00. I don't know why my problem was temporarily solved but as of this morning has returned.
Mike.G Posted September 6, 2023 Author Posted September 6, 2023 Now - after trying the above twice, I turned HA back ON and restarted the Affinity Publisher app and tried a Data Merge, it's working correctly. Something about toggling HA ON and OFF seems like it's impacting the Data Merge tool pulling data from Excel.
Staff NathanC Posted September 6, 2023 Staff Posted September 6, 2023 Thanks Mike, I can see that your data merge .afpub merge template has been sent, but I can't see the data merge associated data merge source (spreadsheet) in the dropbox folder so i've got a sheet full of empty references/fields, could you send the spreadsheet over? Many thanks!
Mike.G Posted September 6, 2023 Author Posted September 6, 2023 Nahtan, I've uploaded the spreadsheet. The orange fields are the ones that will merge into the .afpub sample file I uploaded. I closed and reopened Affinity Publisher and I'm getting the exact same Data Merge again seen below.
Staff NathanC Posted September 7, 2023 Staff Posted September 7, 2023 Hi @Mike.G, Thanks for sending the .xlsx over, I've set 'Round' as my data sheet source and have observed the same irregularities with number formatting, for me this was persistent between app restarts and toggling H/A on and off. I believe I've spotted what may be causing this unusual formatting in Publisher, when I open the 'Round' Sheet and select all the cells in Row 2 starting with 'BC59EE' onwards and check their current formatting, they appear to have been set to a custom currency cell format shown below. If I instead change the cell formatting to 'Currency', set the symbol to '$ English (United Sates)' and set the decimal places to 2, save the .xlsx and then deleted and re-added it as my data source to refresh Publisher, this appears to format the fields as expected, as shown below. Is there any reason you've needed to use the custom currency format over the standard one that Excel offers? My excel knowledge on custom cell formats isn't fantastic.
Mike.G Posted September 13, 2023 Author Posted September 13, 2023 Hi Nathan, I'm circling back to this issue. I've tried testing your solution for a few days and keep getting the same results as before. When I do a Data Merge, Affinity pulls all of my data in as shown in the examples above. I have to remove the data merge sheet and "Turn On" hardware acceleration and restart Affinity Publisher > then "Turn Off" hardware acceleration and restart Affinity Publisher again in order for it to pull my sheet data correctly. If I make an edit to my spreadsheet and click the "Update" button, it'll return all values to the incorrect $00.0 format as before. I have to repeat "Turn On > Close Program > Turn Off > Close Program" every single time I update my spreadsheet. I've been using these same templates for the last two years and it's never done this before. This seems to be a new issue as of the last 2-3 weeks. Do you have any other idea as to why this issue is persisting and affecting every single $currency datapoint from my sheet?
Staff NathanC Posted September 14, 2023 Staff Posted September 14, 2023 13 hours ago, Mike.G said: I'm circling back to this issue. I've tried testing your solution for a few days and keep getting the same results as before. When I do a Data Merge, Affinity pulls all of my data in as shown in the examples above. I have to remove the data merge sheet and "Turn On" hardware acceleration and restart Affinity Publisher > then "Turn Off" hardware acceleration and restart Affinity Publisher again in order for it to pull my sheet data correctly. If I make an edit to my spreadsheet and click the "Update" button, it'll return all values to the incorrect $00.0 format as before. I have to repeat "Turn On > Close Program > Turn Off > Close Program" every single time I update my spreadsheet. I've been using these same templates for the last two years and it's never done this before. This seems to be a new issue as of the last 2-3 weeks. Do you have any other idea as to why this issue is persisting and affecting every single $currency datapoint from my sheet? The Hardware Acceleration Enable/Disable doesn't make any difference on my device so it's hard to say, the number format always shows incorrectly in APub if that 'Custom' currency format is used in Excel instead of the standard one, as soon as I switch the fields out for the standard currency cell format, this works as expected for me and continues to remain with the correct format even if I go back and update my spreadsheet and then update it within the app again, the number format set to 'Currency' remains correct. On your device it might be that the data sheet and cell being referenced (Data Merge Profile Card) also needs to be updated to the 'Currency' format since this is where the data is getting pulled from originally in Excel. I've been doing some trial and error with completely different documents, and it does look like something has changed at some point in V2's update cycle which has caused this 'Custom' currency data format to not be pulled into APub's data merge correctly with the decimal error, I've tested this on a completely different test data sheet in V1, and it's pulling the data correctly. I'll get this logged with the developers.
Old Bruce Posted September 17, 2023 Posted September 17, 2023 @Mike.G I am curious as to what happens if you export from Excel to a .CSV file and use that .CSV file for the Data Merge. Does the weird formatting issue persist? Mac Pro (Late 2013) Mac OS 12.7.6 Affinity Designer 2.6.0 | Affinity Photo 2.6.0 | Affinity Publisher 2.6.0 | Beta versions as they appear. I have never mastered color management, period, so I cannot help with that.
simplicity Posted January 7 Posted January 7 I am wondering whether a solution has been found on this issue? I am also struggling with this same issue as described above, needing to get documents prepared for clients asap. For me, the hardware accelleration trick didn't make any difference. I am using version 2.5.7. right now but I will check going back through the versions to see if it makes any difference. Please let me know if there have been any updates or workarounds that might be of help to me. Thank you!
simplicity Posted January 7 Posted January 7 Interestingly, I found that everything works as expected as soon as I turn the currency format in LibreOffice Calc to USD $ English (USA) (or any other USD currency format, like USD $ Spanish (Uruguay) for example). It also works as expected in GBP currency format. It also works in EUR € English (Ireland) format, but interestingly enough it DOESNT work in EUR € French (Monaco). From every currency I've now tested, the common seems to be that APub expects the currency format to have the currency symbol followed by a value. Any currency format that has the currency symbol, then a space (" ") and only then the value, does suffer from that mis-interpretation. Following that rule, all currency formats that don't have the currency symbol in front of the value but rather at the end, suffer from the bug. I have now tried various uncommon and common currencies (from Benali, over Bulgarian and many others, over various EUR, USD, GBP formats) and they all follow that rule: It works, so long as the currency symbol is the first character and is (without a space) followed by the value. Any currency format following that rule is displayed as expected. Even custom currency formats following this rule work as expected. For now, while being massively annoying, I can work around that. Is there a chance this could be fixed? Can others replicate these findings on their machines?
Recommended Posts