Sql request to export customer data on excel

Hello, what is the right sql request to export the whole customer data (name, phone, date of birth…) on excel file (or other application)
When I enter this sql instruction :
SELECT Name, CustomData
FROM Entities
WHERE EntityTypeId=1 (1 is to export only Customer Entity)

In CustomData Field (on excel file), I get this value : [{“Name”:“Phone”,“Value”:“0878454635”},{“Name”:“Client REF”,“Value”:“ZX12345TEST”},{“Name”:“Category”,“Value”:“VIP”}]

Can we display it with a better way? (like 1 column for each custom field)


We store all entities on entities table and since all entities (tables, customers, etc) does not share same custom fields we can’t store them as table columns. So I used JSON format to store custom field values. Maybe we can implement exporting as CSV on future versions but don’t expect it soon.

Ok, so there is no way to display it correctly and use this table with an external application (creating fidelity card program, sending sms program…)
But even if we can’t display it clearly in column, is there request to use these datas in xls files?

We’ll develop tools for integration with other apps but this won’t happen through database. We’ll release a separate library for it.

Ok thanks, I’ll follow it

Maybe that could help someone but in excel we can display these datas in another column with formule like this :

In B2 (column B ligne 2) we have this data :
[{“Name”:“Phone”,“Value”:“0878454635”},{“Name”:“Client REF”,“Value”:“ZX12345TEST”},{“Name”:“Category”,“Value”:“VIP”}]

Create a new column called “client ref” in column C (for exemple) and enter this formule in C2 :

=STXT(B2;CHERCHE(“Client REF”;B2;1)+21;CHERCHE("}";B2;CHERCHE(“Client REF”;B2;1)+22)-CHERCHE(“Client REF”;B2;1)-22)

Result, in C2 : ZX12345TEST is clearly displayed
NB : Of course, “STXT” and “CHERCHE” should be change in local language

1 Like