Data merge, telephone number

[Pyanepsion]

v2.2.0.2005 (public), v2.2.0.2075

Hello everyone
The telephone format starting with 0 is not imported correctly from an Excel file. It shows :
0 followed by several spaces.

phone.afpub phone.xlsx

[Old Bruce]

I think the problem is the use of formulas. I also doubt that we will ever see flawless importing of data from from spreadsheets which use formulas and formatting conditionals. We have to get used to using text, not formulas.

You can either have yet another sheet in the spreadsheet that is a Copy and Paste formula results as text into that sheet, and use that sheet as the Merge source or export as TSV or CSV format.

[Pyanepsion]

Hello, @Old Bruce

Thank you for your feedback.

I understand your point of view on the use of formulas in spreadsheets. However, in this particular case, the formula simply applies a format to a character string that already exists. The initial character string, in this case "0123456789", is already present in the Excel file.

What's more, I've been using the import of telephone numbers in my invoices for a long time, and it's only very recently that it's stopped working.

So I think the problem is linked to the initial 0.

[MikeW]

If Quark can import this table correctly, Serif can with a bit more effort/time.

I hardly use tables, much less connecting to Excel spreadsheets (so that update are displayed in Q, but one can bring them in as static tables too). I generally, if the data works for it, bring in as tab delimited as Old Bruce mentions and format with paragraph styles--unless I absolutely need tables.

For now, I think until this is fixed, the copy/paste as text to a new sheet is the best way around the issue.

[Hangman]

This looks like a partial regression though neither v1 nor v2 seems to handle this well regardless of whether using .xlsx or .csv files...

Affinity Publisher V1 Left | V2 Right

 

 

Affinity Publisher V1 Merged File Left |Affinity Publisher V2 Merged File Right

[MikeW]

QXP:

[Andreas CH]

Ich ändere alle meine Felder in Excel in Text um, so habe ich bei keinem Programm keine Probleme mit der Formatierung.

Übrigens die Formatierung in der Schweiz ist immer xxx xxx xx xx xx. Alles andere ist veraltet.

 

I change all my fields in Excel to text, so I don't have any formatting problems with any programme.

By the way, the formatting in Switzerland is always xxx xxx xx xx. Everything else is outdated.

phone 2.xlsx

Formel (Deutsch)	Textformat
=TEXT(B2;"00 00 00 00 00")	01 23 45 67 89
=TEXT(B3;"000-000 00 00")	012-345 67 89
=WENN(B4>=10000000000;TEXT(B4;"0-000-000-000");WENN(B4>=10000000;TEXT(B4;"(000) 000-0000;000-0000")))	(012) 345-6789
=TEXT(B5;"00 00 00 00 00")	01 23 45 67 89
=TEXT(B6;"000 000 00 00")	012 345 67 89
=TEXT(B7;"000 00 00 00")	123 45 67 89

[Andreas CH]

Das Problem tritt auch beim Seriendruck mit Word auf.
Hier dazu der Lösungsvorschlag von Microsoft.

https://learn.microsoft.com/de-de/office/troubleshoot/excel/excel-data-not-retain-formatting-mail-merge

Ich habe mit dem Umwandeln in Text in Excel sehr gute Erfahrungen gemacht. (siehe vorheriger Beitrag.)
Alle benötigten Ausdrücke stelle ich in Excel textformatiert bereit.

The problem also occurs with Word mail merge.
Here is the solution suggested by Microsoft.

https://learn.microsoft.com/en-us/office/troubleshoot/excel/excel-data-not-retain-formatting-mail-merge

I have had very good experience with converting to text in Excel. (See previous post.)
I provide all the required expressions in Excel in text format.

[Old Bruce]

@Andreas CH has hit on the obvious cause. A leading 0 will not be included as a number but it will if it is a zero (text).

Reminds me of the good old days with typewriter keyboards having numerals from 2 - 9. No 1s or 0s we used lowercase l (L, ell) and Upper Case O (o or Oh). The "font" was designed so the l and O could pass for the 1 and 0.

[Pyanepsion]

14 hours ago, Pyanepsion said:

So I think the problem is linked to the initial 0.

The telephone format of several countries imposes the initial 0 and is added by the telephone format in Excel.

[Pauls]

Seems to be functioning for me - 2.0.4 was the last working version for you?

[Hangman]

Hi @Pauls,

It doesn't appear to be working on macOS in v1 or v2...

I realise @Pyanepsion is running Windows 11 but it sounds as though he is seeing the same as I'm seeing with regards to the '0 followed by several spaces' so would be interested to understand what you're doing differently here, unless you've converted the cells to text. It certainly isn't working for custom formatted numbers exported to .xlsx

[MikeW]

Just for fun, here are screenshots from other applications...

InDesign...

