Affinity Publisher Data Merge Manager

[Pedro Rolon]

 

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?

[thomaso]

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)?

[Hangman]

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]

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]

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?

[Pedro Rolon]

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

[thomaso]

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.

[Pedro Rolon]

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.

[Pedro Rolon]

Thank you very much for answering🫡🤩

[Pedro Rolon]

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 🤩

[thomaso]

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?

[Pedro Rolon]

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

[Affinity Info Bot]

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.