How do I prepare data for importing files?

Before you get started with your analysis, it is important that the files you are importing are formatted correctly. This guide will outline file requirements, recommendations, and best practices for importing data into ENVISION.

Data Preparation

  • The maximum file size is 75 MB
    • If your file is larger than 75 MB, consider rolling it up to the postal code or ZIP+4 level and creating a weight field (weight fields represent the number of records per postal code or ZIP+4)
  • Field names are necessary and must be fewer than 50 characters in length
  • Headers must only be one row and in the first row
    • No blank row after your header row (the first row of data should always be populated with data in each column)
    • Refrain from using symbols (@#$%^&* etc.) or punctuation in column headers or in the data fields
    • Clear Formats in Excel to avoid leading or trailing spaces in the file name or header fieldsmceclip0.png
  • Regular text works best (no formatting or accounting symbols on dollar values)
  • Location files need a Name/Label Field i.e. Store ID or Store Name
  • When geocoding using a street address, ensure that proper street names are used (eg: 55 York Street)
  • The file should be formatted as a CSV

Geocoding Requirements for ENVISION Canada Workspaces
Geocoding Requirements for ENVISION USA Workspaces

Geocoding Requirements for ENVISION Canada Workspaces

  • If your customer file has postal codes, you will need to import via:
    •  Geocode
  • If your customer file has a latitude and longitude, you will need to import via:
    •  Coordinates
  • If your location file has postal codes, you will need to import via:
    •  Geocode Address
  • If your location file has a latitude and longitude, you will need to import via:
    •  Coordinates

Note: When geocoding, we recommend including street address, city, and province, alongside the postal code. 

 Sample file names:

 Incorrect  Correct
 062012_retailcustomers.csv  retailcustomer_062012.csv
 customer$_retail.csv   customer_dollars_retail.csv
 mortgages & loans prospects.csv   mortgages_loans_prospects.csv
 members 2020  .csv  members2020.csv (no spaces)

 

Sample address names:

 55 York Street  Number preceding address, you can also use abbreviations (ex. ST, AVE, CRT)
 210 Rue D'Iberville  'Rue' proceeding or following the street name, along with English translations are acceptable
 770-340 12 Ave SW   Only use a hyphen for addresses with unit/suite numbers

 

Geocoding Requirements for ENVISION USA Workspaces

  • If your customer file has a ZIP+4 or address, you will need to import via:
    •  Geocode
  • If your customer file has a latitude and longitude, you will need to import via:
    •  Coordinates
  • If your location file has ZIP+4 or address, you will need to import via:
    •  Geocode Address
  • If your location file has a latitude and longitude, you will need to import via:
    •  Coordinates

 Note: When geocoding, we recommend including street address, city, and state, alongside the ZIP+4 code. 

Additional data preparation specifics:

  • Depending on the inputs of your customer file, geocoding will use a stepped approach
    • If a ZIP+4 is provided for geocoding, the geocoding information will be matched on ZIP+4
    • If there is no match on ZIP+4, or if ZIP+4 is not provided, geocoding will be applied to the combination of address, city and state
    • Should a match not be made, or address information not available, the geocoding match will be made on the ZIP  
  • The field that contains the ZIP+4 code, including the header, must be formatted as text to maintain any leading zeroes

Sample file names:

 Incorrect  Correct
 customer$_retail.csv  customer_dollars_retail.csv
 mortgages & loans prospects.csv   mortgages_loans_prospects.csv
 members 2020  .csv  members2020.csv (no spaces)

 

Sample address names:

 55 York Street  Number preceding address, you can also use abbreviations (ex. ST, AVE, CRT)
 770-340 Main Ave  Only use a hyphen for addresses with unit/suite numbers
 210 12 Ave SW  For numbered streets, do not use the ordinal indicator (ex. st, th, rd, nd)

 

Powered by Zendesk