Import Component Lists from Excel or CSV files

The Import Data function allows you to import a list of components for Assembly and/or Kit items from products that already exist in the system using the Product Component import type. The import will assign existing products as components of existing kits or assemblies items.  The system needs an Excel spreadsheet or .csv file for the import, which only has to contain the Finished Good Product ID, Component Product ID, and Component Quantity for each kit or assembly, but it can also contain the Warehouse, Sequence, Notes, and specify which components have Variable Quantities. 

Both the Finished Good and the Component Products need to exist in the database before importing. Also, the Finished Good must have a Bill of Material type specified.

Select Import Type [1/5]

  1. Select File > Import Data to open Select Import Type, the first of five windows in Import Data.
  2. Click Product Components from the Inventory folder in the #1 Select Document Type window.
  3. If you’re creating a new template, click Create Mapping on the right side of the screen in #2 Select Mapping. It is necessary to create a new mapping profile for the first time that you import a file. When modifying an existing template to use a different spreadsheet, select the specific mapping and click Edit Mapping.
    Product component import

Mapping Editor [2/5]

  1. Choose the Excel (xls, xlsx) spreadsheet or CSV file you wish to import in the Filename box within the Source Spreadsheet section. After selecting the file to import, the information listed in the file will be displayed in the Preview window on the right-hand side of the screen. Each column will be labeled A, B, C, and so on, depending on how many columns there are in your file.
  2. If the file contains multiple tabs, select which one you want to import by using the drop-down menu in the Worksheet field.
  3. Check or uncheck the First row of spreadsheet contains field names checkbox depending on the values of the first row on the worksheet. If the first row contains the titles of each column, check the box to view the titles as they are in the file. These titles will not be used in the import process, but rather as a reminder of what information is listed in each column.
  4. In the Summary section, give the template an ID, Name, and Description to identify what you’re importing, as well as marking the mapping as Active (or unchecking the Active box to make it Inactive). Use relevant, unique information for easy recognition in case you wish to go back and reopen the saved mapping at another time. If you’re editing an existing map, you won’t need to specify this information unless you simply want to update it.
    Product component import Summary
  5. To map the spreadsheet columns to fields in Acctivate, start by selecting the Columns tab. It’s best to name the columns in your spreadsheet as something relatable to the field it will be imported into on the Product Component list. The Preview section of the mapping editor shows you which column has been mapped to each field in Acctivate. The top row shows your spreadsheet headers and the second row shows the field it’s mapped to.
  6. Click on the specific Acctivate field you wish to map to make that row active and choose the column from your spreadsheet in the Mapped field section.
    Product component mapped field
  7. Repeat this process until you have mapped all fields in your spreadsheet you wish to import.
    • For creating new products, you’ll be required (key and R icons) to have something mapped for Product ID, Component Product ID, and Quantity. The remaining fields are optional.
    • If you would like the components to show on the work order in a specific order, add a Sequence to your spreadsheet and map to this field. Also, components with Variable Quantities for Custom/Process assemblies should be marked as such.
    • Feel free to set Default values for anything with the same value across the board or left blank in a column. Let’s say all the items are variable. In this case, you can set Variable Quantity to have a Default value of Yes rather than adding a column to the spreadsheet and setting all the values to be Yes. The same goes for a column with blank cells that should all be the same value.
  8. Most customers can leave the Options tab as is. These advanced options tell Acctivate how to handle duplicate and missing information. For more information about these options and how they affect the import, check out the Advanced Import Options article.
  9. Once the mapping has been completed/updated, click the Save Mapping button.
  10. Click the Begin Import button, after you have reviewed the information in the Preview window.
    Product component begin import

Data Editor [3/5]

  1. The third window, called the Data Editor will open.
  2. Depending on what you want to view in the Data to Import data grid, check or uncheck the checkbox near the top that reads Show Only Invalid Rows.
  3. Near the bottom of the window in the Statistics section, the number of passed and failed rows will be shown, as well as a total row count. Please review this information and act accordingly.
  4. If rows failed (unchecked), an explanation of the error will be displayed in the Errors section, as you select each line.
    Product component Data Editor
  5. There are a few options for resolving errors:
    1. Edit the data from this window. This will not update your spreadsheet, but it will allow you to edit the data in order to import the failed rows.
    2. Click the Back button, click on the Mapped field with the error, and Show Conversions. This gives you the ability to “convert” values in the spreadsheet from incorrect to correct data. Specify the bad information in the From field and what the value should be in the To field:
    3. Close the Import Data utility, update your spreadsheet, and start the import over.
      Note:
      Acctivate can create a spreadsheet with all the rows that failed the import (this step is detailed in the Importing [5/5] window section, step 2).
  6. If the error is not in the source file, the error could have been made when mapping. If this is the case, click the Back button and in the Columns tab review your mapping. Re-save if changes are made.
  7. When ready, click the Next button.

Import Confirmation [4/5]

  1. The fourth window, Import Confirmation will open.
  2. Before actually importing the data, review the statistics given in this window.
    Import Confirmation
  3. If you approve the provided statistics, click the Finish button. To correct failed
    rows, see step 3 of the Data Editor [3/5].
  4. Select Yes, from the pop-up window to backup your database before importing. Database backup is strongly recommended.
  5. The Backup Company Data screen will pop-up. Fill in the necessary fields and check boxes.
  6. Click the Start Backup button.
    Backup
  7. You will see a “[…]Do you wish to proceed?” prompt. Select Yes.

Importing [5/5]

  1. The Importing will open. This window is where the actual data is being imported into Acctivate. A progress bar will show progress as the data is imported into Acctivate. Importing may take a considerable amount of time depending on the size of the file being imported and the speed of your system. Never end task on an import. There could be a large list of rows causing it to seem locked up or frozen or an exception spreadsheet being created for any errors.
  2. If there were errors, you will be prompted to create an exceptions spreadsheet. This spreadsheet can be used to cleanup any bad data and re-imported without having to manipulate the original spreadsheet. Each row in the exception spreadsheet will list the specific error that caused it to fail. Click Yes and select a location to save it.
  3. A pop-up window will appear when the Import process is complete. Click OK on this pop-up window.
    Import complete
  4. Statistics (as described in the previous steps), as well as, the number and types of records created and updated in Acctivate will be given in the window. If a spreadsheet was created with the failed rows, the Directions Section will appear in this window. It tells the user where the file was saved so it can be opened in another program (i.e. Excel) to make your changes and import the modified spreadsheet. Click the Open button and the spreadsheet will open with all the failed rows, which will include the row number as it relates to the your original spreadsheet and exceptions (a brief explanation of why each row failed the import).
  5. Click the Close button.
  6. Open the Product Information window in Acctivate to view your new/updated product component lists.
Tagged with: , , , , , , ,
Posted in Import Data, Inventory - Last modified on May 9, 2016Lauren Stine
Still need help?
Search all content on this site, contact support at 817-870-1311, or create a support request.