The next two were copied/pasted (which doesn't seem to hold the format in APub either)...

Serif PagePlus...

Viva Designer:

[Andreas CH]

11 hours ago, Pyanepsion said:

The telephone format of several countries imposes the initial 0 and is added by the telephone format in Excel.

Excel speichert immer die Zahlen unabhängig der Formatierung ab.
Mit «Zellen Formatieren» wird nur die Darstellung in der Zelle verändert, die Zahl bleibt gleich.

Eingabe                  Formatierung                                 Speicherung

0123456789         01 23 45 67 89                                 123456789

Excel always saves the numbers regardless of the formatting.
With "Format Cells" only the representation in the cell is changed, the number remains the same.
Input                      Formatting                                      Saving
0123456789         01 23 45 67 89                               123456789

 

Ich habe den Test noch auf «Datum» ausgeweitet.
Irgendetwas stimmt noch nicht. Kann es an der Lokalisierung liegen?

I have extended the test to "date".
Something is still wrong. Could it be the localisation?

Vorgaben in Excel - Specifications in Excel

(Formatierungen.xlsx)

Resultat in Word - Result in Word

Resultat in Affinity - Result in Affinity

Formatierungen.xlsx

[Pauls]

@Hangman - I've simply loaded the afpub file, removed and readded the data source then previewed the results. Which regional settings are you using ?

I've tried french on WIndows and that worked for me too

[Hangman]

Hi @Pauls,

Likewise, though I've still failed to get this to work on macOS after testing it extensively yesterday regardless of whether using French or English...

Out of interest is it working for you on macOS? The issue I found with my testing was that Publisher (on Mac) simply wouldn't accept Excel custom formatting of any variety...

I'm assuming (rightly or wrongly) that @Pyanepsion is experiencing the same issue based on his initial comment, though he would need to clarify...

I'm a bit baffled as to why this is working for you, other than a possible Windows/Mac issue. Is there anything I'm doing differently to your process in the screen recording...

[Pauls]

can you try deleting the data source then readding it?

[Hangman]

Hi @Pauls,

No different I'm afraid (see screen recording)...

I spent several hours testing this yesterday using custom-formatted numbers generated in Excel, Google Sheets and Apple Numbers, all exported to .xlsx files and I see the same results regardless of the data source...

The main issue appears to be that Publisher doesn't like the spaces and/or dashes between the different country phone formats...

France - ## ## ## ## ##
Belgium - ## ### ## ##
Canada - (###)-###-###
Luxembourg - ### #####
Switzerland - ### ### ## ##
Morocco - ###-######

[Andreas CH]

6 minutes ago, Hangman said:

The main issue appears to be that Publisher doesn't like the spaces and/or dashes between the different country phone formats...

Das kann ich bestätigen. I can confirm that.

Ich benutze

• Windows 11
• Office 365
• Publisher 2.2.1
• Lokalisierung Schweiz (Deutsch)

Die einfachen Formatierungen werden vom Publisher übernommen.
Sobald ein "ungewohntes" (gelb markiert) Format z.B. Datum ohne Jahreszahl) eingegeben wird, kommt der Publisher nicht klar.

 

I use

    Windows 11
    Office 365
    Publisher 2.2.1
    Localisation Switzerland (German)

The simple formatting is taken over by the Publisher.
As soon as an "unfamiliar" (marked yellow) format (e.g. date without year) is entered, the Publisher cannot cope.

Dazu noch die formatierte Excel-Datei.

Formatierungen.xlsx

[Hangman]

Hi @Andreas CH,

That's exactly what I'm seeing as well, i.e., standard date formats are correctly interpreted by Publisher but any custom formats, e.g., to add a leading zero while maintaining a number format (so not converting the number to text) or the addition of a space, hyphen or Parenthesis then Publisher isn't happy...

But I'm still baffled as to why this seems to be working correctly for @Pauls

[Pyanepsion]

@Pauls, wouldn't you have used a Text format on the Excel cell, or an older version of Affinity Publisher?

[Pauls]

using your files and build 2.2.1.2075. I wonder if its linked to locales and the newly added date formatting functions for fields. Where Hangmans fails mine works.

[Hangman]

9 minutes ago, Pauls said:

I wonder if its linked to locales and the newly added date formatting functions for fields. Where Hangmans fails mine works.

Hi @Pauls, could you elaborate?

Obviously, in this use case, these are phone numbers rather than dates but curious as to what has changed and where having tried this using different locales and languages without success to date...

Is there another option I should try?

[Pyanepsion]

The bug was encountered with version v2.2.0.2005 (public), v2.2.0.2075 (public).

With version 2.2.1.2075 (beta), it apparently works correctly in UK English, and the same in French,

[Hangman]

26 minutes ago, Pyanepsion said:

With version 2.2.1.2075 (beta), it apparently works correctly in UK English, and the same in French

Not for me on macOS Monterey, I still see the same issue with the 2.2.1.2075 (beta)...