Sales
      Back to home
      1. Legacy
      2. Sales

      Import Sales History from Spreadsheet

      Use the Import Sales History from Spreadsheet option on the Product Setup Tool to import sales history from an MS Excel spreadsheet. For example, you can use this option to import sales history from a legacy software.

      What do you need to know?

      • Imported invoices are assigned a Closed status: Evosus Data Conversion is also added to the Comment field on the invoice (Customer > Open a customer > Invoices tab > Open an invoice > Profile tab).

      • Legacy customer numbers must be set up on customer accounts: The invoices are associated with customer accounts using the legacy customer number. Legacy customer numbers are assigned to customer records using the User 1 field on the Customer screen (Customers > Open a customer > Options tab).

      • The import can take hours: This import can take hours, so you should run the import at night.

      • It's not like other imports in the application: The data is immediately imported as it is validated. This means you don't preview the data before it is imported.

      • You can import the same spreadsheet more than once: If a record is a duplicate record based on the invoice number and legacy customer number, the invoice is not imported. This means you can import the same spreadsheet more than once. For example, if some of the records in a spreadsheet generate errors, you can fix those errors and import the spreadsheet again and the system will not create duplicate records.

      • MS Excel removes leading zeros: MS Excel removes leading zeros because by default all cells are numeric data types. For example, 0001 becomes 1. This can be problematic when importing UPC codes, vendor codes, or any information where the leading zeros are important.



      Step 1: Prepare the data to be imported

      1. Create an MS Excel spreadsheet that contains all of your sales history.

      • Create multiple spreadsheets if you have more than 500,000 records. We recommend no more than 500,000 records in a single MS Excel spreadsheet. If you have more than 500,000 records, break the spreadsheet up into multiple spreadsheets and import the data in chunks.

      • Don't split up an invoice if you create multiple spreadsheets. If you have to split your data into multiple spreadsheets, do not split up a single invoice across multiple spreadsheets.

      1. Add column headings to the spreadsheet(s).

      • Each spreadsheet must have the following column headings in the correct order.

      • You can also create a spreadsheet template using the Make Spreadsheet Template button (Product Setup Tool > Import Sales History from a Spreadsheet > Make Spreadsheet Template).

      • InvoiceNumber: 25 characters maximum. Any characters over 25 will be truncated.

      • If the InvoiceNumber is blank, the record will be assigned the next available invoice number in the application.

      a. InvoiceDate: If you leave this field blank, the import will use 1/1/1999 as the invoice date.
      b. LegacyCustomerNumber: The legacy customer number is entered on customer accounts using the User 1 field (Customer > Open a customer > Options tab > User 1 field).

      • 50 characters maximum.

      a. ItemCode: 25 characters maximum. Any characters over 25 are truncated.

      1. (Required) Sort the spreadsheets by InvoiceNumber and LegacyCustomerNumber. This groups all of the invoice items together.
      • This is important! But why? - The import starts at the top of the spreadsheet and reads each record. Each time a LegacyCustomerNumber or InvoiceNumber changes on the spreadsheet, the import automatically creates a new invoice. This means if the spreadsheet is not sorted by InvoiceNumber and LegacyCustomerNumber, the invoice items will not be grouped together, and the import will create a new invoice for the first invoice item that it encounters, and all subsequent invoice items will trigger the duplicate invoice error.  


      Step 2: Import the data

      1.  Double click on the Import Sales History from a Spreadsheet option on the Product Setup Tool.

      • If the Product Setup Tool does not appear when you log in, enter YES in the Product Setup Mode field on the System Parameters screen (Administration > System > Evosus Defaults > System Parameters > Product Setup tab).

      1. A screen displays general information about importing data from MS Excel.

      2. Click OK. The Import Legacy Sales History screen appears.

      3. Select the spreadsheet in the Import This Spreadsheet field.

      4. Click Import Legacy Sales History.

      • The data is imported immediately! - Unlike other imports in the application, the data is not staged. The data is immediately imported as it is validated.

      • Stop the process if you get a bunch of errors! - Click Stop Processing to stop the import process. Once the process stops, fix the errors in the spreadsheet and then import the spreadsheet again.

      1. Error messages are displayed in the lower portion of the screen.

      • Invoice is duplicate: A record with that invoice number already exists. This can happen if you did not sort the data by invoice number, or you split up a single invoice across multiple import spreadsheets.

      • Customer Not Found: A customer record with the LegacyCustomerNumber was not found. The invoice is assigned the Customer - Default Customer. To view the invoice, go to Employee > Open Invoices > Select Closed in the Status field.

      1. Fix any errors and import the spreadsheet again.

      • Remember, you can run the import more than once on the same spreadsheet! - If a record is a duplicate record based on the invoice number and legacy customer number, the invoice is not imported. This means you can import the same spreadsheet more than once. For example, some of the records in a spreadsheet generate errors, you can fix those errors and import the spreadsheet again and the system will not create duplicate records.

      • Delete Legacy Sales History: You can use this button to delete all of the sales history invoices that you have already imported. This will delete all invoices with a status of Closed, and a comment of EVOSUS DATA CONVERSION. 


      What if the item code is not set up in the database?

      • The invoice is linked to an item code with a description of MISC.

      • If that item is not found, the invoice is linked to an item code with a description of MISCELLANEOUS.

      • If that item is not found, the invoice is linked to the first active item code in the database sorted by item ID. Generally, this is item 1.

      - ItemDescription: 80 characters maximum. Any characters over 80 will be truncated.

       - Quantity, UnitPrice, UnitCost - Must be a number up to 11 characters long. Negative numbers are allowed. These fields cannot be blank.