Back Office > Administration > SQL Tools > Import & Export > Generate Price Book
Description
This menu item is used to generate data in a Price Book format that can then be imported into SwiftPOS Back Office. Designed to provide resellers with a utility that assists when to importing Product related data source from another POS or other software system into SwiftPOS. Because SwiftPOS required Group Numbers and not just Group Descriptions, this import will automatically convert your Group Descriptions into Group Numbers and place all the Products in the appropriate groups.
Product data formatted in one of the prescribed formats below can be converted into Price Book files using this menu item. "Format 2" allows for mapping data into the appropriate fields. These files can then be imported into SwiftPOS Back Office using the Supplier Electronic Trade v1 menu item. The Price Book structures have been standard for the last 15 years and are supported in any version of SwiftPOS. Current development is being done to make this menu visible for sites that are registered for Products. Until then, this data conversion would need to be done by the Reseller.
http://www.swiftpos.net.au/touch/PCTouchValueAddedServices.html - This import process can be used for importing PCTouch or EPay data. (Reseller Only)
Generate Price Book
This screen is accessed from here.
- Format 1/2/3/4 - Select the format the source data is currently compatible with. See below for the available file formats. Note : Currently only Format 1 & 2 are available for use. The remaining formats have been designated for future use.
- File - Select to locate and specify the file containing the source data to be converted.
- Folder - Select to specify the folder in which the converted data files will be generated.
- Generate Price Book - Select to generate the Price Book files in the folder specified above.
- Increment PLU by - Displays/Enter the increment to be added to the last PLU number used when creating new Products. Functions in the same way as the Global setting found here.
Format 1
The source file is expected to be in a CSV format. The file format below was originally based on this file called ProductFileRMS.zip of sample data.
SwiftPOS Data Name |
Data Type |
Mandatory |
Comments |
Product Code |
Text |
YES |
Maximum of 13 characters. RMS Field Name is Item Code. |
Product Description |
Text |
YES |
Maximum of 60 characters. RMS Field Name is Item Description. |
Barcode |
Text |
|
Maximum of 20 characters. RMS Field Name is Barcode. |
Master Group |
Text |
|
Maximum of 30 characters. RMS Field Name is Item Category. Reporting Master Groups |
Product Groups |
Text |
|
Maximum of 30 characters. RMS Field Name is Item Touch Category. Reporting Product Groups |
Category |
Text |
|
Maximum of 30 characters. RMS Field Name is Item Group. Category |
NA |
|
|
Not Used. RMS Field Name is Item UOM. |
NA |
|
|
Not Used. RMS Field Name is Item Row. |
NA |
|
|
Not Used. RMS Field Name is Item Column. |
Price |
Integer |
|
The Normal Selling Price (incl of GST). RMS Field Name is Item Rate. |
NA |
|
|
Not Used. RMS Field Name is Item TaxStructure Code. |
NA |
|
|
Not Used. RMS Field Name is Kitchen. |
NA |
|
|
Not Used. RMS Field Name is Size. |
NA |
|
|
Not Used. RMS Field Name is Font. |
Cost |
Integer |
|
The Cost Price (excl GST). RMS Field Name is Cost Price. |
Example :
Item Code,Item Description,Barcode,Item Category,Item Touch Category,Item Group,Item UOM,Item Row,Item Column,Item Rate,Item TaxStructure Code,Kitchen,Size,Font,Cost Price
BOOK1,The Ord,,Gift Shop,Gift Shop,Retail,,,,8,,,,,6
B01,XXXX Gold Stubbie,,Packaged Beer,Beer Mid,Beverage,,,,5.5,,,,,1.52
B02,XXXX Gold Can,,Packaged Beer,Beer Mid,Beverage,,,,5.5,,,,,1.52
B03,Carlton Mid Stubbie,,Packaged Beer,Beer Mid,Beverage,,,,5.5,,,,,1.35
B04,Carlton Mid Can,,Packaged Beer,Beer Mid,Beverage,,,,5.5,,,,,1.64
Format 2
The source file is expected to be in a CSV format. Below are the only fields that are supported currently. If you have more complex file structures, you may need to use the Custom Import which requires a more detailed understanding of table structures. You could also create the basic data and then update existing/missing data using the Custom Import.
SwiftPOS Data Name |
Data Type |
Mandatory |
Comments |
Group |
Text |
|
Description with a maximum of 30 characters and the import process will convert these to Group Numbers automatically. (Product Group) |
ProductCode |
Text |
|
Maximum of 13 characters. (Product Code) If not supplied, it will use the PLU as the Product Code and make them the same. |
Description |
Text |
YES |
Maximum of 60 characters. (Product Descriptions) |
PLU |
Text |
|
Maximum of 20 characters. (1-999,999) If not supplied, it will automatically create the PLU numbers with the increment quantity set above. |
Cost |
Integer |
|
The Cost Price (excl GST). |
Price |
Integer |
|
The Normal Selling Price (including GST) and this will be imported into Price Level 1. |
Barcode |
Text |
|
Maximum of 20 characters with leading zeros. So long as you have not opened the file in Excel and corrupted the barcodes, these will remain intact. |
Example A:
Group,ProductCode,Description,PLU,Cost,Sell,Barcode
Beer,CMS0024PK,Carlton Mid Strength 24pk,,9.00,45.00,01234567890
Beer,CMS006PK,Carlton Mid Strength 6pk,,3.00,20.00,01234567891
Beer,CMS00STBY,Carlton Mid Strength Stubby,,1.00,4.00,01234567892
Beer,ABC,Stonehenge 24pk,,11.00,56.00,4455223232
Beer,Abd,Stonehenge 6pk,,5.00,22.00,
Beer,Abd,Stonehenge Stubby,,2.00,5.50,384724565488888
Example B:
Barcode,,Group,Description,Sell,Cost,
9336334001743,TOUCH,Calling cards (touch),Optus,$10 Optus Touch Voucher,$10.00,$2.30,NO,15/04/2009
9336334001750,TOUCH,Calling cards (touch),Optus,$20 Optus Touch Voucher,$20.00,$4.60,NO,15/04/2009
9336334001767,TOUCH,Calling cards (touch),Optus,$30 Optus Touch Voucher,$30.00,$6.90,NO,15/04/2009
Example C:
Description,Sell,Group,ProductCode,PLU,Cost,Barcode
Calling cards (touch),10,,,,,
Calling cards (touch),12,,,,,
Calling cards (touch),15,,,,,
Calling cards (touch),20,,,,,
In the examples above, the field names an place them in any order you choose to match your data. The fields much be included on the first line of the CSV file.
If you import a custom csv file with incorrect or missing headings, a dialog box pops up telling that the file has missing or incorrect header names and asks if you would like to correct this now. Press yes, if you wish to continue this will make a window will pop up which allows to assign the correct headers to the price book items. If, it saves without any errors it will add the headers that you assigned to each item. However, if there is a error it will move the items/headers that error into "invalid.csv" which is found in the folder that you tried to export the price book to.
If you import a custom csv file with Product/s with incorrect item length, a dialog box will pop telling you that some Products do not match and that each of Product/s that contains errors has been written out into another file called "invalid.csv" which is located in the folder that you tried to generate the price book into.
Example D:
Optus,,$10 Optus Touch Voucher,,,$10.00,9336334001743
Optus,,$20 Optus Touch Voucher,,,$20.00,9336334001750
Optus,,$30 Optus Touch Voucher,,,$30.00,9336334001767
CityLink,,CityLink Car 24-Hour Pass,,,$15.45,9332104000633
CityLink,,CityLink LCV 24-Hour Pass,,,$24.70,9332104000640
CityLink,,CityLink HCV 24-Hour Pass,,,$29.35,9332104000657
CityLink,,CityLink Car Tulla Pass,,,$5.50,9332104000695
CityLink,,CityLink LCV Tulla Pass,,,$8.80,9332104000701
CityLink,,CityLink Car Weekend Pass,,,$15.45,9332104000718
CityLink,,CityLink LCV Weekend Pass,,,$24.70,9332104000725
CityLink,,CityLink $25 account top-up,,,$25.00,9332104001555
CityLink,,CityLink $50 account top-up,,,$50.00,9332104001562
The file above has no headings (or headings that are different to the defaults) so you automatically get this message box pop up to choose the field mapping.
If your file had headings, you could choose the heading to match what you are looking to import. If there is no headings in the file, it will just show the contents of the first line in the file. You can match the fields that you want to import and the remaining files will be created automatically so that you have a valid file.
The Price Book data in the folder will look like this below.
Related Topics
- Administration
- Data Export
- Installation
- Importing data in Excel file format using the Price Book.
- Reseller Help
- SQL Tools
- SwiftPOS Price Book