V8 - Introduction to exporting, bulk editing, and importing data

SOS Inventory's tools for exporting data and importing data allow you to edit your data in bulk instead of one piece of data at a time.

If you need to make only a few additions or edits to your data, the most efficient approach is to do so directly within SOS. However, if you need many additions or edits to your data, the import and export data tools of SOS Inventory are the way to go. These tools are very powerful. You must use them carefully and understand how they work, so that you don’t accidentally overwrite your data.

The Export data and Import data features are found under the Data tools section of the Company menu. All data functions are performed via Excel or CSV files. The Bulk edit page found under the Data tools section is a brief explanation of the bulk edit process that is described more fully here.

The process for editing data in this manner involves three basic steps:

1. Export the data type you need to modify. In the Columns section of the Export page, be sure to select Add all columns required for export. The file can be exported in either an Excel or CSV format. For new SOS customers with no data in the system, the exported file will serve merely as the template for you to add your data.
    
   edit_noteNOTE: It is recommended that the Excel format be used for exporting items, since the possibility of long numeric fields (such as SKU) exists. If CSV is used for exporting items, the long numeric fields would be converted to exponential notation when the CSV file is opened in Excel.
2. Edit the data as needed. Review Guidelines for editing data, along with specific procedures and/or column descriptions for the type of data you are editing.
    
3. Import the edited Excel or CSV file back into SOS Inventory.

Data types available for export

The content of exported data is informational only. But once exported, it can be edited as needed and imported back into SOS Inventory.

The types of data you can export depend upon your SOS Inventory plan.

How to export data

To export data from SOS Inventory:

1. Go to Company menu > Data Tools > Export Data, then select the type of data you wish to export. The system will take you to the export page for that data type.
    
2. Select your desired As of date (for items) or Date range (for transactions), Filters (a location for items is required if you are changing quantity on hand and value on hand), Options, and Columns to include on the export. If you are intending to make changes and import the data back into SOS Inventory, click the gray button under the Columns section that states Add all columns required for import.
    
3. If you anticipate reusing the export, you can add the export type as a favorite, save how you customized it, or schedule it through the Manage section. Favorites, customized, and scheduled exports will be added to the Export data page.
    
4. In the Actions section, select how you wish to obtain the exported information.

• Display export. Data appears on the bottom of the export page in SOS Inventory. You may then print an uneditable hard copy of the data.
• Save as Excel/CSV. Downloads the data to your computer in the file format you specify (Excel or CSV).
• Send via email. Send the data as an email attachment in the file format you specify (Excel or CSV).

Guidelines for editing data

When performing mass edits, follow the guidelines listed below. They will help to ensure that your updated data are successfully imported into SOS Inventory.

• DO make a backup copy of the exported data file before editing.
• DO leave the Id column (ItemId, CustomerId, or VendorId) blank when inserting a new row. The system will assign a number when your edited file is imported back into SOS Inventory.
• DO make sure that the Id column (ItemId, CustomerId, or VendorId) stays matched to the proper row. For example, if you upload an item file containing both new and existing items, the system will first attempt to match the ItemId to an existing ItemId in the database. If it cannot find a match, it attempts to match the Name. If it can’t find a match, then it creates a new item. If you accidentally jumble the Id column across rows, SOS will find a match, but it might match to the wrong row, which can cause a big jumble in your data, both in SOS and in QuickBooks Online.
• DO delete a row from the spreadsheet if no changes will be made to it.
• DO break long files into multiple sheets. The upload will be faster if you limit the number of rows to under 900 per sheet. The system will prompt you to indicate which workbook sheet should be imported.
• DON’T change any of the column headings. The format of the template is fixed, and it is very important. If you change or delete column headings, your import will likely fail.
• DON’T leave any special features in the file (filters, formulas, freeze panes, hide/unhide columns or rows, etc.). You may use these while editing the file, but remove or turn them off before saving.

Importing data

Importing data to SOS Inventory requires that the uploaded data be submitted in a format that the system can read.

Currently supported data for imports are:

• Items
• Customers
• Vendors
• BOMs
• Price tiers
• Sales orders
• Sales receipts
   

Steps for importing bulk edit data

1. Go to Import data (Company menu > Data tools > Import data).
    
2. Select the type of data you want to import.
    
   edit_noteNOTE: If your spreadsheet file has multiple sheets, the system will let you know that it cannot continue until you select which sheet to upload from a dropdown list of the
   sheets displayed.
   1. If importing items:
      1. Select the location.
      2. If item changes include updated quantity on hand and value on hand, make sure that the As of date matches the date that you chose when you performed the data export.
          
3. Choose the Excel or CSV file containing your data.
    
4. Select Preview. Up to ten rows of data will appear for your review to ensure that you are importing the correct file.
    
5. If everything in the Preview looks correct, select Import. The system will begin to process the data.

SOS will issue a message that lets you know whether the import was successful. If you receive an error message while attempting to import or edit items, the cause is almost always a missing or incorrectly titled column. You must keep the columns in the template and leave the headings the same, or the upload will not complete properly. If you are unable to determine the problem, please contact Support for assistance.