This document includes important steps for importing data via CSV/Excel, as well as common problems and solutions.


Step-by-step guide


Using the Import Tool

  1. Make sure the logged-in user has "add" permissions for the type of records you are planning to import.

  2. Navigate to the Tools menu and select Import.

  3. If you have an existing configuration (.ini) file on-hand, you can load it with the top-left file-open icon.

  4. Note that after you setup the configuration (on the following steps) you can save it to a .ini file for later use (via the save or save-as icons at top-left).


  5. Choose a specific file to import via the "Browse Files" button, or specify to import any supported files in the folder chosen via "Browse Folders" (the first supported file will be used for setting up the fields).

  6. If the import file doesn't have a header row, leave "Has Header Row" unchecked at all times.  Even if it does have a header row, leave "Has Header Row" unchecked while mapping fields (it just needs to be checked before you click the "Import" button).

  7. The "Date Format" setting lets you tell the import tool how to parse any imported field that represents a date.

  8. The "Import Type" specifies which type of records will be imported.

  9. When matching the fields, at a minimum all of the "Required" fields must be matched.



    • If you don't have data for a required field (for example Multiple Warehouses is turned off but the Inventory import requires a Warehouse to be matched) you can try to include the column in the import file but with empty values underneath it, otherwise try importing with a known default value (see the "Tips and Tricks" section for more information).

  10. Matching fields must be done one field at a time.  Here is an example for one field (select the field in each list, then click "Match").


  11. If you don't agree with a match, just select it and click "Unmatch", for example:


  12. For import files with header rows, once you select "Has Header Row", you can verify the data will be imported as intended:


  13. Don't forget to set the "Destination" and "Failure" output folders with their respective "Browse" button.  These folders specify where to move the imported file to after the import attempt (for successful and failed imports, respectively).
    • Be sure that the import file isn't open in another application before attempting to import it.
    • The import file (file with the same filename) shouldn't already exist in either of the output folders prior to the import attempt.
    • If you get some error about accessing/moving the file, closing/re-starting the Spire desktop app will release any open file locks.

  14. When you are ready to process your import, click the "Import" button at the top of the window.
    • Imports of several thousand records may take a few minutes - if the import process seems to have halted, just wait a few minutes and do not cancel it.
    • When the import is done, it will indicate how many records were imported successfully.
    • If an error is encountered, the import will be failed and an error message will indicate what went wrong (usually some data isn't formatted correctly or some foreign key refers to a record that doesn't exist).


Tips and Tricks


1. Spire import doesn't convert Excel formulas such as "=A2+B2" to the calculated value (it will try to import the formula's text itself).  For such spreadsheets, please save as a CSV file and then import the CSV file.


2. If importing a CSV file results in some unrecognizable characters in the imported record, try to import it as a CSV that was saved in UTF-8 encoding (this is available via Excel's save-as feature).


3. Importing values that can contain alphanumeric characters may import incorrectly if the value only contains numerical digits.  For example, Sales Order S000001234 should import fine because the "S" definitely indicates an alphanumeric value, however Sales Order 5000012345 may import as an exponential or decimal number due to the way the value is interpreted by the import tool.  To correct such problems, please prefix the value with a single quote character, so the spreadsheet/CSV should have the value '5000012345 instead of 5000012345.

  • It's important to know that if the destination field in the database is actually numerical, such as an integer or floating-point value, prefixing the import value with the single quote will break the import.  The single quote is only intended for destination fields that are textual.


4. The import is case sensitive.  For example, if you are updating values via import for Inventory part number PART1234 and the import file specifies Part1234, most likely this will fail.


5. For failed Inventory imports for companies with Multiple Warehouses turned off, the most common problem is that the imported Warehouse field should be populated with code 00 (in Spire, browse to the Edit menu → Warehouses and verify the Code of the Default Warehouse).


6. If you want Sales Order 12345 to appear as the expected 10-digit code in the Excel spreadsheet intended for import, please use the custom cell format 0000000000 (10 numerical digits) so it appears as 0000012345.  Even if the code is SS00012345 (contains alphabetic characters) the formatting will still work.


7. The Data Dictionary at http://localhost:10880/doc/database/ is very valuable for determining import values, especially for record/field-specific flags, statuses and record sub-types.


8. For more information on how to fill in the different fields of the Import, search the KB articles for that particular import.