Exporting and Importing From SQL Scripts

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)


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

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:

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:


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

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.

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…


 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]

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

,por.[Id] ASC


[Products:1,1,1, 1]

###Data Export

{REPORT SQL DETAILS:@@Products:F.GroupCode,F.Product,F.Portion,F.Price:

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.

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

1 Like

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


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

,mi.[Id] as [ItemId]
--, [Id]
,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]

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

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

,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) …


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.