Q: I want to import my customers and suppliers from my old system into MW. What is the easiest way to do this?
MoneyWorks Express and MoneyWorks Gold can import name records, exported from most legacy accounting solutions. Although all accounting systems are different or proprietary, they are all databases, consisting of tables (lists) tracking various fields (types of labeled information). In order to move the data in these lists from one system to another, you must export the lists in a text format from the old system and line up that text file to match up the field names in the new system.
Express & Gold are very flexible in exporting and importing records (names, items) and or transactions, with a unique and extremely flexible mapping system. However the first step is to export the names, in this case customers from your old system and that will take some knowledge of excel or numbers for Mac purists.
The Export text file
Export the names records suppliers & customers from your old system as tab-delimited or c.s.v. text files. Which can then be opened in excel or numbers and viewed. Viewing in your spreadsheet, it is a good time to clean up your data, adding missing last names, getting rid of duplicates or old suppliers that you no longer deal with etc.
Note: Intuit products Quickbooks, Quicken etc. are sometimes limited to their proprietary .iff format. In that case, export as iff and when you open the export file in excel or numbers, the top 20 rows will have gibberish code. Just delete the 20 lines and you will be left with your records in rows and columns, identical to the tab-delimited format.
Additionally, some versions of Quickbooks store the billing addresses and the delivery addresses in separate lists (tables). So you might have to do two exports for customers and two for suppliers. If so add the two customers lists to the same spreadsheet. Remember each row is an individual customer/supplier record and since the order of names will be the same in the rows, just copy the whole delivery address spreadsheet and paste it into the top row of the next empty column in the original.
You should now have two export files (spreadsheets), one for customers and one for suppliers, where every row is a record for one customer and every column is a field type e.g. company name, address 1, address 2, contacts, telephone etc. If using Excel save the export files as tab-delimited text file, if using Numbers export as a c.s.v. format.
Create a New Customer Record in MoneyWorks
The easiest way to line up the columns (fields) in the order that MoneyWorks requires (mapping) is to create a test customer in MoneyWorks first (fill in all the information or fields that you could possibly use)
and then export it out as a text file (highlight the record go to File > Export selection).
Or just highlight the name in the Names List and copy (Cmd key + C on a Mac or COTRL + C on Windows) and then paste into excel or numbers (Cmd + V).
The first row of the spreadsheet is the field names used in MoneyWorks and the second row is the test names record that you created. Now copy the columns of information from your export file into this new MoneyWorks export file.
Note: where the MoneyWorks record assigned an AR account and an AP account, use that value for all the other rows that you adding. The MoneyWorks record also has a unique Code field for each names record. Assign a unique alphanumeric code (max. 11 chars) to each row (names record). See chart of rules at the bottom.
By keeping all the columns (even those you are not using) not deleting them and keeping them in the same order, your complete list will then line up when you import it back into Moneyworks.
Select Import > Names under the file menu and then choose your loaded text (.txt) file.
Or alternatively drag the text file in to the open Names list.
Either way the MoneyWorks import Map will open. with you file loaded and in the correct order as the Import Map.
As shown above the Import Map consists six columns. The three on the left are the data that you importing, in the order of the columns on the import file. The two on the right consist of the field names that MoneyWorks uses in it’s names records. If you used the import method above the fields will line up in the same order. The arrows in between can be checked on or off, meaning bring in that field’s value or not.
Alternatively if you were bringing in a raw list of records sorted by columns, you would move the Movable MoneyWorks fields up or down to line up with the fields of the raw list, as descibed above.
Assuming the fields line up, you need to let MoneyWorks know what type of file you are bringing in. At the bottom left of the map choose the File Format from the drop down list [Red arrow]: Tab-delimited for excel or Comma-delimited for the c.s.v. file format. The Map will then line up the fields independently. Click Ok.
Note: any one record that is too long or is using a wrong characters or missing key fields, (in other words MoneyWorks’ necessary fields and rules) the import will not work. However by checking Skip Bad Records [Green arrow above], MW will import all the correct and usable records and produce a reject list of the ones that it cannot. You access the rejects by clicking Rejects button on the import window.
The bare essentials
Which brings us to the last bit of useful information. The chart below lists the names fields, their purpose, the maximum number of characters and the character type e.g. text meaning alphanumeric text. Note: downloadable pdf version of the chart is here.
The mandatory fields are:
Code: as described is a unique 11 alphanumeric code assigned to each customer record. As an aside, many new customers spend way too much time defining a coding system. In reality, the customer’s name (e.g. company name) is more important as all customer lists or customer choices show or auto fill by the Name field. The Code can be any unique ID, in fact you can set the Code field to Work it Out and MoneyWorks will assign a unique code.
Name: the company name or if the customer is a person their name, the one you want to invoice as a customer sale or buy from as a supplier.
Customer Type: MoneyWorks tracks two types of customers. COD customers that you only sell to via receipts, in other words no invoicing or terms of sale and debtor customers who you invoice and give terms of payment to e.g. Net 3o days etc. Use 1 if the customer COD only, Use 2 if you offer terms of sale via invoices.
Supplier Type: Similar to customers, you can may have suppliers who only sell to you via COD (use 1) or those that offer you terms of sale via invoices e.g. AP, payables. (use 2). If as in this example, we are only importing a customer list we would enter 0 – zero as the supplier type. However, if the customer was also a supplier you could assign them as a value of 2 under the Supplier Type column as well in your import file.
RecAccount: is the Accounts Receivable account code. If you have not added multiple AR accounts to the chart of accounts template you chose when you created your company file, similar to the Code field above, you can set this field to Work it Out and MoneyWorks will assign the AR account. If you did create multiple AR accounts, assign the correct one to each customer in your import file.
PayAccount: is the Accounts Payable account code and similar to the RecAccount above you can set the field to Work it Out, if you only have one.
TaxCode: Set to Work it out or if certain customers had different tax rates, your import file would contain the MoneyWorks tax code that could be assigned to this customer as a sales tax override.
Currency: If you do not use multiple currencies (Gold feature) or have not yet turned the feature on or if you are using Express leave this field blank in your import file and your base currency (USD or CAD) will be used. In other words only mandatory if you are using multiple currencies in MoneyWorks.
Recommended and Optional
The green fields listed in the chart above are recommended, in that they will provide the information for invoices etc. that most customer would like to see. The yellow fields marked optional are for those customers that really want to get into it :).
As mentioned at the beginning a working knowledge of excel or numbers is required and it does take some time to line up your import file. If you do not feel adventurous, you can enter names one at a time or if you have too many to do that, hire a MoneyWorks consultant to import the records for you.
For more information about MoneyWorks Support in Canada, in the USA, or in the UK contact www.moneyworkssupport.wordpress.com