Q: I am in the process of bringing our 3000+ items in to our new company file in preparation for the conversion. How do I import the products.
MoneyWorks Express and MoneyWorks Gold include importing and exporting and will import items lists e.g. products, services etc., with the main difference being that MoneyWorks Gold will manage inventory, tracking qty. on hand etc. and Express is just an item list with an assigned income account when selling and cost of goods account when purchasing, there fore not tracking qty on hand.
Exporting the legacy item lists
The first step is to export your items list from your legacy accounting system. Export formats are generally text files: tab-delimited text (preferred), c.s.v. (comma separated values) or .iff the proprietary text format of QuickBooks. The text files can be opened in Excel or Numbers. Note: that when you open an .iff file you will find about 20 rows of gibberish code at the top or bottom of the list depending on which QB you have. Delete those rows and you are left with the data in a tab-delimited format.
The individual columns, usually labeled in the first row represent the different product fields that your legacy system used and or calculated based on transactional history (sales year to date etc.). Each row under those headings represent the individual product records.
Note: this is an ideal time to clean up your product lists. If there are discontinued items that you no longer stock, delete those rows. If there are typos in the description or incorrect pricing, correct them. If your you are planning on using MoneyWorks Gold and your exported list contains Qty on hand, make sure the quantities are correct as the opening inventory levels can also be imported as a separate step.
Understanding Import Maps
Obviously all accounting software programs are different with each manufacturer tracking different information (fields) for each item as well as assigning different names to those fields. The easiest way to see the fields that MoneyWorks uses is to create an Item in your new MW company file and fill in as much information as you require.
e.g. new Bronze Widget large filling in the desired information under the Details tab, the Buying Info tab and Selling Info Tab as shown below. Click OK.
Then highlight your new item in your Items List, copy (Cmd + c ) or if on Windows (CNTRL + c ) and then paste (Cmd + v ) or (CNTRL + v ).
MoneyWorks will immediately open the import map as shown below. Obviously, since this is a MW item being imported into MW, the MoneyWorks fields [Red Arrow] line up perfectly the Labels [Blue Arrow] with the labels being those labelled columns that we were talking about earlier. The actual product record(s) are shown individually in the Record column [Green Arrow]. This would not automatically happen if you imported your raw export that uses different field names and in different order.
But it might be worthwhile to see the actual Map and fields that MoneyWorks uses at this time. Note this is a MW Gold file.
While here, let’s look at some the different options you have when importing records or for that matter anything into MoneyWorks. The options are at the bottom ofd the map as described in this blown up screen shot.
You can choose the file format [Red Arrow] that you are importing with the default being tab-delimited with field names, or for c.s.v. mentioned above choose Comma-delimited etc. Import maps once lined up can be saved by future use (Orange Arrow]. You can set the option to just update existing records [Blue Arrow] which we will use later to bring the opening inventory levels into MoneyWorks Gold and you can also check Skip Bad Records [Green Arrow] where will create an accessible text file of the records that could not be imported, so they could be correct and re-imported. Typically used when bringing in 1,000s of records at one time. Left unchecked any error in any record will stop the import.
Lining up your import map, the short cut.
Close the map above by clicking Cancel and then re-copy the new Item record in the Items list and this time paste it into an new Excel or Numbers file.
Alternatively you can highlight the item in the list and then export it to your desktop, by going to File menu at the top of your computer screen, selecting export.
Exporting will create a tab-delimited text file containing the field names as column headers in the first row and the item record in the second row. Save to your desktop and open the file with Excel or if using Numbers drag the file to the Numbers icon in your dock.
In either case the Item record will automatically be entered into the Excel/Numbers file with the First row containing the Field Names in each column and the second row being the information we entering into those fields when we created the Item. The columns/fields will also be in the field order of the default map shown above, with left to right equalling top to bottom.
Now open your exported file and copy columns of information from the export file and paste into the MoneyWorks export, e.fg. product description, selling price, cost price etc. Where the MoneyWorks line Item (Row 2) is using a specific account code, fill or paste those account codes in the column for the the rows that you have pasted in.
e.g. SalesAcct, StockAcct (Gold only) and COGAcct.
Likewise with the “ConversionFactor” and “Type” columns.
The only other key field is to assign a unique Code for each item. This is 15 character code, alphanumeric. However keep it simple as you always search by the description or name of the product. Once complete. delete the row 2 product that you originally create from the spreadsheet and save the file as a tab -delimited text file, or if using Numbers export as a C.S.V. file.
Then import the file into MoneyWorks (File menu > Import > Items. Find the file and click OK and you will be back at the Import Map that was shown above with all the fields lined up. Assuming of course that you not delete any columns or rearrange their order.
Learning the Fields
As briefly referred to above certain Item fields are mandatory and of course each field has different lengths etc. This chart lists the Field characteristics, mandatory, optional etc as well as descriptions, characteristics etc. They are listed in the default import order.
As you can see in the chart above, very few fields are mandatory and are the only fields that MoneyWorks requires in order to create an Item record. However many of the mandatory fields can be set to default values when importing.
Note: For further reference a PDF of chart is available here.
As an example even a simple Items List with just a description cost & selling price can be mapped and imported into MoneyWorks as items.
The only field that needs to be filled in before importing would be the unique Codes which are entered above starting at 100. Saving this file as a tab-delimited text file, you can simply drag it into the open Items Window and the import Map will open.
In this map you can move the Destination Fields [Red Arrow] up or down in the list to align with the four source fields or columns in the spreadsheet (Code, Description, SRP & Cost). Then for other mandatory fields you can set a value by clicking into Value column [Blue Arrow] for a particle field e.g. setting the SalesAcct field to account 4000 (from the chart of accounts) [Green Arrow]. Then likewise assigning a value for StockAcct, COGAcct, ConversionFactor, and Type fields.
In other words the import mapping is very flexible, creating Items with limited information as above or if your inventory listings are more complex you have sixty one fields to work with, that you would line up in your spreadsheet before importing.
For more information about MoneyWorks Support in Canada, in the USA, or in the UK contact www.moneyworkssupport.wordpress.com