Offline Sales Import- CSV File Format

    This article explains the format in which the CSV file should be structured when uploading offline sales
    via the Offline Sales Uploader on JLP. For more detailed instructions on how to upload offline sales,
    please refer to the article 'How to Import Offline Sales'. 

    CSV Fields

    The fields required in the CSV will be different depending on the configuration.

    Please refer to the attached example CSV file for more details.

    Note that not all fields are used by the bulk-sales service but are stored for history regardless.

     

    Field

    Header required

    Value required

    Format

    Integer/

    String

    Additional 

    validation

    Comments

    ORDER_ID

    I

      Order ID in the client's system. Retrievable from the DB upon request – not shown in UI anywhere
    LOTTERY S Must match an active lottery Lottery key for example my_lottery
    DRAW_NO I Must match an open draw If the keyword 'next' is specified, the next open draw will be used
    CUSTOMER_ID     S text (max = 100) If provided we will try to find customer using this ID instead of email
    TITLE   S "Ms", "Mrs", "Miss", "Mr" or "Dr  
    FIRST_NAME S text, no digits (min = 2, max = 44)  
    LAST_NAME S text, no digits (min = 2, max = 36)  
    ADD_UNIT   S text a maximum of 97 characters for the concatenation of these three fields.  
    ADD_STREET_NUMBER When email not given or when payment type is BD or the sites use standard address format S  
    ADD_STREET_NAME When email not given or when payment type is BD or the sites use standard address format S  
    ADD_LINE_1 When email not given or when payment type is BD or the sites use international address format S   Customer's address in 1 line which replaces ADD_UNIT, ADD_STREET_NUMBER and AND_STREET_NAME
    ADD_LINE_2

    When this

    field is set to optional in international address format

    When this field is set to optional in international address format S   Customer's apartment number, building name, etc (optional)
    ADD_SUBURB When email not given or when payment type is BD or the sites use standard address format S text (max = 44)  
    ADD_STATE

    When email not given or when payment type is BD or the sites use standard address format

     

    This field can also be optional if it is set to be optional in international format

    S text (max = 44)  
    ADD_POSTCODE When email not given or when payment type is BD or the sites use standard address format S text (max = 10)

    AU should be integer > 0

    Other countries - any text value

    COUNTRY When email not given or when payment type is BD or the sites use standard address format S 2 character ISO code eg AU, GB
    EMAIL   S ^[^@]+@[^@]+\.[^@]+$ Mandatory if no address data is provided
    PHONE   S text (max = 20) We will only collect a single number from this flow.
    DOB   dd/mm/yyyy text (exactly 10) dependant on the configuration there may be restrictions on this field eg older than a restricted gambling age
    IS_SUBSCRIPTION

    When 

    SUBSCRIPTION_ENABLED

     true

    S "YES" or "NO" Mater VIP sale
    IS_AUTOPLAY

    When AUTOPLAY_ENABLED 

    true

    S "YES" or "NO" Autoplay this sale
    TICKET_VALUE S "10" or "10.00" or "$10" Dollar amount of the price of Ticket Book
    SALE_DATE dd/mm/yyyy text (exactly 10) This is separate to Payment Date. We need payment date recorded against the transaction. Sale date exists for Suppliers to record when a sale occurred.
    PAYMENT_TYPE S CC or CASH or MO or CHQ or BD Case insensitive.  Note these payment methods will need to be available
    CC_TOKEN

    When CC_TOKEN_ONLY

     true

    When payment type is CC S text (exactly 10) The CC token must have been created with the same payment gateway and credentials as configured
    CC_PAYMENT_GATEWAY     S   Introduced in http://gerrit.lan/c/bulk-sales/+/110023 When provided this will override the base configuration for the payment gateway. Usually has values of "stripe", "paystream", "eway".
    AFFILIATION   S Must match either an existing affiliate code or name Either the code or name of an existing affiliate.
    CAMPAIGN     S   Stored against the cart row in JL
    BANK_ACCOUNT_NAME

    When

    PAYMENT_METHOD includes BD

    When payment type is BD S    
    BANK_ACCOUNT_NUMBER

    When

    PAYMENT_METHOD includes BD

    When payment type is BD S    
    BANK_CODE

    When

    PAYMENT_METHOD includes BD

    When payment type is BD S    
    GAME_OFFER   When GAME_OFFER header is used S One of the following:
    "standard_monthly",
    "standard_quarterly",
    "standard_biannually",
    "standard_annually"
    The type of game offer. This will always be "standard_monthly" unless the direct debit is not monthly.
    BILLING_DAY When GAME_OFFER is used When GAME_OFFER is used I one of the following:
    1, 8, 15, 23, 28
    Used to determine the direct debit collection day. Must be one of the available values (1,8,15,23,28).
    CAUSE

    When CAUSE_ENABLED

     true

    S Must match an existing cause key The key of a cause set up in the system i.e my_cause.
    ORGANISATION     S Must match the name of an existing organisation.  The name of a sales organisation i.e "ACME Telemarketers".
    CHANNEL   S Must match an existing channel key If this field is missing the channel assigned to the uploader is used for all records.  If present, this column overrides the channel.  If the channel field is supplied (optional) all records must supply a valid channel key.
    COMMS_EMAIL     S "YES" or "NO" opt in to receive email marketing preference
    COMMS_SMS     S "YES" or "NO" opt in to receive SMS marketing preference
    COMMS_PUSH     S "YES" or "NO" opt in to receive Push notification preference
    COMMS_PHONE     S "YES" or "NO" opt in to receive phone marketing preference
    COMMS_MAIL     S "YES" or "NO" opt in to receive direct mail preference
    EXTRA_*     S

    Extra field column header names must contain non-special characters however, dashes and underscores are permitted.

    Extra column headers can only contain characters a-z, A-Z, 0-9 _ - . (full-stop).  No spaces are allowed.

    Extra columns are a way to capture any additional sales data not otherwise explicitly defined in this file format.   Extra fields are denoted by having a prefix "extra_" followed by the name of the field.  For example, the column heading "extra_tshirt_size" would create a data field called "tshirt_size".  There can be any quantity of extra_ fields supplied.  All supplied records for extra data are stored as text fields.

    There are no specific text limits on the length of the header of the data but we suggest keeping under 255 characters.

    Deprecated Fields

    The following CSV fields are currently deprecated and will be removed from the file format in the next major release.

    Field Header required Value required

    Format

    Integer (I)/

    String(S)

    Additional 

    validation

    Comments
    CC_TYPE When CC_TOKEN_ONLY false When payment type is CC S "VISA" or "MASTERCARD" or "AMEX"  
    CC_NUMBER When CC_TOKEN_ONLY false When payment type is CC S Strip spaces, Luhn check  
    CC_NAME When CC_TOKEN_ONLY false When payment type is CC S    
    CC_EXPIRY When CC_TOKEN_ONLY false When payment type is CC S   Supports lots of different formats but can't really go wrong with "01/25"
    CC_CVV     S 3 or 4 digits, can have a leading zero Not included in the CSV by default. Can be included if a CVV is somehow collected. Gateways can encode without a CVV 
    Was this article helpful?
    0 out of 0 found this helpful