Best Practices
      Back to home
      1. Deep Sync Help Center
      2. AccuLeads: B2C & B2B Direct Mail & Email Data
      3. Best Practices

      Best Practices: How to Clean a File for Use with SnapShot and File Suppressions

      Best Practices for Cleaning a File to Upload to AccuLeads

      For a tutorial, check out this video in the AccuLeads Academy playlist on YouTube:

      Remove Non-Standard Formatting

      • Any non-standard formatting can cause delays in the processing of the file.
        • This can include Non-standard Fonts, Frozen Rows, Merged Cells, Cell Borders, and more.
      • The easiest way to remove non-standard formatting is to copy all cells from your current file and paste them as values into a new file or tab.
        • If you use a new tab, delete the old tab before uploading and save the file with a new name so that you do not lose the original file's formatting.
      • We also recommend saving as a .csv or .txt file for the fastest upload speeds and most consistent formatting.

      Column Widths

      • Please note that column widths within your file must fall within the following:
        • Name fields, up to 40 characters long
        • Address field, up to 50 characters long
        • City field, up to 30 characters long
        • State field, exactly 2 characters long
        • Zip Code field, exactly 5 characters long (Only Zip-5s are accepted, and any hyphens or other characters should be removed.)

      The above do not include Headers, but we recommend shortening them to this length so you can quickly see if they meet these requirements.

       

      • If you do need to shorten the columns, you can:
        • Select the Entire Column
        • Select "Data"
        • Select "Text to columns", then select "Fixed width"
        • On the next page of the modal, click the maximum column width you need
        • Select "Do not import column" on the final page

      Concatenate any necessary information from Address Line 2 to the end of Address Line 1, as only one address column can be used. Do not combine an address and a P.O. Box, as this will delay the process. This can be done via Copy/Paste or the "=CONCAT" function in a new column.

       

      Blank Cells:

      It is recommended that all relevant cells contain information. If any cell is missing data, it must be added, or a placeholder can be added if the information is not known.

      • Sort columns A to Z and scroll to the bottom to ensure each field is populated. If any information is missing, fill it in or delete the record entirely. 

      Deleting empty cells and columns only applies to suppression file uploads. If you are cleaning a file for an email append, you should not delete the empty cells and columns. Our email append is performed using a 4-pass process, and we may be able to locate an email based on the information presented.

      Special Characters:

      All Special Characters (e.g., periods, hyphens, slashes, dollar signs) must be removed.

      • Use a macro or search for these characters using Ctrl+F so they can be found and removed.
      • Any characters on your keyboard that are not letters or numbers (including punctuation) will fall in this category and will need to be removed.
      • "Carriage Returns" (adding a new row within the same cell) should be removed as well.
        • These can be found using Ctrl+F and pressing Ctrl+J in the search field. This will show a small flashing dot in the search field for all carriage returns.

      Non-ASCII Characters:

      These are characters that do not fall within the standard ASCII character set. If these characters are present, our system will not recognize them and will delay the process.

      Examples of these characters include ó, é ⍰, half spaces (characters that create blank spaces that are only half the width of a normal space), and more.

      The steps to remove the non-ASCII characters using the program "Notepad++" are as follows (using a different program may require different steps):

      1. Save a copy of the Excel file as .txt (tab-delimited) and then close the Excel file.
      2. Reopen the Excel file (.xlsx or .csv) in Excel and open the .txt version in Notepad++ (you can do this by dragging the .txt file into a blank Notepad++ sheet).
      3. Within Notepad++, select "Search" in the top bar and then select "Find characters in range..." at the very bottom.
      4. In the opened dialog box, make sure "Non-ASCII Characters (128-255)", "Down," and "Wrap around" are all selected. Then click on "Find," and the text file will jump to the first non-ASCII character.
      5. Using the line # on the left of Notepad++, I got to the same Row in the Excel file. In the Excel file, you can use the Go To function by pressing Ctrl+G and then entering the column and line number that was seen in Notepad++ (for example, if the non-ASCII character is in the third piece of text in line 72,115, you would input "C72115" if the cell is Column C and Row 72115).
      6. After finding the cell in the Excel file locate the character to be removed and either delete in the Excel file or copy the character and enter it into Find and Replace (ctrl+H or ctrl+F second tab) and in the replace box enter a character that can replace it such as "a" for "α" or a character that can be removed by the cleaning macro such as "?" if it is an invisible character or a character that does not need to be replaced.
      7. If you replaced it with a "?" or similar character to be removed by the cleaning macro, run the cleaning macro now.
      8. If the number of replacements using the Find and Replace function was high, you may want to save the Excel file and repeat from Step 1, as the Non-ASCII Character Search in Step 4 only pulls one character at a time and will not skip over any already found characters. If the number of replacements is low, you can continue from Step 4 by clicking "Find" to pull up the next instance of Non-ASCII Characters. Repeat the steps as needed until there are no remaining non-ASCII characters.
      9. Save the Excel file, which should now be clean. To check if the file is clean, save the new clean Excel file as a .txt (tab-delimited) file and drag it into Notepad++. Repeat Steps 3 and 4. If the Notepad++ file does not find any non-ASCII characters, the file is now clean.

      After a file is cleaned, it is good to upload it into AccuLeads for suppression. Make sure you select the correct database(s) from which you will be suppressing the file. If your file has headers, ensure you select this box when uploading. The fields must be mapped to the correct columns; the system will do its best to try to match them for you, but you may need to change some. I always just use the same header names on each suppression file to make sure this maps correctly each time:

      • FIRST
      • LAST
      • ADDRESS
      • CITY
      • ST
      • ZIP

      After you have uploaded a file, you can check on its progress by hovering over the "History" tab and selecting "My Analytics/Services". Then, you can go into Customer Suppression to check the status of any suppression. Ensure that the "Service Status" is not delayed; if it is, additional cleaning may be required. Please reach out to the Client Success team if this is the case.

      Once a suppression is ready, it will display "Complete"; you can then apply it to a count. Just copy/paste (or look up) the Order ID in the "Order Suppression" page when editing a count.

      AccuLeads Support

      If you need help, please call our Client Success team at 800-732-3440. For technical support, please call the AccuLeads HelpDesk at 800-732-3440.