Large database import

Questions and Answers about the POS Pizza 6 and newer Database
EricCripe
Member
Member
Posts: 26
Joined: Thu Jan 21, 2016 6:23 pm

Large database import

Post by EricCripe »

I have over 600 "as-is" items that need to be imported into the database. Am I to understand that there is no way to automate this process? As far as I can tell only editing the xml file and importing it would circumvent manually entering each item. Please tell me there is another way!
User avatar
Scott
Site Admin
Site Admin
Posts: 2566
Joined: Wed Apr 16, 2003 5:35 pm
Location: Western PA

Re: Large database import

Post by Scott »

The XML Export/Import tool is specifically designed for moving items between different installations of POS Pizza.

Your best shot at attempting to import non-POS Pizza data, would be to use an SQLite tool that can do this. You will need to find one that fits your current data format, RAW, CSV, XML, etc. It would still require setup so that the fields between the data and the database can be matched correctly. Menu data from various systems is very different from one another, and there is no easy way to import one into another without some form of manual intervention or translation of the data.


Scott
User avatar
Scott
Site Admin
Site Admin
Posts: 2566
Joined: Wed Apr 16, 2003 5:35 pm
Location: Western PA

Re: Large database import

Post by Scott »

Also, what format is your current data in? If it is in CSV then the SQLite3 command shell can do a straight import. If it is in another format, or you are uncomfortable using the command shell, then you can find lots of SQLite3 utils around the internet that should work just fine as long as these are all "as-is" items with no available mods.


Scott
User avatar
synpse
Veteran Member
Veteran Member
Posts: 244
Joined: Wed Oct 12, 2011 2:38 pm
Location: Pennsylvania
Contact:

Re: Large database import

Post by synpse »

I'm also looking for "another way" other than using the GUI to manually add each item, and all the toppings, costs, whole/half/standard/removeable standard, etc. I wish I could "edit the grid" instead of a new window for every topping. (Yes, I know about importing from another item.)


I may just build an MS Access program so I can quickly create items. I peaked at the SQLite database a while ago, and I remember being scared of touching the raw data.

I'm thinking (seeing in my mind..) a form that has some checkboxes with the toppings. So I can quickly just check a box for "large dough" and a box for "hidden" and a text field for the costs. It may take me 3 hours to get the program right, but it'll save a week of repetitive drop-down lists, OK's, and clicking. Then, export the Menu Items and the Topping Items into the POSPizza DB format, and import them to the db. I can work faster in Access, and don't know XML very well.
EricCripe
Member
Member
Posts: 26
Joined: Thu Jan 21, 2016 6:23 pm

Re: Large database import

Post by EricCripe »

Thanks for the reply Scott. I edited my file and created a csv containing the Category, Item Description and Price. After poking around the database using SQLiteStudio I see that categories and items are in separate tables. I was hoping you could help me check my logic. I think I should first import the categories in to it's table and then import the items into it's table after replacing the categories name with the "list-loc" value from the category table. Make sense?
User avatar
synpse
Veteran Member
Veteran Member
Posts: 244
Joined: Wed Oct 12, 2011 2:38 pm
Location: Pennsylvania
Contact:

Re: Large database import

Post by synpse »

Seems to make sense to me. Also remember, there is a 'toppings validater' tool in the Mgmt Util. I'd give that a run after your imports, make sure the ID #'s match up and all.
User avatar
Scott
Site Admin
Site Admin
Posts: 2566
Joined: Wed Apr 16, 2003 5:35 pm
Location: Western PA

Re: Large database import

Post by Scott »

EricCripe wrote:Thanks for the reply Scott. I edited my file and created a csv containing the Category, Item Description and Price. After poking around the database using SQLiteStudio I see that categories and items are in separate tables. I was hoping you could help me check my logic. I think I should first import the categories in to it's table and then import the items into it's table after replacing the categories name with the "list-loc" value from the category table. Make sense?

Add the categories, then use the "entry_id" field in the actual items to point to the correct category. The list_loc field is for category "positioning" in the list, and is not a record pointer.


