Importing sales and staff data from a file

Importing data from a third party software which T.M.A.S. does not directly integrate with.

Table of Contents

    Important - Prior to continuing.

    Verify if you are currently using a supported POS Integration and if so, follow the appropriate article to setup.  Otherwise continue to determine if your third-party application meets the following criteria in order to facilitate the process of importing a file into T.M.A.S.

    T.M.A.S. provides the ability to import sales and staff data files which have been exported from another third-party application. This feature is used best when there is a means to completely automate the process.  When determining if this process can be automated, the other third-party application should meet the following criteria, as this could complicate as certain steps in the process requiring the importing user to perform tasks manually.

    Third-Party application criteria

    • Ability to generate a summarized sales file by the hour, (30min or 15min also works). OR generate a transaction log detailing total qty of items sold and total of transaction per line. For more information related to what data should be included in the verify the samples below.
    • Ability to output comma, semi-colon or tab delimited file in txt, csv or xls* format.
    • Ability to schedule the file to be generated daily or hourly (30min or 15min also works).
    • Ability to deliver to the T.M.A.S. FTP server (Your custom FTP server and E-mail are also an options)

    If you are uncertain whether you meet the above criteria, please contact to discuss the possibilities.

    Example of flat file headers for Summary File Type
    Location ID Date & Time Hour (Optional) Sales Transactions Items Staff Hours (optional)
    Location ID:
    This location identifier needs to match the location ID in T.M.A.S. The RefID is usually a 1-4 digit store number. T.M.A.S. software can also apply a “mask” from the beginning of the RefID of the store, as long as it is uniform across all locations. For example, if the ID name is SB0007, this field could contain the complete ID name or simply the last 3 or 4 digits which is the store number (ex: 007 or 0007)
    Date and Time:
    Date for which the data is provided.  Date must be in one of the following accepted formats. Verify the configuration wizard in the following steps for the accepted formats.
    The time (in hour) for which the sales information are provided. This needs to be 2 digits, military format. The number represents the start of the interval. So for sales for 13:00 to 14:00 the number would be 13.

    This field needs to contain the value of the sale for the specified hour. No minimum of digit is required. The number must also contain cents and the cents needs to be delimited by a “.”, as this is a fixed decimal value. For example: 600.76.  Do not use commas to represent values in thousands.

    • If there is no sale for this hour the value needs to be 0.00.
    • If the total sale for the hour is negative, a minus “–“ must be added before the sales amount. Example: -88.67

    Number of invoices generated. This is an integer value. This field contains the number of transaction for the specified hour. No minimum of digits is required. Example, for 6 transactions, we would simply put “6” there. Also:

    • If there is no transaction for this hour the value needs to be 0.
    • If you want to deduce credit, just subtract credits/returns from the transaction number.
    • If the total amount of transaction (invoices minus credit) for this hour is negative, a minus sign “–“ must be added before the transaction amount. If only 2 credits were made during an hour, we would put “-2” in this field for that hour.
    This field contains the total number of items for all transactions for the hour. If there are no transactions for this hour indicate it as 0.
    Staff Hours:

    Number of employees on the sale floor for the specific hour. This can be a real value or integer. A decimal value would be generated when a staff is only present on the sales floor for a part of the hour. If there are 10 employees on the floor then we would put “10” as value for this field for the hour. 0 is accepted in this field if there really is not one on the floor for this hour.

    • It accepts decimal. But these will not be shown in the report, they will be rounded up.
    • An employee is considered to be a staff when he is actually on the sales floor and ready to answer or assist a customer.
    • A manager can be counted here if he is actually working on the floor and assisting customers. If he is doing work in the back store we would recommend not counting him in this statistic.
    • 0 is accepted in this field if there really is no one working on the sales floor.

    How to setup file importation

    Step 1. Creating an FTP account

    1. Expand Administration
    2. Select Manage Locations
    3. Select Import / Export Data / P.O.S. Integration
    4. Select Add
    5. Click Create new Storetraffic FTP Account
    6. Input the E-mail address for the user account
    7. Click Add - User will receive an e-mail from with the password. This account will then be selectable in the StoreTrafficFTP* dropdown.

    E-mail NOT received!

    Contact SMS Storetraffic 


    SMS Storetraffic FTP usage information

    Click here for more information

    Step 2. Accessing the Feature
    1. Login to TMAS
    2. Select Configure
    3. Select Manage Locations
    4. Select Import / Export data
    5. Select Add
    Step 3. Complete General Information
    1. Indicate Label (Name to be given to import job)
    2. Indicate Import Type
      • Fixed Width - Rarely Used 
      • Delimited  - Most Common
      • STC File - Used to import traffic data from another source into T.M.A.S.
    1. Indicate file mask (*.csv or *.txt)
    2. (Optional) Indicate delimiter (Comma, Semi-Colon; or Tab) if Delimiter import type was selected
    3. Status Active as default
    Step 4. Input FTP Settings

    The user has the ability to use the default integrated SMS FTP provider or indicate their own private FTP account by bulleting the following option.

    • Use Default
    • Custom
    FTP - Use Default FTP - Custom
    1. Bullet Use Default - For SMS Provided FTP site
    2. Indicate an Existing FTP User or create a NEW FTP user for the SMS FTP Site
      • To "Create new BFTP Account" do the following
        1. Click the link to Create new FTP Account
        2. Input the e-mail address you want associated to the account
        3. Click Add
    1. Verify / Update - FTP address path points to the Import folder or root by default.  (Optional) check off change default path and indicate a new path if other folders were created in the SMS FTP site. Note* This process will not create folders on the FTP, you would need to login separately to create this.
    2. Click Next


    Please contact SMS Tech Support and be ready to provide information related to your specific FTP provider as we will need to whitelist it on our firewall in order for it to be used.  Also, in the event that alternate ports are used than your typically default ports for the FTP transport protocols, additional fees related to the opening of these ports and testing may apply. 

    (Common ports are FTP=21, FTP/SSL(aka FTPS)=21 or 990, SFTP=22).

    1. Bullet Custom for FTP
    2. Input the URL of the FTP address
    3. Indicate a port *See warning above
    4. Input username
    5. Input password
    6. Indicate file path
    7. Click Next

    NOTE* - A connection test will be performed at the end of the add import setup wizard - if the connection test fails, an error message will be displayed, and the import job will NOT be added.

    Step 5 - Setting a Schedule

    Indicate a Schedule for when and how often a file will be uploaded to the FTP.  TMAS Application will take into consideration your current time zone. Best to schedule the import job for 5 minutes after your file to uploads to the FTP server.

    1. Select schedule type (Daily, Hourly, 30 min or 15min)
    2. Set Hour to Start Import
    3. Click Next 
    Step 6 - Complete Columns Information
    The purpose of this steps is to indicate to T.M.A.S. which type of file is being imported and which column of data contains which type of value.
    Location Mask
    Depending on the Store ID selected for your locations the store # may be padded with Characters indicating your company.  If this is not present you would need to indicate a mask for the Store ID column so that the TMAS application can match it to a location.  If the Store ID is present in your file and exactly as indicated in the location ID of the location, leave this empty.
    Date Format

    Selection MUST match the date column of the imported file. Available options are indicated in the dropdown.

    Columns Information:
    Summary Information File: The most common file type can be used for both Sales and Transactions as well as Traffic from another software.
    • Check Off & Indicate Column # of the Location ID - the Store ID is present in your file and exactly as indicated in the location ID of the location
    • Check Off & Indicate Column # of the Date - Day you would like to import for
    • Check Off & Indicate Column # of the Hour - Hour you would like to import for
    • Check Off & Indicate Column # any other columns such as Sales, Transactions and items 
    Staff log file
    Use when importing staff from a different source which could not be included in a Summary Information File.
    • Check Off & Indicate Column # of the Location ID - the Store ID is present in your file and exactly as indicated in the location ID of the location
    • Check Off & Indicate Column # of the Date - Day you would like to import for
    • Check Off & Indicate Column # of the Hour - Hour you would like to import for
    • Check Off & Indicate Column # any other columns such as Staff id, Punch IN , Punch OUT
    Transactions log file
    When importing transaction summaries (aka invoices) if items are not supplied, UPT will appear as 0 otherwise, we import what is supplied. For transaction details (aka invoice lines), by default one line means one item. You can specify items and we will use what you supply. Should you use non whole units like weight, they will be rounded off per time laps.
    Transaction Summaries Transaction Details


    Then click Preview file and Match the example to what you will be uploading to the FTP

    Preview Example for Selections above: Click ADD when it matches and you are satisfied with the selections

    How to Edit or Delete an Import

    1. Expand Administration
    2. Select Manage Locations
    3. Click Import/Export data
    4. Check off the desired import
    5. Click the Pencil Icon to Edit or Trash Icon to Delete

    Running an Import on Demand

    Use the following option when you would like files to import quicker than the scheduled time (Daily or Hourly) has indicated

    Click the Cloud and arrow icon to run - a message will be displayed indicating it has ran

    F.A.Q. about data

    Q: Should the file be sent hourly or daily?
    A: Recommended to send the file hourly accumulative so that the T.M.A.S. dashboard features will be able to be made use of.  LEARN MORE ABOUT DASHBOARDS

    Q: How should the import file be named?

    A: Even though T.M.A.S. software will import any ASCII based file, the file name should include the complete date as well as the store ID. This is to facilitate our backup procedure and to be able to better locate a file. The file name does not have any impact on the import process. The recommended extension is .txt or .csv
    Example, a file from store ID 1010 containing data for April 1st 2014 could be named 1010-04012014.txt

    If you send the traffic hourly you could resend the same file, which would get overwritten, or you could add an underscore and the hour at the end of the name. For 13:00 this could be: 1010-04012014_13.txt

    Q: Should the file contain only data for the previous interval?

    A: Recommendation is as follows dependent on the upload frequency for import.

    • Hourly - File should be accumulative for the current day.
    • Daily - File should include all hours of the day.

    Q: Should the file be limited to business hours?

    A: The file should NOT be limited to business hours only as some sales take place just after or before opening. Also, entries for non-business hours are preferable as it will ensure accurate data for any special events that takes place. 

    Q: Which types of transactions should be included in the import data?

    A: Other type of transaction like gift cards, are irrelevant to SMS hosted software. It needs to be decided by the customer/POS provider when this transaction is applied. In any case a gift card should only applied once, either when it is used or when it is purchased, as it would corrupt the data to include it twice.

    Q: Can T.M.A.S. accept 2 files one for Sales and Transactions and the other for Staff?

    A: If Sales and Staff information comes from 2 different systems, it can be split in 2 files. Simply send a Sales & Transaction file without column #6, and a staff file without column #4 and #5 (column #6 becomes column #4)

    Q: How does T.M.A.S. read the data in the file? Can more than 1 location be included in the same file?

    A: T.M.A.S. imports data from a from each row based on the corresponding location ID which is existing in T.M.A.S. and the date/time indicated.  The import is passive so whatever data is appearing in the files is what is.  Always uses the last data received. If you have duplicate entry (same store, same date and time), the last data imported will be used, the other will be overwritten, not appended or added. There is no minimum or maximum delay between each file transfer and T.M.A.S. software (Less than 50 files per import scheduled). A file could be updated hourly with the latest completed hour, and sent to T.M.A.S. software. This will result in up to the last hour statistic. Because new values will delete old entries, in this case you can re-use the same file only with the last hour appended to it.

    header-top-left-border Created with Sketch.
    header-top-right-border Created with Sketch.