Help export customer data

[mhayatlah]

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,
Mohammed

[JTRTech]

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

If you explain what your doing it would help.

[mhayatlah]

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

I am old now 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

[mhayatlah]

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

[JTRTech]

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.

[mhayatlah]

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

[mhayatlah]

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

[JTRTech]

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

Export custom data into csv or txt

Youll need to add a constraint expression to entity type etc to set to only be one type of entity. As for output how do you mean fkd? are you setting the export directory as a .csv file?

[mhayatlah]

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

[QMcKay]

SambaPOS 5 Bulk Product and Inventory Importer

##Synopsis This Tool is primarily used as a Bulk Product, Inventory, and Recipe importer based on data read from a CSV file. This is especially useful in Retail applications where there is a direct 1:1 relationship between Items you buy from a supplier and Products you sell. As of this release, this Tool is compatible with the following SambaPOS versions: 5.1.58 - 5.1.60 (DB version 124) ::: dbops124c.zip (24.6 KB) Originally developed by @JohnS for SambaPOS 3, updates to the Tool were dev…

Excel file to sambapos

Search the 'net to find tools to convert CSV to YAML. Or use Excel to read the CSV data and use this formula in a Cell: =CONCATENATE("- Name: ", A2, CHAR(10), " GroupCode: ", "Imported", CHAR(10), " Barcode: ", C2, CHAR(10), " Portions: ", CHAR(10), " - Name: Normal", CHAR(10), " Price: ", G2) That produces YAML formatted data from your CSV file. Copy the formula to every row. You will probably want to clean up your CSV data file. There are duplicate Products, and some wi…

[mhayatlah]

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

regards

[JTRTech]

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

[QMcKay]

@mhayatlah

would u please explain to me or would u please send me some steps to do

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

@JTRTech

There was another post the other day where someone was creating a yaml string using a formula in excel,

That is shown in the the 2nd link.

[mhayatlah]

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