Scott
User avatar
Scott
Site Admin
Site Admin
Posts: 2566
Joined: Wed Apr 16, 2003 5:35 pm
Location: Western PA

Re: Large database import

Post by Scott »

I would not attempt to import any items that contain toppings, only "as is" items without any type of toppings or modifiers in them.


Scott
EricCripe
Member
Member
Posts: 26
Joined: Thu Jan 21, 2016 6:23 pm

Re: Large database import

Post by EricCripe »

Thanks for the clarification Scott. Think I'll give this a try this afternoon. I am only importing "as-is" items so we should be fine there.
EricCripe
Member
Member
Posts: 26
Joined: Thu Jan 21, 2016 6:23 pm

Re: Large database import

Post by EricCripe »

Any reason why I should be getting an error saying that the database is read only? I have stopped the server but am unable to import the categories from the csv
EricCripe
Member
Member
Posts: 26
Joined: Thu Jan 21, 2016 6:23 pm

Re: Large database import

Post by EricCripe »

Nevermind. It was a windows permission problem. My username didn't have write permission.
EricCripe
Member
Member
Posts: 26
Joined: Thu Jan 21, 2016 6:23 pm

Re: Large database import

Post by EricCripe »

What is the unique item name constraint?
User avatar
Scott
Site Admin
Site Admin
Posts: 2566
Joined: Wed Apr 16, 2003 5:35 pm
Location: Western PA

Re: Large database import

Post by Scott »

Each item must have a "unique" item_name as this is the master index field for that particular table. The item_name is how it is shown to the cashiers, the description is what prints on the receipt (and does not have to be unique).


Scott
EricCripe
Member
Member
Posts: 26
Joined: Thu Jan 21, 2016 6:23 pm

Re: Large database import

Post by EricCripe »

I have verified in excel that all of my item names are unique (no duplicates) and am using the "item_name" format (no spaces). Are there any other constraints that could be giving me the UNIQUE constraint error that I am receiving when I attempt to import?
EricCripe
Member
Member
Posts: 26
Joined: Thu Jan 21, 2016 6:23 pm

Re: Large database import

Post by EricCripe »

I had description and item name switched on my csv file. Success!
EricCripe
Member
Member
Posts: 26
Joined: Thu Jan 21, 2016 6:23 pm

Re: Large database import

Post by EricCripe »

So now I see all of the items in the Master Items screen of the Database Management program but they are not showing up in the Main POS Module. I checked that the times available were correct.
EricCripe
Member
Member
Posts: 26
Joined: Thu Jan 21, 2016 6:23 pm

Re: Large database import

Post by EricCripe »

Any ideas on what I might be missing? Here is a line from the CSV I imported:

1,AleSmith_IPA_6pk,AleSmith IPA,1, , , ,15, , , , , , ,8:00:00,23:59:59, , , , , , , , , , , , ,1, , , , ,

Once again, all of the items are visible in the Master Items screen of the Database Management program but all of the categories are empty in the Main POS Module.

Thanks for your help!
User avatar
Scott
Site Admin
Site Admin
Posts: 2566
Joined: Wed Apr 16, 2003 5:35 pm
Location: Western PA

Re: Large database import

Post by Scott »

I would check the following values.

hidden_days should be 0 (zero).

show_start should be 00:00:00

show_end should be 23:59:59

These are filters that if set incorrectly could be not allowing the items to show in the menu. My suspicion would be the show_end value. You could execute an SQL command to set that column to the correct value for all rows. UPDATE items_tbl SET show_end='23:59:59';

Or you could reset them all: UPDATE items_tbl SET show_start='00:00:00', show_end='23:59:59', hidden_days=0;


Scott
EricCripe
Member
Member
Posts: 26
Joined: Thu Jan 21, 2016 6:23 pm

Re: Large database import

Post by EricCripe »

