macOS Affinity Publisher 2.0.3 data merge and multilines fields in databases

[laurent32]

For all that concerns databases, I pretty much work with FileMaker Pro.

It can easily export data in XSLX or CSV formats.

When data are exported in XSLX format, multilines fields use 0D (CR) to go to the next line.

When data are exported in CSV format, lines fields use 0B (VT) to go to the next line.

In Affinity Publisher 2.0.3,

When data are imported from XSLX format, we loose the multilines fields because 0D (CR) is transformed to 20 (space).

When data are imported from CSV format, we also loose the multilines fields, but the 0B (VT) is still present, it is just ignored ? Bug ?

What I propose :

If [ 0D was changed to 0B in XLSX imports ] and if [ 0B were all changed back to 0D once the imports are done ] we could have back our multilines fields ?

Else, how can we keep our multilines fields good ?

Thanks for your answer.

[laurent32]

Looking at :

Does it mean that if I include those Unicodes in my csv file, I would get soft returns and returns back in my fields when I do a data merge ?

- U+2028 (for shift return) would give me saut de ligne (line break)

- U+2029 (for return) would give saut de paragraphe (paragraph break)

Am I correct ?

[loukash]

On 1/19/2023 at 12:27 PM, laurent32 said:

For all that concerns databases, I pretty much work with FileMaker Pro.

Same here.

Now… considering how picky FM is when it comes to importing CSV: To import for example the CSV that I can export from my bank account into my self-programmed FM financial database – which does my project time tracking, invoicing, accountancy, taxes, you name it – I have to: 1) clean up the table in LibreOffice to remove redundant non-CSV rows, 2) save as CSV with semicolon in Windows Latin1 encoding, 3) convert the CSV encoding to UTF-8 via BBEdit oder TextBatchConv, 4) convert semicolon to comma CSV via https://onlinecsvtools.com/change-csv-delimiter. Then I can finally import it into FM so that it matches the corresponding table. Yep, I have tried various workflows, formatings and intermediate steps, and this one was the one that actually always works. 

So I'm not necessarily "shocked" that not everything works that smooth vice versa either.
Given the plethora of CSV flavors, many of them will need some cleanup either via a dedicated text editor like BBEdit, or via an online tool.

2 hours ago, laurent32 said:

Does it mean that if I include those Unicodes in my csv file, I would get soft returns and returns back in my fields when I do a data merge ?

Why not simply try?  
You can also use a unique placeholder character, e.g. ¶, and replace it globally after import via Find & Replace.

[laurent32]

10 minutes ago, loukash said:

I have to: 1) clean up the table in LibreOffice to remove redundant non-CSV rows, 2) save as CSV with semicolon in Windows Latin1 encoding, 3) convert the CSV encoding to UTF-8 via BBEdit oder TextBatchConv, 4) convert semicolon to comma CSV via https://onlinecsvtools.com/change-csv-delimiter. 

Glad you use FileMaker Pro ! And if you do, FM's friend is Easy CSV Editor from Vlad : those 2 are the apps I use the most.

Easy CSV Editor will help you clean-up your CSV at once and you'll be able to format the final file in one step :

[laurent32]

18 minutes ago, loukash said:

Why not simply try?

It's planned !! But I'm running after time… aren't we all ?

[loukash]

2 minutes ago, laurent32 said:

FM's friend is Easy CSV Editor from Vlad

Thanks, but since this CSV import is something I need to do just once a year, I'm fine with my "freeware" workflow. Especially since it's all still El Capitan compatible, unlike the Easy CSV Editor. Even though with Affinity v2, I'm now spending more time booting my Mac from the Catalina partition than ever before.

3 minutes ago, laurent32 said:

I'm running after time… aren't we all ?

Oh yeah!
The time is very near when I will have to massively reduce my forum presence here and focus on other important tasks, like practicing bass guitar before the big band (and other bands') rehearsals begin in February again… 

[laurent32]

Big Bang is coooooooool !!

Don't reduce too much your presence here, we need you !

[laurent32]

20 hours ago, loukash said:

Why not simply try?  

OK, I tried…

I inserted some line breaks (U+2028) and paragraph breaks (U+2029) in my filemaker database and then exported CSV and XSLX files and data merged them in Affinity Publisher v2.0.3.

For CSV files, Filemaker keeps replacing line break and paragraph break by 0B (VT = called vertical tabulation in Filemaker = line tabulation) so : NO, it doesn't work !

For XLSX files, there is good news : 😀 YES 😀, it works ! Data Merge did bring back those line breaks and paragraph breaks I inserted (with automation) in my FileMaker Pro database.

So I get a great connection between FileMaker Pro v19.6.1.45 and Affinity Publisher v2.0.3 (via XSLX exports in FM) and that is 👍.

[loukash]

55 minutes ago, laurent32 said:

For XLSX files, there is good news

Meanwhile, I actually also tested my aforementioned FM import workflow with *.xlsx that I exported from the original (malformed and thus edited) *.csv directly via LibreOffice, rather then using my "multi-converted" *.csv workflow, and it works. The reason I wanted to avoid *.xlsx all those years was to keep everything as plain text for easy lookup. But I guess I may want to rethink that…  

The *.xlsx format is apparently more robust and more predictable as an interchange format. That's not necessarily surprising, as even the Numbers app can handle it pretty well since years. (On El Capitan, I'm still using "iWork 09", i.e. Numbers v2.3 which I prefer to any of the subsequent versions.)

[laurent32]

7 minutes ago, loukash said:

The *.xslx format is apparently more robust and more predictable as an interchange format

Even more for FileMaker Pro exports… I agree !

[Oufti]

On 1/22/2023 at 10:58 AM, laurent32 said:

For CSV files, Filemaker keeps replacing line break and paragraph break […]

You could have a look at this tutorial:

 

Basically, this workflow consists of a double Find/Replace, before export and after import, using a temporary symbol (e.g. ¶) that will survive the exchange process.