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.

The file you are importing should have no additional formatting, like the file in the screenshot below. Please note your fields may vary but will need to include specific information depending on the purpose.

mceclip1.png

Data Preparation Specifics

File Size

  • The maximum file size is 75 MB
  • If your file is larger than 75 MB, consider rolling it up to the ZIP+4/postal code level and creating a weight field (weight fields represent the number of records per ZIP+4/postal codes)

Headers and Field Names

  • 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
  • Field names are necessary and must be fewer than 50 characters in length
  • Location files need
    • A Name/Label Field i.e. Store ID or Store Name
    • A proper street name (e.g. 55 York Street), when geocoding using a street address

Example 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)

 
Example address name:

55 York Street 
210 Rue D'Iberville 
770-340 12 Ave SW

  • Number preceding address, you can also use abbreviations (ex. ST, AVE, CRT)
  • 'Rue' preceding or following street name, along with English translations are acceptable 
  • Only use a hyphen for addresses with unit/suite numbers

Formatting

  • Regular text works best (no formatting or accounting symbols on dollar values)
  • The file should be formatted as a CSV

Specific to ENVISION USA Workspaces:

  • 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

Geocoding Requirements 
It is crucial that the import type reflects the data within your file. The following table will outline the appropriate import type based on the file type you are importing. 

File Type Data Type Import via
Area file Geographic Codes Geographic Codes
Area file Spatial object fields Spatial (Polygon)
Location file Address Geocode Address
Location file Latitude & Longitude Coordinates
Customer file Geographic Codes Geographic Codes
Customer file ZIP+4 or address/postal codes Geocode
Customer file Latitude & Longitude Coordinates

 

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

Powered by Zendesk