Data Migration File Requirements Reference

Historical data migrated into SmartSimple must meet specific requirements for data integrity, extraction, schema, and file attachments. 

Data Integrity Requirements

Data imported into SmartSimple must meet these integrity requirements.

↑ Back to top

Data Extraction

Supported Formats

SmartSimple accepts historical data in these formats.

• Plain text files (CSV, XML, or JSON formatted)
• Microsoft Excel
• Microsoft Access database
• Microsoft SQL Server database (under 10,200 MB)
• MySQL database
• Oracle database

↑ Back to top

Recommended Export Practices

These practices reduce import errors during data extraction.

• Export using UTF-8 character encoding to avoid encoding issues with special characters and language support.
• Convert NULL values to empty values to avoid importing NULL as text.

↑ Back to top

Extraction Constraints

Plain text file exports must meet these formatting rules.

• Plain text files must escape delimiter characters.
• CSV values must be enclosed in double quotes, with embedded quotes escaped.

Example

This example shows a correctly formatted CSV file that preserves line breaks and escapes embedded quotes.

"Organization Name","Organization ID","Description"
"Oranges Inc","4321","Peel.
Eat.
Repeat."
"Bananas Ltd","8756","You're going to go ""bananas"" over our product!"

↑ Back to top

Data Schema

Typical Record Types

Data must be provided in normalized form, with the information for each record type represented in a separate table or file. Migrated data commonly includes these record types, though the list is not exhaustive.

• Organizations
• Organization Addresses
• Organization Associations to Grants
• Contacts
• Contact Addresses
• Contact Associations to Grants
• Grants
• Grant Reviews
• Grant Progress Reports
• Grant Payments
• Other Grant Activities
• Funds
• Budgets

Example

This example shows a common guideline for organizing worksheets by record type.

• All contacts on one worksheet, with a unique identifier for each contact and a matching identifier for their organization, for contacts associated with organizations.
• All organizations on one worksheet, with a unique identifier for each organization.
• All grants on one worksheet, with a unique identifier for each grant and matching identifiers for the applicant contact and organization.
• Multiple contacts associated with a grant, such as applicants or reviewers, on a separate worksheet with the grant unique identifier, the contact unique identifier, and the role of the contact association.
• Multiple organizations associated with a grant, such as payees, on a separate worksheet with the grant unique identifier, the organization unique identifier, and the role of the organization association.
• All payments on one worksheet, with a unique identifier for each payment and a matching identifier for the grant against which the payment is made.
• All progress reports on one worksheet, with a unique identifier for each progress report and a matching identifier for the grant against which the progress report is made.

↑ Back to top

Schema Constraints

Data schemas are limited by this constraint:

• The maximum number of columns in any table or file is 100. If more than 100 columns are required, use multiple tables or files that each include the shared unique identifier.

↑ Back to top

File Attachments

Migrating file attachments into SmartSimple requires these two items:

• Zipped file containing all file attachments to upload.
• Mapping file (CSV) identifying the unique record identifier, record type, and relative file path for each file.

↑ Back to top

Zip File Structure

Example

This example shows attachments organized by record type before they are matched to records in a mapping file.

fileattachments.zip
grants\
  grant12345\
    grant_budget.docx
    grant_request.docx
  grant45678\
    grant_budget.docx
    grant_request.docx
organizations\
  Oranges Inc\
    org_mandate.docx
  Bananas Ltd\
    org_mandate.docx

↑ Back to top

Mapping File Format

Example

This example maps the attachments above to their corresponding SmartSimple records using the unique identifier and field ID columns.

↑ Back to top

File Attachment Constraints

File attachments are limited by these constraints:

• Maximum file size is 1 GB per individual file.
• Maximum length of the full path description is 255 characters.
• File names must consist of characters within the UTF-8 character set. It is recommended to avoid special characters in file names, since reserved characters differ across platforms.
• There is no file versioning when importing file attachments into SmartSimple. Duplicate files attached to the same record overwrite each other. For example, if two mapping file rows point to the same file name for the same record, only the most recently imported file remains instead of both files.

↑ Back to top

System Generated Fields

These standard fields are generated automatically by the system and cannot be modified through data import:

• Created Date
• Created By
• Modified Date
• Modified By

Representative dates from another system can be tracked using custom fields.

↑ Back to top

Advanced Data Table Limitations

Importing Advanced Data Table records has these limitations:

• Imported Advanced Data Table records are not displayed until the table is saved manually one time.
• The syntax to display Advanced Data Table records must be hardcoded and revised if the dependent XML field changes, rather than using the @XML reference, which updates automatically.

↑ Back to top