Pedro Rolon Posted September 4, 2023 Share Posted September 4, 2023 I am working for a catalog in affinity publisher, where I have a table that contains, internal code, ean code, brand, product description and others, what if in my ean code is giving me problems when stretching the data from my excel ex: Ean Code Excel : 9968645984691 A. Publisher Ean Code: 9968645985000 because the numbers are altered when generating the combination of data in A. Publisher? Quote Link to comment Share on other sites More sharing options...
thomaso Posted September 4, 2023 Share Posted September 4, 2023 Hi @Pedro Rolon, Welcome to the Affinity Forums! What type (format / category) is the cell C2 in Excel? What do you get in APub if you use the table saved as .csv file, not as .xls(x)? Pedro Rolon 1 Quote macOS 10.14.6 | MacBookPro Retina 15" | Eizo 27" | Affinity V1 Link to comment Share on other sites More sharing options...
Hangman Posted September 5, 2023 Share Posted September 5, 2023 Hi @Pedro Rolon and welcome to the forums, This does appear to be a bug in Publisher v2 (assuming you're using v2) so as @thomaso suggests, for now, saving your data as a .csv file looks like the way to go until the bug is fixed. V1 Left | V2 Right stokerg and Pedro Rolon 1 1 Quote Affinity Designer 2.5.5 | Affinity Photo 2.5.5 | Affinity Publisher 2.5.5 Affinity Designer Beta 2.6.0.2861 | Affinity Photo Beta 2.6.0.2861 | Affinity Publisher Beta 2.6.0.2861 MacBook Pro M3 Max, 36 GB Unified Memory, macOS Sonoma 14.6.1, Magic Mouse HP ENVY x360, 8 GB RAM, AMD Ryzen 5 2500U, Windows 10 Home, Logitech Mouse Link to comment Share on other sites More sharing options...
Staff stokerg Posted September 5, 2023 Staff Share Posted September 5, 2023 Hi @Pedro Rolon, I've replicated this issue in V2 and it appears to be related to the formatting of the cell in Excel. In my test i noticed when entering the number into the cell, Excel displayed the number as 9.96865E+11, from looking into this, it's how Excel treats long number cells that have the General format applied to them. If you right click on the cell and select Formatting>Numbers and set this to Numbers instead of General, decimal place 0 and for Negative Numbers, set that to the red 1234. If you then save that as a xlsx, you'll be able to use it in a data merge and get the correct value displayed. Clearly this all just worked in V1, so i'll get this logged with the Developers. Pedro Rolon and Hangman 1 1 Quote Link to comment Share on other sites More sharing options...
Pedro Rolon Posted September 5, 2023 Author Share Posted September 5, 2023 13 hours ago, thomaso said: Hola@Pedro Rolón¡ Bienvenido a los foros de Affinity! ¿Qué tipo (formato/categoría) es la celda C2 en Excel? ¿Qué obtienes en APub si usas la tabla guardada como archivo .csv , no como .xls(x)? I switched the number format to text and saved my table as a CSV file. Now the numbers are importing correctly. However, I was wondering if CSV can only read one sheet, as my document has multiple sheets. Previously, with the XLSX format, I could choose the sheet to extract data from, but in CSV it seems I don't have that option. Do I need to work on each sheet separately in CSV? Quote Link to comment Share on other sites More sharing options...
Pedro Rolon Posted September 5, 2023 Author Share Posted September 5, 2023 (edited) 4 hours ago, Hangman said: Hola@Pedro RolónY bienvenido a los foros, Esto parece ser un error en Publisher v2 (suponiendo que esté usando v2), por lo que@tomasosugiere, por ahora, guardar sus datos como un archivo .csv parece el camino a seguir hasta que se solucione el error. V1 izquierda | V2 Derecha Thanks friend, yes I'm using v2, at the moment it's working for me using csv, but I hope they can fix it in a future update. 😁 Edited September 5, 2023 by Pedro Rolon Quote Link to comment Share on other sites More sharing options...
thomaso Posted September 5, 2023 Share Posted September 5, 2023 6 minutes ago, Pedro Rolon said: Do I need to work on each sheet separately in CSV? I am not experienced with this question. – But, as asked before + mentioned by @stokerg you possibly can use .xlsx if you adjust the cell format of the EAN code column (e.g. as 'number' or 'text') to avoid any Excel internal modification to your typed and wanted cell content. Quote macOS 10.14.6 | MacBookPro Retina 15" | Eizo 27" | Affinity V1 Link to comment Share on other sites More sharing options...
Pedro Rolon Posted September 5, 2023 Author Share Posted September 5, 2023 2 minutes ago, thomaso said: No tengo experiencia con esta pregunta. – Pero, como se preguntó antes + mencionado por@stokerges posible que pueda usar .xlsx si ajusta el formato de celda de la columna del código EAN (por ejemplo, como 'número' o 'texto') para evitar cualquier modificación interna de Excel en el contenido de la celda que escribió y desea. I tried both the number and general formats, but the problem persisted. A solution I found before reaching out here was to add an asterisk (*) at the end of the EAN code, like this: 9968645984691*. That worked for me, and later I removed the asterisk using the replace function in Publisher. Quote Link to comment Share on other sites More sharing options...
Pedro Rolon Posted September 5, 2023 Author Share Posted September 5, 2023 Thank you very much for answering🫡🤩 Quote Link to comment Share on other sites More sharing options...
Pedro Rolon Posted September 5, 2023 Author Share Posted September 5, 2023 2 hours ago, stokerg said: Hola@Pedro Rolón, Repliqué este problema en V2 y parece estar relacionado con el formato de la celda en Excel. En mi prueba, noté que al ingresar el número en la celda, Excel mostraba el número como 9.96865E+11. Al analizar esto, así es como Excel trata las celdas con números largos a las que se les aplica el formato General. Si hace clic derecho en la celda y selecciona Formato>Números y lo configura en Números en lugar de General, lugar decimal 0 y para Números negativos, configúrelo en rojo 1234. Si luego lo guarda como xlsx, podrá para usarlo en una combinación de datos y obtener el valor correcto mostrado. Claramente, todo esto funcionó en la V1, así que lo registraré con los desarrolladores. I followed your steps for the formats and if it works correctly in v2, putting number format, decimal 0 and 1234 red color 🤩 stokerg and Hangman 2 Quote Link to comment Share on other sites More sharing options...
thomaso Posted September 5, 2023 Share Posted September 5, 2023 5 minutes ago, Pedro Rolon said: A solution I found before reaching out here was to add an asterisk (*) at the end of the EAN code, like this: 9968645984691*. That worked for me, and later I removed the asterisk using the replace function in Publisher. Interesting solution, but odd + cumbersome, too. Have you tried "text" as cell format for the numbers? Quote macOS 10.14.6 | MacBookPro Retina 15" | Eizo 27" | Affinity V1 Link to comment Share on other sites More sharing options...
Pedro Rolon Posted September 5, 2023 Author Share Posted September 5, 2023 3 minutes ago, thomaso said: Solución interesante, pero también extraña y engorrosa. ¿Has probado "texto" como formato de celda para los números? yes haha, but @Stokerg solution works correctly stokerg and thomaso 2 Quote Link to comment Share on other sites More sharing options...
Staff Affinity Info Bot Posted October 31, 2023 Staff Share Posted October 31, 2023 The issue "Database merge with a xlsx file that has a Cell formatted as General and has a long number causes a rounding issue with the merged data" (REF: AF-53) has been fixed by the developers in internal build "2.3.0.2083". This fix should soon be available as a customer beta and is planned for inclusion in the next customer release. Customer beta builds are announced here and you can participate by following these instructions. If you still experience this problem once you are using that build version (or later) please reply to this thread including @Serif Info Bot to notify us. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.