Help export customer data

hello all,

I am trying to export the customer data using MS management studio.

I have a litl experience with SQL query

this is the culomn data

[{“Name”:“PostCode”,“Value”:“BS3 1JQ”},{“Name”:“House Number”,“Value”:“169”},{“Name”:“Street”,“Value”:“North Street”},{“Name”:“Town”,“Value”:“Bristol”},{“Name”:“County”,“Value”:“City of Bristol”},{“Name”:“Distance”,“Value”:“0.383125”},{“Name”:“Name”,“Value”:null}]

and this is the select ststmant i use

SELECT EntityTypeId, Entities.CustomData
FROM Entities
WHERE EntityTypeId = ‘1’

how i can column like this

PostCode : House Number : Street : Town :

BS3 1JQ 169 NNN Bristol

Kind regards,

Export to what?
Thats JSON and until 2016 SQL has limited/no JSON abilities.

If you explain what your doing it would help.

to MS Excel but first, I have to write the correct select statement.

I am old now :slight_smile: and been more than 10 years I did not do any programming or coding, I am a chef now:slight_smile:
I just need t know how to do select statement can read this data from Entities.CustomData
maybe I can refresh my brain.

please have a look to the print screen

usually, as I remember every column store a value in this case Entities.CustomData store many data, this is new to me so I forgot how to read this using select

As I said that’s JSON which SQL pre 2016 doesn’t have much power to manipulate.
It is used because the entity custom fields are not fixed field content, similar to custom product tags.this is not unusual especially in world of web with php and Java.

You will be better off using the samba reports and data export option to output to CSV which you’ll be able to open in excel.

The report wouldn’t be overly complex…
{REPORT ENTITY DETAILS:xxxxxxxxx} will do the job.
I have one very similar to get a list of emails for mail list for last client but not at PC.

any tutorial in one page with exact steps because in the forum it’s confusing

there is no much can do in MS management studio for this

It’s not that confusing, third result for search of ‘export entity data’

1 Like

after an update the excel file and the data how I can import it back to samba

I read the both link, I couldn’t understand or do it for customer details. would u please explain to me or would u please send me some steps to do


There was another post the other day where someone was creating a yaml string using a formula in excel, did you see that?

The steps are detailed in the links I provided. There is no need to re-iterate them here.

That is shown in the the 2nd link.

Dear ALL,

many thanks to all for the efforts I made I learned a lot from you.
I successfully export the customer details to excel files please have a look to the report and to the Excel file below

now in excel I have clean the data and fix the everything, I can now send the SMS to customers and run some more marketing.

now I would like to import the clean data bake to samba or sync the data whatever you call, @QMcKay advised me to use batch create after creating a yaml string using a formula in excel,

I had followed all steps as in the below pic

when I use this formula in Excel

I used the batch create

i am not sure what is the prob