Using Excel to Filter in external Data
Building Your Database
The next thing you need to do after your system is installed is get as much of the data on line as possible. You can type it, import it or use a spread sheet, such as Microsoft Excel.
Using Excel to Import Data
Excel can be used to prepare data to be imported with the CAP Import Utility included in SellWise. It cannot be used to maintain data from catalog updates.
Here’s the procedure:
Print the CAP file format. Using Inventory for our example, in SellWise, select C (configuration), U (utilities), I (Import/Export), P (print record formats), I (Inventory). The result are shown below.
Data Record Template
Build an Excel Template to match the record format. (Templates, such as TEMPINV.XLS are available from CAP as an option.) You can build the inventory template by formatting the column width and cell exactly as described here.
|
|
|
|
|
|
| Rec ID | A | 1 | Number – Text | General |
| S/L | B | 9 | Number – Number 1 decimal | Right Edit, Fill, Series, column, linear, step 1 (Starts with number you enter in first cell.) |
| Item ID | C | 15 | Number - Text | General. |
| Desc | D | 30 | Number - Text | General. |
| SubDes | E | 20 | Number - Text | General. |
| 3 Fields | F | 3 | Number - Text | General. |
| (The '3 fields' above are Reorder, Serialized and Has Sold. All are 'N' to start with.) | ||||
| Vendor | G | 7 | Number - Text | General. |
| Price | H | 21 | Number - Currency, No Symbol, 4 decimal | Align right. |
| Cost | I | 21 | Number - Currency, No Symbol, 4 decimal | Align right. |
| QtyOH | J | 17 | Number - Number No decimal | Right. Fill down with 0. |
| Min | K | 6 | Number - Number No decimal | Right. Fill down with 0. |
| Max | L | 6 | Number - Number No decimal | Right. Fill down with 0. |
| Pack | M | 6 | Number - Number No decimal | Right. Fill down with 0. |
| Order | N | 6 | Number - Number No decimal | Right. Fill down with 0. |
| Avg | O | 21 | Number - Currency, No Symbol, 4 | Align right. |
| Tax | P | 1 | Number - Text | Fill down with A. |
| Dept | Q | 1 | Number - Text | General. |
| PF | R | 1 | Number - Text | Fill down with A. |
| Notes | S | 1 | Number - Text | Fill down with N. |
Open Catalog in Excel
Open the catalog file with Excel. Select delimited (separated by commas and quotes) or fixed width data (each filed is the same width). Start import at row 1 unless there’s a header record. Then use row 2. File origin is DOS or Windows ANSI.
Use the product file information to set dividers between the columns of data. Just click where each column begins and you’ll see a vertical line formed.
Select each column and set the type to Text except quantities and prices which are General.
All the data should be in columns in the spread sheet. Double click between each column header to set the width.
Cut and paste the catalog columns into the CAP Template.
Select File, Save As, (Note at the bottom, Save as type: Use Formatted Text(Space Delimited) *.prn) Excel will add .PRN. Save in the directory with SellWise.
Import the File
Save the inventory file by doing COPY INVT1.CAP INVT1.ORG
Put this file in the SellWise directory. Select C, U, I, I, I and enter the file name to import. Such as INV.PRN.
Finally, look at the inventory data to be sure the data imported correctly.
You can repeat this by starting over with an empty file. COPY INVT1.ORG INVT1.CAP.
Enter Quantity on Hand
Take a physical inventory count and enter the quantities. A portable data collector and the CAP Variance program make this easier.
Selling Items Not in the File
During the early phases of operation you may scan items and get the message ‘Item Not Found’. You can press + (The non-stock or open dept key) to sell the item. Select a department code and enter the selling price. Optionally, you can scan the item at the Description field. This records items not in the file so you can add them when convenient.