Exporting and Importing From SQL Scripts

[KuriGohan_And_Kameha]

Hi all.

I am trying to export from my script test with handler @@test:

    select MenuItems.Name,MenuItemPrices.Price 
    from MenuItems 
    left join MenuItemPortions on MenuItemPortions.MenuItemId=MenuItems.Id 
    left join MenuItemPrices on MenuItemPrices.MenuItemPortionId=MenuItemPortions.Id

Data export looks like this: (ISR)

    [test:1,1]
    Name|Price
    @@test

but the export result as csv: c:\unisa\ISR.csv

Name,Price
AN001 - Full ti 240,00
AN002 - Full ti 280,00
AN003 - Full ti 320,00
AN004 - Full ti 320,00

but it is suppossed to look like this:

Name,Price
AN001 - Full tips -Square,240,00
AN002 - Full tips -Oval,280,00
AN003 - Full tips -Stiletto,320,00
AN004 - Full tips -Ballerina,320,00

Is there any way to achieve this.

For Extra Reference, This is a report i made using the same script:

[NamesPrices:1,1]
Names|Prices
@@test

with this output

Then I would like to be able to import this csv file to change the prices.

Thanks in advance.

By the way, I don’t know sambapos reporting functions as well as I know SQL; so please do let me know if this is possible without a SQL script

[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.

[QMcKay]

#How to create an Excel file to update Product Prices

I don’t like to edit YAML files for updating Product Prices. I prefer to use an Excel spreadsheet to do so.

##SQL

First we need to get all our Products from the DB. Run this SELECT statement using SSMS:

SELECT
 mi.[GroupCode]
,mi.[Id] as [ItemId]
,mi.[Name]
--, [Id]
--,[Barcode]
--,[Tag]
--,[CustomTags]
--,[ItemType]
,por.[Id] as [PortionId]
,por.[Name] as [Portion]
,pri.[Id] as [PriceId]
,FORMAT(pri.[Price],'0.00') as [PriceConverted]
,CASE convert(int, pri.[Price])
 WHEN pri.[Price] THEN convert(varchar(20),FORMAT(pri.[Price],'0.00'))
 ELSE ''
 END as [PriceNew]
--,pri.[PriceTag]

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

WHERE 1=1
AND isnull(pri.[PriceTag],'') = ''
AND mi.[Name] not like '%VIP%'

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

When you run that in SSMS, it produces the following output:

---

##Create the Excel file

We can copy that output from the SELECT statement …

… and paste it into an Excel file …

---

##SQL UPDATE statements generated by Excel

Then we can create an Excel formula to dynamically construct a SQL UPDATE statement:

=CONCATENATE("UPDATE pri SET pri.[Price] = '",G2,"' FROM [MenuItemPrices] pri WHERE 1=1 AND pri.[Id]=",F2)

Then click and drag downward to copy the formula to all rows …

---

##Edit Product Prices

Now you can edit prices, and the SQL UPDATE statement will dynamically change …

---

##Execute the Price UPDATE statements in SSMS

Finally, we can copy the UPDATE statements from Excel, and paste them into SSMS, then Execute to update the Prices …

---

##Check the Price Updates

Now we can run the SELECT statement again from the first step to check the Price update(s) …

[KuriGohan_And_Kameha]

This works perfectly, thank you @QMcKay. I will use the YAML as you said, until we find a way to only change the prices. We are actually using a laptop and a server terminal, the laptop would have been the server but it tends to leave with the owner, and she wants to be able to change the prices on the laptop and run some sort of input for changing the prices. Thanks Again.