ProductImport
From MyMoney Online Help
Contents |
How to Import Product files
You have the ability to import product files into Suite. These files must be Tab Delimited Text files. These are simple text files where the fields are separated by tabs, instead of commas or spaces. They can be created or converted using a variety of methods and programs, including Notepad and Excel. If your files are already tab delimited, you can skip the File Preparation section, but it might be worth reviewing for best practises.
Besides periodic price updates, this feature can be used as a way to change a property for any number of products or to add in classes.
File Preparation
Vendors might offer product updates in various formats, including Excel. We will use Excel since most people have it and it is very simple to create a tab delimited Text file. If Excel is not available, contact Hatala Support and we'll work with you on finding an alternative.
To convert from Excel.
- Open your file in Excel
- Column Headers: Row 1 must contain the headers that will match with Suite, such as Part Name, Description, List Price, Cost, Color. Make sure they are titled in a way that you can work with. These headers will be matched with product properties in Suite.
- Clean up the Rows: Row 2 and every row containing relevant data after it (rows you expect to be imported), must have sound data corresponding to the respective headers. Ex: Cells under the Price column should have monetary values and the Product Name should be alpha and/or numeric names, etc. It's also necessary to delete any blank rows that might be present. Delete every blank row.
- Unique Product Numbers: Each row should have a unique value for the field that will be matched to Suite's Product Number. (You match your file's Part Name to Suite's Product Number, everything under Part Name needs to be unique.) Suite will import only one instance per Product Number. Repeat values and blank entries need to be removed or modified for compatibility.
- Clean up the Columns: If a Column has a blank or unknown Header and/or random bits of data show up in different rows under it, if you don't know what these mean, delete the column entirely. (In other words, delete columns you won't use.) Vendors can offer any number of details for their products, if you can't use it, clear it out until you can. You can always run the update again when you have more information. Although Suite will ultimately not use any fields unmatched (not selected) in the import routine, it will speed up the process to clean these out.
- Save: After you have the columns and rows cleaned up, save the file as a tab delimited text file: File > Save As > Save as type: Text (Tab delimited) (*.txt)
- Name it: Name the file next to "File name:" to the name that you want associated with the Supplier and click Save. From there you might need to click on Yes or Ok.
- Close the TXT file and return to Suite. See Basic Steps below.
Before
After
Basic Steps
- Click on Menu > Product Maintenance and click on the Settings button at the top.
- Click on "Apply Product Updates".
- Click on "Click Here To Import File".
- This opens a Windows - 'Open' window. Find your txt file and double click on it.
- The left hand column shows the header fields in your import file. The right hand side shows which fields are available in Suite to be matched up.
- If you get both fields highlighted, you can double click either one to match them up. Right click if you want to remove the association.
- After you have matched them up, click on Import Product Data.
Remember:
The Product Number must be unique.
Using blank fields for item properties, where existing data is present, will result in blanking out that data. So if Item_1 has a Color value of Blue, and you import a new update leaving the Color column blank or unselected, it will blank out that item's Color value.
Advanced File Preparation
There are a number of formats and data fields that can provided by any given product vendor. Many will be similar but still require a little preparation, others could have unusable or excessive data. Here are some things to look for and tips to modify the data.
How to delete a column in Excel: Right-click on the column letter at the top, everything in the specific column should be highlighted, left-click on Delete.
How to delete a row in Excel: Right-click on the row number on the left, everything in the specific row should be highlighted, left-click on Delete.
Always know the column in your Excel file that will be matched with Product Number in Suite should have a unique value (alpha-numeric acceptable). This is the Product Name in Suite and how Suite differentiates each product. Duplicate values will only import the last read record. For example, if item ABC is in five different rows, it will only import the last row. Product Number is the unique identifier for the system's products.
Never use a file with excessive white space columns or rows, pictures, garbage data/characters, foreign ascii. You can delete extra rows by right-clicking the first blank row, hold down Shift and Ctrl and then hit the down arrow on the keyboard. They should all be selected, Delete them. Sometimes it doesn't appear there is anything in these rows, but whitespace and blank data can cause issues with text files.
- Blank cells are fine for non-essential columns or columns that won't be matched with the Product Number, like Description.
- The name of the text file will be the Supplier name in Suite. So if you name the file Supplier521.txt and import the contents of that file, you will have a Supplier named Supplier521. (Your Items Sold By Supplier report will be associated with that name, until the item's Supplier is modified.)
What product properties can be imported into Suite?
The following are available for importing into Suite and can be matched up as follows. They are also the listing order in Suite on the right hand side.
- Product Number - This must be unique.
- Description - Alpha/Numeric
- Manufacturer - Alpha/Numeric
- Color - Alpha/Numeric
- Weight - Numeric only
- Catalog Page - Currently inactive in Suite GUI
- Rentable, Serviceable, Returnable - Use "Yes" or Yes.
- Cost - The cost of the item from the Vendor/Supplier. Dollar signs "$" are acceptable but not quotes. Do not enclose price with quotes.
- MSRP - Note that this value currently is visible from the Invoicing Product Search Results. Dollar signs "$" are acceptable but not quotes. Do not enclose price with quotes.
- Class - Suggested to use with Category's. If you use a Class that isn't in the system it will be added automatically.
- Sub Class - Suggested to use with Category's. If you use a SubClass that isn't in the system it will be added automatically.
- List price - This is what the customer pays. Dollar signs "$" are acceptable but not quotes. Do not enclose price with quotes.
- Effective Date - Suite will plug in a date automatically (the current system date) or use the format of mm-dd-yy to customize the date
- Reorder number of Pkgs - "Reorder" value.
- Units Per Package - "at n Units per Pkg" value.
- Reorder Trigger Qty - "When Inventory QTY goes below" value.
- Supplier Product Number - This value will be assigned to the product's Supplier > Item # field.
- UPC - Scannable UPC codes.
- Min Advertised Price - Currently on 1.3.0.21 Suite defaults the Products view to the Notes window. Match this with the same field for Cost for quick viewing of the Supplier Cost.
- Notes - This goes to the Notes ares
- Quantity - This goes to the "On Hand" quantity
Use of special characters
Do not enclose characters using quotes " " or ' ' for the following properties: Weight, Rentable, Repairable, Returnable, MAP, Cost, MSRP, List, Effective Date, Reorder. Suite will ignore these and use 0 or $0.00.
Quotes will be used as literal characters for the following fields, meaning "Widget A" will import as "Widget A" instead of Widget A: Product Number, Description, Manufacturer, Color, Class, SubClass, Item #, Item UPC.
Dollar signs are fine for use where a dollar figure is used. They will be imported as literal characters in all fields where alpha/numeric characters apply.
How do I join two columns in Excel?
If a Supplier provides you an Excel file and you need to merge two columns, it can be done using notepad. Copy the contents of those two columns into a new page in Excel. Now save that as a Tab Delimited file. Open that file up and copy the tab space between the two values (no characters just the blank space). Click on Edit > Replace. Paste that "space" (or it might show a small square) into "Find What" and leave "Replace With" empty. Click on Replace All. This clears the "tab" space and you can Ctrl+A and Ctrl+C and Paste that content into Excel as a new column.
White space issues
If you have any problem importing the file, look for white space that might have been saved from the Excel file. Notice the difference between these files below. The file on the right is properly formated. You'll probably need to the reopen the file in Excel and correct the columns and remove the white space or extra columns/records.
Header order
The headers in your txt file will be the same as the left hand column in Suite. The order on the right are listed above and never change.
What are these blank spaces in Suite?
