Exporting and Importing From SQL Scripts

[QMcKay]

@KuriGohan_And_Kameha

please do let me know if this is possible without a SQL script

It is possible without SQL script. See the Topic that I link next regarding Data Exports.

Any Report can be modified to become a Data Export using whatever type of column delimiter you choose.

Data Exports feature

I notice Data Exports feature under Manage > Reports, but nothing mentioned on the forum about it. What does this do? Can I use it to create CSV exports? Any info much appreciated. Thanks EDIT: Ok I notice it exports the report to a txt file. If for example I use the {REPORT INVENTORY} tag I am getting output like this: >Local Warehouse|| Svedka|4,900|ML Absolute|4,280|ML Belvedere|455|ML Taking same format from the inventory report with the table header [Inventory Report:1,1, 1…

---

You are immediately going to have an issue with CSV in particular because your decimal separator is a comma, and your column/field delimiter is also a comma. So you will need to craft your Export to quote the columns/fields.

Even though we can do this without SQL, we can still use it if you are more familiar…

###SQL:

SELECT
 mi.[GroupCode] as [GroupCode]
,mi.[Name] as [Product]
,por.[Name] as [Portion]
,pri.[Price] as [Price]

FROM [MenuItems] mi
JOIN [MenuItemPortions] por on por.[MenuItemId]=mi.[Id]
JOIN [MenuItemPrices] pri on pri.[MenuItemPortionId]=por.[Id]

WHERE 1=1
AND isnull(pri.[PriceTag],'') = ''

ORDER BY
 mi.[GroupCode]
,mi.[Name]
,por.[Id] ASC

###Report

[Products:1,1,1, 1]
>>GroupCode|Product|Portion|Price
@@Products

###Data Export

GroupCode,Product,Portion,Price
{REPORT SQL DETAILS:@@Products:F.GroupCode,F.Product,F.Portion,F.Price:
:"{0}","{1}","{2}","{3}"}

---

When you finally successfully create a CSV Data Export, there is no facility to use the file as an Import to change prices. For that, you will need to create a YAML file instead, which can also be created using Data Exports, and you can then edit the file and use it for import.

Batch Product Creation with YAML

Use Batch Product Creation feature not to go crazy while creating products. V5’s Batch Product Creation feature allows importing products from formatted text data. That format is called YAML. It is human readable and easily editable with your favorite text editor. - Name: Test Item 1 GroupCode: Test Items Barcode: 12345 Tag: Tag1 Portions: - Name: Pcs Price: 5 - Name: Pack Price: 10 Tags: - Name: Custom Tag 1 Value: Tag Value 1 - Name: Custom Tag 2 Value: Ta…

---

Or you can use Excel to dynamically build SQL UPDATE statements which you then execute using SSMS.