Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Export the menu items - Micros 3700 2

Status
Not open for further replies.
mxap

mxap

Vendor
May 11, 2005
197
0
0
US
We have to chnage the entire menu price. Is there a way to export all our menu items in Excel or CSV, change the price and import it back?
thanks, guys.
mxap
 
Sort by date Sort by votes
Here is what I use to export to CSV file which you can then open in Excel:

Code: 
[COLOR=#808080]//////[/color] [COLOR=#0000FF]BEGIN[/color] EXPORT [COLOR=#0000FF]BLOCK[/color] [COLOR=#808080]//////[/color]
 
 
[COLOR=#0000FF]SELECT[/color] 
    def[COLOR=#808080].[/color]mi_seq[COLOR=#808080],[/color]
    def[COLOR=#808080].[/color]obj_num[COLOR=#808080],[/color]
    def[COLOR=#808080].[/color]name_1[COLOR=#808080],[/color]
    prices[COLOR=#808080].[/color]preset_amt_1[COLOR=#808080],[/color]
    prices[COLOR=#808080].[/color]preset_amt_2[COLOR=#808080],[/color]
    prices[COLOR=#808080].[/color]preset_amt_3[COLOR=#808080],[/color]
    prices[COLOR=#808080].[/color]preset_amt_4[COLOR=#808080],[/color]
    prices[COLOR=#808080].[/color]preset_amt_5[COLOR=#808080],[/color]
    prices[COLOR=#808080].[/color]preset_amt_6[COLOR=#808080],[/color]
    prices[COLOR=#808080].[/color]preset_amt_7[COLOR=#808080],[/color]
    prices[COLOR=#808080].[/color]preset_amt_8[COLOR=#808080],[/color]
    prices[COLOR=#808080].[/color]preset_amt_9[COLOR=#808080],[/color]
    prices[COLOR=#808080].[/color]preset_amt_10
[COLOR=#0000FF]FROM[/color]
    MICROS[COLOR=#808080].[/color]mi_def def [COLOR=#808080]JOIN[/color]
    MICROS[COLOR=#808080].[/color]mi_price_def prices
    [COLOR=#0000FF]ON[/color] def[COLOR=#808080].[/color]mi_seq [COLOR=#808080]=[/color] prices[COLOR=#808080].[/color]mi_seq
[COLOR=#0000FF]ORDER[/color] [COLOR=#0000FF]BY[/color] def[COLOR=#808080].[/color]obj_num [COLOR=#0000FF]ASC[/color][COLOR=#808080];[/color]
 
[COLOR=#0000FF]OUTPUT[/color] [COLOR=#0000FF]TO[/color] [COLOR=#FF0000]'C:\temp\Menu_Item_Prices.txt'[/color][COLOR=#808080];[/color]
 
 
 
 
[COLOR=#808080]/////[/color] [COLOR=#0000FF]END[/color] EXPORT [COLOR=#0000FF]BLOCK[/color]  [COLOR=#808080]///////[/color]

And here is the block to import the updates prices back in:

Code: 
[COLOR=#808080]///[/color] [COLOR=#0000FF]BEGIN[/color] IMPORT [COLOR=#0000FF]BLOCK[/color] [COLOR=#808080]/////[/color]
 
 
[COLOR=#0000FF]DROP[/color] [COLOR=#0000FF]TABLE[/color] [COLOR=#0000FF]IF[/color] [COLOR=#808080]EXISTS[/color] #mi_price_def[COLOR=#808080];[/color]
 
[COLOR=#0000FF]CREATE[/color] [COLOR=#0000FF]TABLE[/color] #mi_price_def[COLOR=#0000FF] [/color][COLOR=#808080]([/color]
    mi_seq SEQ_NUM[COLOR=#808080],[/color]
    obj_num OBJ_NUM[COLOR=#808080],[/color]
    name_1 NAME16[COLOR=#808080],[/color]
    preset_amt_1 MONEY12[COLOR=#808080],[/color]
    preset_amt_2 MONEY12[COLOR=#808080],[/color]
    preset_amt_3 MONEY12[COLOR=#808080],[/color]
    preset_amt_4 MONEY12[COLOR=#808080],[/color]
    preset_amt_5 MONEY12[COLOR=#808080],[/color]
    preset_amt_6 MONEY12[COLOR=#808080],[/color]
    preset_amt_7 MONEY12[COLOR=#808080],[/color]
    preset_amt_8 MONEY12[COLOR=#808080],[/color]
    preset_amt_9 MONEY12[COLOR=#808080],[/color]
    preset_amt_10 MONEY12
[COLOR=#808080]);[/color]
 
INPUT [COLOR=#0000FF]INTO[/color] #mi_price_def
[COLOR=#0000FF]FROM[/color] [COLOR=#0000FF]C:[/color]\temp\Menu_Item_Prices[COLOR=#808080].[/color]txt
[COLOR=#FF00FF]FORMAT[/color] [COLOR=#0000FF]TEXT[/color][COLOR=#808080];[/color]
 
[COLOR=#FF00FF]UPDATE[/color] MICROS[COLOR=#808080].[/color]mi_price_def [COLOR=#0000FF]SET[/color]
    [COLOR=#0000FF]target[/color][COLOR=#808080].[/color]preset_amt_1 [COLOR=#808080]=[/color] [COLOR=#808080]source.[/color]preset_amt_1[COLOR=#808080],[/color]
    [COLOR=#0000FF]target[/color][COLOR=#808080].[/color]preset_amt_2 [COLOR=#808080]=[/color] [COLOR=#808080]source.[/color]preset_amt_2[COLOR=#808080],[/color]
    [COLOR=#0000FF]target[/color][COLOR=#808080].[/color]preset_amt_3 [COLOR=#808080]=[/color] [COLOR=#808080]source.[/color]preset_amt_3[COLOR=#808080],[/color]
    [COLOR=#0000FF]target[/color][COLOR=#808080].[/color]preset_amt_4 [COLOR=#808080]=[/color] [COLOR=#808080]source.[/color]preset_amt_4[COLOR=#808080],[/color]
    [COLOR=#0000FF]target[/color][COLOR=#808080].[/color]preset_amt_5 [COLOR=#808080]=[/color] [COLOR=#808080]source.[/color]preset_amt_5[COLOR=#808080],[/color]
    [COLOR=#0000FF]target[/color][COLOR=#808080].[/color]preset_amt_6 [COLOR=#808080]=[/color] [COLOR=#808080]source.[/color]preset_amt_6[COLOR=#808080],[/color]
    [COLOR=#0000FF]target[/color][COLOR=#808080].[/color]preset_amt_7 [COLOR=#808080]=[/color] [COLOR=#808080]source.[/color]preset_amt_7[COLOR=#808080],[/color]
    [COLOR=#0000FF]target[/color][COLOR=#808080].[/color]preset_amt_8 [COLOR=#808080]=[/color] [COLOR=#808080]source.[/color]preset_amt_8[COLOR=#808080],[/color]
    [COLOR=#0000FF]target[/color][COLOR=#808080].[/color]preset_amt_9 [COLOR=#808080]=[/color] [COLOR=#808080]source.[/color]preset_amt_9[COLOR=#808080],[/color]
    [COLOR=#0000FF]target[/color][COLOR=#808080].[/color]preset_amt_10 [COLOR=#808080]=[/color] [COLOR=#808080]source.[/color]preset_amt_10
[COLOR=#0000FF]FROM[/color] MICROS[COLOR=#808080].[/color]mi_price_def [COLOR=#0000FF]target[/color] 
[COLOR=#808080]LEFT[/color] [COLOR=#808080]OUTER[/color] [COLOR=#808080]JOIN[/color] #mi_price_def [COLOR=#808080]source[/color]
[COLOR=#0000FF]ON[/color] [COLOR=#0000FF]target[/color][COLOR=#808080].[/color]mi_seq [COLOR=#808080]=[/color] [COLOR=#808080]source.[/color]mi_seq
[COLOR=#0000FF]WHERE[/color] [COLOR=#0000FF]target[/color][COLOR=#808080].[/color]mi_seq [COLOR=#808080]IN[/color][COLOR=#0000FF] [/color][COLOR=#808080]([/color][COLOR=#0000FF]SELECT[/color] mi_seq [COLOR=#0000FF]FROM[/color] #mi_price_def[COLOR=#808080]);[/color]
 
 
[COLOR=#FF00FF]UPDATE[/color] MICROS[COLOR=#808080].[/color]mi_def [COLOR=#0000FF]SET[/color]
    [COLOR=#0000FF]target[/color][COLOR=#808080].[/color]name_1 [COLOR=#808080]=[/color] [COLOR=#808080]source.[/color]name_1[COLOR=#808080],[/color]
    [COLOR=#0000FF]target[/color][COLOR=#808080].[/color]obj_num [COLOR=#808080]=[/color] [COLOR=#808080]source.[/color]obj_num
[COLOR=#0000FF]FROM[/color] MICROS[COLOR=#808080].[/color]mi_def [COLOR=#0000FF]target[/color]
[COLOR=#808080]LEFT[/color] [COLOR=#808080]OUTER[/color] [COLOR=#808080]JOIN[/color] #mi_price_def [COLOR=#808080]source[/color]
[COLOR=#0000FF]ON[/color] [COLOR=#0000FF]target[/color][COLOR=#808080].[/color]mi_seq [COLOR=#808080]=[/color] [COLOR=#808080]source.[/color]mi_seq
[COLOR=#0000FF]WHERE[/color] [COLOR=#0000FF]target[/color][COLOR=#808080].[/color]mi_seq [COLOR=#808080]IN[/color][COLOR=#0000FF] [/color][COLOR=#808080]([/color][COLOR=#0000FF]SELECT[/color] mi_seq [COLOR=#0000FF]FROM[/color] #mi_price_def[COLOR=#808080]);[/color]
 
 
[COLOR=#808080]////[/color] [COLOR=#0000FF]END[/color] IMPORT [COLOR=#0000FF]BLOCK[/color] [COLOR=#808080]//////[/color]
 
Upvote 0 Downvote
Moregelen,
Thank you so much. I think this will work.
As being new to this.
Is this a batch file I need to run?
If yes, where do i run this file from, C:\MICROS folder?
Can you please expand on this?
We have Micros 3700 5.4.1.
thanks
 
Upvote 0 Downvote
Ah. I would just run it from dbisql (start, run, dbisql).

Login in using username custom, password custom, selecting the ODBC data source Micros.
 
Upvote 0 Downvote
Make sure you have a folder called C:\temp.

The first script will create the CSV file in that folder.

Make your changes, and put the updated file in C:\temp. Make sure you DO NOT CHANGE THE SEQ NUMBER - only change prices, the last 10 columns.


Run the second script once you have put the updated file in C:\temp and it will update all of the items. You CAN change the name of the item, and the script will update it, though only name 1.
 
Upvote 0 Downvote
Hi Moregelen,
here is what I did.
I connected to DB by "dbisql"
pasted your script and execute. That brought up the error.
Please see attached screenshot.
thanks again for the help.

Screen_Shot_2017-06-12_at_4.14.28_PM_akbn6e.png
 
Upvote 0 Downvote
... are you sure you pasted what I posted? Your screenshot is not even close to what I posted.
 
Upvote 0 Downvote
Moregelen,
Please ignore the post. I am not paying attention.
sorry.
thanks
 
Upvote 0 Downvote
Moregelen,
I was able to export to TXT and then converted to CSV.
Now, I change the prices in CSV file, save it as TXT and import back.
Does this sound right?
thanks
 
Upvote 0 Downvote
File should have been saved as a CSV file automatically. Just edit the prices and make sure when you save it you keep it a csv file. Excel will try and get you to make it into an excel file. Then just run the second part when done editing.
 
Upvote 0 Downvote
mxap,

You should really consider having someone familiar with SQL help you with this. You have the potential to mess up your entire database, at least the menu items. It seems like you are a bit lost. If you have someone show you the first time you should be able to handle it in the future.

 
Upvote 0 Downvote
Thanks, guys.
I am trying this on test server. If it works only then will try on live server.
Thanks , again.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

B
  • Question
MICROS RES 3700 - Desperate!
Replies
4
Views
376
bdavidson024
B
RickysRestaurants
  • Locked
  • Question
Micros 3700 Res 4.11
Replies
2
Views
192
RickysRestaurants
RickysRestaurants
JimFromCanada
  • Question
RE: MICROS RES 3700: IFS error 1
Replies
6
Views
279
JimFromCanada
JimFromCanada
RickysRestaurants
  • Locked
  • Question
Micros 3700 4.11
Replies
7
Views
121
Nicholas Kisarale
Nicholas Kisarale
AnabellaNasser
  • Locked
  • Question
Export Utility for micros 3700 v5.7
Replies
0
Views
53
AnabellaNasser
AnabellaNasser

Part and Inventory Search

Sponsor

Back
Top