I have set the hidden_days to 0 (I thought it defaulted to that so I didn't originally fill it in) and verified that the show_start and show_end were set correctly to 00:00:00 and 23:59:59 respectively. They are still not showing up in the menu. Any other ideas?
User avatar
Scott
Site Admin
Site Admin
Posts: 2566
Joined: Wed Apr 16, 2003 5:35 pm
Location: Western PA

Re: Large database import

Post by Scott »

The last thing that I can think of is expiration all set to 2000-01-01 01:01:01 ?


Scott
EricCripe
Member
Member
Posts: 26
Joined: Thu Jan 21, 2016 6:23 pm

Re: Large database import

Post by EricCripe »

I had left that field blank (default). I tried to populate it with 2000-01-01 01:01:01, 2100-01-01 01:01:01 and 2026-01-01 01:01:01 and it had no effect. I then created a test item through the Management Utility Program and confirmed that it showed up in the Main POS Module. I copied all the fields that were generated in items_tbl and copied them into the csv file containing the items I wanted to add and did an import and still had the same results! I am at a complete loss as to what is going on here. I am starting to run into a time crunch. I don't want to start building the pizza menu until this is done because I understand that the modifiers are tricky and you don't want to mess with the database once you start building the tables for the pies but it absolutely crucial that these 742 as is items make it into the database and I really don't want to have to create these items manually!
User avatar
Scott
Site Admin
Site Admin
Posts: 2566
Joined: Wed Apr 16, 2003 5:35 pm
Location: Western PA

Re: Large database import

Post by Scott »

The fields which I indicated above (hidden_days, show_start, show_end, expiration) are the only fields other than category (which would not matter when viewing ALL) and disabled that would cause the POS to skip items.

Last thought, set the disabled field explicitly to 0 (ZERO), as it may be NULL if you omitted it.

One other thing that you can do, is try loading the menu in the LT version, since it only looks at the disabled field, all of the others are ignored.


Scott
User avatar
Scott
Site Admin
Site Admin
Posts: 2566
Joined: Wed Apr 16, 2003 5:35 pm
Location: Western PA

Re: Large database import

Post by Scott »

One more thought. To test the disabled flag, go into the management util, and choose an imported item, and check the disabled box, and save. Go back into that same item in the editor, and uncheck the disabled box, and save again. See if that item now shows up in the POS.


Scott
EricCripe
Member
Member
Posts: 26
Joined: Thu Jan 21, 2016 6:23 pm

Re: Large database import

Post by EricCripe »

Hi Chris. Thanks for all your help on this. The disabled field was set with a 0 when I copied the parameters of the test item I created. I also tried to check and un check the disabled box with no change. Here is what my table looks like:

1,AleSmith_IPA_6pk,AleSmith IPA,1, ,1/1/2000 1:01,0,15,0,0,0,0,0,0,0:00:00,23:59:59,0,1/1/2000 1:01,1, , , , ,0,0,22528,0,0,1,0,0,0,0,0
User avatar
Scott
Site Admin
Site Admin
Posts: 2566
Joined: Wed Apr 16, 2003 5:35 pm
Location: Western PA

Re: Large database import

Post by Scott »

Not sure if it is just an Excel thing or not, but your date/time formats are not in SQL format which they would have to be inside of the database.

I would recommend going to SQLite Expert's web page and grabbing the Personal Edition of their tool so that you can have a look at the data in the actual database.

Use the tool and open the POSPizza.PZ6 file and pay attention to all of the fields listed above. The date/time format should like like;

2016-01-30 08:00:00.000 for Jan 30, 2016 / 8:00a. DateTime fields should always be 19 or 23 characters long (19 is acceptable if dropping the .000 portion, input them without this portion).

08:00:00 8:00a. Time fields should always be 8 characters long.

2016-01-30 Jan 30, 2016. Date fields should always be 10 characters long.

Your import item should then look like;

1,AleSmith_IPA_6pk,AleSmith IPA,1, ,2000-01-01 01:01:01,0,15,0,0,0,0,0,0,00:00:00,23:59:59,0,2000-01-01 01:01:01,1, , , , ,0,0,22528,0,0,1,0,0,0,0,0

The columns in bold changed.

Another thing that you could do is execute the following line in the SQLite command shell.

SELECT * FROM items_tbl WHERE item_id=1;

This will return the item above as it is in the database.

Scott
Post Reply