Import New Matters from a Spreadsheet (w/ Video)

Written by Mason Davis

Last published at: March 17th, 2022

Share this article:

Importing matters from a spreadsheet is the primary means for getting your data into AppColl. The following videos and instructions will assist you in this process.


Scroll to the bottom to watch the videos on the below steps.


Click Here to learn about advanced features of the importer.


Attachments

Matter Import Template.xlsx


Populating the Spreadsheet

  1. Open the attached spreadsheet template.
  2. Important: Follow the template's instructions in rows 2-7 for populating your data.
  3. All header rows in red are mandatory fields. Black header rows are optional.
  4. Delete rows 1-8 and 10-27. Now you should have only the header row (labeled in red) and the matter rows as shown below.
  5. Save the spreadsheet. Please note that spreadsheets can be saved as xlsx files or csv.


Importing the Spreadsheet

  1. Go to the Matters module and click on the "Import CSV/XLSX" link in the upper right corner of AppColl.
  2. From the Import window, click the Choose File button to browse for the spreadsheet file on your computer.
  3. Select "AppColl Matters" from the file type drop down menu.
  4. Make sure the "Add New Matters" radio button is selected.
  5. Click the Import button.
  6. AppColl now checks the spreadsheet for any errors. When this process completes, you're taken to a page that shows any issues. If there aren’t any errors, click on the "Import All Rows" button.
  7. If there are errors follow these steps:
    1. Click on each error row and fix the described issue directly in AppColl. Cells in Red have an error that must be fixed. Usually the errors can be fixed by clicking on the pencil icon on the red error cell, making the change and clicking the check mark icon. Cells in Yellow are simply warnings and can usually be ignored.
    2. At any time, you can click the "Save and Validate" button to see any fixed rows disappear.
    3. Once all errors have been fixed, click the "Import Valid Rows" button.
  8. When the import is complete, you should see a message indicating success.
  9. Repeat steps 5-7 until all matters are properly imported into AppColl and no errors are reported.
  10. Alternatively, you can import the validate rows only and then export the error rows to a CSV file to fix later on. To do so you would click the 'Import Valid Rows' button on the left.



Ignoring Warnings

AppColl displays a warning (indicated with a yellow cell) if any data is allowed to be updated, but results in some value that does not already exist. The primary examples are contacts records. As you can see in the above screenshot, Acme, Inc. does not exist as a contact. There is nothing that needs to be done since AppColl will automatically create the contact with the proper role of Client. Further, Jane Doe does not exist in AppColl, so a contact record of role = Attorney will be created for her.


Fixing Error Rows

AppColl displays all error rows in the bottom half of the screen. Each error rows indicates something that must be fixed for the row to be imported. At any time, you can click the "Import Valid Rows" button which will import everything without errors. Once done, only the error rows will remain. You can click the "Export Error CSV" button to export these rows and fix them at a later time.


Fixing error rows are best done one at a time. Start with the top error row by clicking on it. The top portion of the screen will jump to this row in the spreadsheet grid. Usually the errors can be fixed by clicking on the pencil icon on the red error cell, making the change and clicking the check mark icon. Once the error is fixed, you can move on to the next one or click the "Save and Validate" button to see this error disappear. Repeat for all error rows. The below section walks through how to fix each error listed in the above screenshot.


Error Examples

Line 3) There are two errors. The first error indicates the AttorneyRef value already exists in Line 2. Since AttorneyRef values must be unique across all matters, a new value must be entered. You can click the pencil icon on the cell, enter a new value and click the check mark. The new value is now saved. The second error indicates that matter type of 'Utility: PCT' cannot use a country of US. While the type cell is highlighted, you can fix this by changing the Country cell to WO. Alternatively, you can change the type to something other than Utility: PCT.

Line 8) There are two errors again. Once is a duplicate AttorneyRef and one is a duplicate application number and country. Fix each value using the pencil icon again.

Line 10) South Korea is not a valid country name according to WIPO. Note that you can use the full WIPO country name or the WIPO 2-digit code, which in this case is KR. Google 'WIPO Country Codes' to get a list of these 2-digit codes.

Line 11) Utility: 371 is not a valid matter type. The initial spreadsheet lists all the valid matter types. In this case, you would want to use 'Utility: National Phase'.


Once all errors are fixed, click the "Save and Validate" button to clear all errors. Once cleared, click the "Import All Rows" button


After the new matters have been successfully imported into AppColl, data for U.S. and PCT patents can be pulled from Private Pair and imported into AppColl via XML files or from the Update Matter button.


Advanced Features

AppColl's importer has a number of advanced import options which can be found by clicking here.


Attachments

Matter Import Template.xlsx


Populating the matter import spreadsheet