how to update flat file daily.

Hi,

I would like to know how I would update my model daily with my Flat Files that I have. i have created the actions and placed them on a Dashboard, but need to know how I would update theses files daily and run the actions?

Thank you

importing_data_95804.PNG

Accepted answers

JaredDolich

@neg177

Yes! Love questions like this.

Here's a way using Windows Scheduler to update the current date every day.

Action: Create a task in Windows Scheduler that runs at 5:00 AM every morning that runs the Python script. To accomplish this create a new task and set the properties the following way:

General – Make sure you run the task as an administrator.

Triggers – Set the trigger to run at 4:00 AM or whatever suits your business partner’s requirement.

Actions – For the actions, you’ll need to specify where the Python.exe is located and where your Python script is located. In the “add arguments” section, make sure you put your path and file name in double quotes, as shown below.

Unit Testing

Well, we cannot really finish our development without testing our process.

Whew! It worked.

All comments

AWhitworth

Hi,

The most basic option is that you can manually run this process every day and select your latest flat file in the dialogue box. However, you may want to look into the many Integration options Anaplan offers. The below link highlights the different options available to you, I would suggest looking into Anaplan connect.

https://help.anaplan.com/en/f3ec5fcc-0e46-4a59-bcce-c1319716b823-Data-Integration

JaredDolich

@neg177

Yes! Love questions like this.

Here's a way using Windows Scheduler to update the current date every day.

Action: Create a task in Windows Scheduler that runs at 5:00 AM every morning that runs the Python script. To accomplish this create a new task and set the properties the following way:

General – Make sure you run the task as an administrator.

Triggers – Set the trigger to run at 4:00 AM or whatever suits your business partner’s requirement.

Unit Testing

Well, we cannot really finish our development without testing our process.

Whew! It worked.

ashish.banka

@neg177 The best way to run a file daily is via automation. As @AWhitworth mentioned and @JaredDolich demoed, you can use the approach based on the tools and utilities available at your disposal.

If you are interested in using UNIX shell Script to update flat file via Anaplan Connect via Cron scheduler, let me know and I can provide the required sample script for the same

ChrisAHeathcote

Hi Jared,
This is a great piece of functionality which should be in all models.
Do you have and are wiling to share the Python script?
Chris

neg177

This was a great explanation. I'm going to set up and if I have any questions I will not hesitate to ask.

Thank you

JaredDolich

@ChrisAHeathcote

Yep. Happy to share the script. Also, pending is a best practice article that explains this step by step. @rob_marshall has asked that I add the AnaplanConnect version too. here's the python script and the model schema.

# This script creates a system date CSV file, uploads to Anaplan, then runs import action
# Written by Jared Dolich
# LinkedIn: https://www.linkedin.com/in/jareddolich/
# Anaplan: /profile/JaredDolich
# Retailitix March 2020
#
# 3 Steps
# 1. Create CSV file with system date using MM/DD/YYYY format (US format for Short Date)
# 2. Upload the CSV file to Anaplan
# 3. Run the Import action in Anaplan to update the module
# Import Python Libraries
import requests
import base64
import sys
import string
import os
import json
import time
import csv
# Import System Variables
from getAuthentication import userBA
from getGUIDs import wGuid, mGuid, fileID, fileName, dataSource, csvFileName
# Instantiate and Declare Variables
timeStr = time.strftime("%m/%d/%Y") #Uses MM/DD/YYYY Can also add H, M, and S
# File Data Variables - This is a 1 chunk import, so we don't need to use most of these
fileData = {
"id" : dataSource,
"name" : csvFileName,
"chunkCount" : 1,
"delimiter" : ",",
"encoding" : "",
"firstDataRow" : 2,
"format" : "",
"headerRow" : 1,
"separator" : ","
}
# Create the Upload URL and the Import URL
uploadURL = (f'https://api.anaplan.com/1/3/workspaces/{wGuid}/models/{mGuid}/' +
f'files/{fileData["id"]}')
importURL = (f'https://api.anaplan.com/1/3/workspaces/{wGuid}/models/{mGuid}/' +
f'imports/{fileID}/tasks')
# -----------------------------
# STEP 1 - CREATE THE CSV FILE
# -----------------------------
# Create the CSV File and add one row for that adds the current date
# Two Columns:
# Value - This is the single list item in the "Administration" List
# Current Date - This stores the current date value
with open(csvFileName, 'w', newline='') as f:
fieldNames = ['Dimension', 'Current Date']
theWriter = csv.DictWriter(f, fieldnames=fieldNames)
theWriter.writeheader()
theWriter.writerow({'Dimension' : 'Value', 'Current Date' : timeStr})
# ------------------------------------
# Authentication - Select a Method...
# ------------------------------------
# Use this authentication logic if you are using a CA Certificate
# cert = open('cert.pem').read()
# user = 'AnaplanCertificate ' + str(base64.b64encode((
# f'{username}:{cert}').encode('utf-8')).decode('utf-8'))
# Use this authenticcation logic if you are using Basic Authentication
putHeaders = {
'Authorization': userBA,
'Content-Type': 'application/octet-stream'
}
postHeaders = {
'Authorization': userBA,
'Content-Type': 'application/json'
}
# -------------------------------------
# STEP 2 - UPLOAD FILE TO ANAPLAN (PUT)
# -------------------------------------
# Opens the data file (filData['name'] by default) and encodes it to utf-8
dataFile = open(fileData['name'], 'r').read().encode('utf-8')
fileUpload = requests.put(uploadURL, headers=putHeaders, data=(dataFile))
# ---------------------------------
# STEP 3 - RUN IMPORT ACTION (POST)
# ---------------------------------
# Runs an import action
postImport = requests.post(importURL, headers=postHeaders, data=json.dumps({'localeName': 'en_US'}))

neg177

Quick question, do I need Anaplan Connect to set up this automation through Task Scheduler in Windows?

JaredDolich

@neg177

i would just add to @ChrisAHeathcote comment. While you can use any tool that can call a RESTful API, the easiest way if you're not sure, is to use Anaplan Connect. I prefer Python but you have to install that on your machine. It's free but it takes a little time.

For Anaplan Connect you'll need to write the import script and add these lines (thank you @rob_marshall )

Quick Links

Can you help?

Unanswered questions

New UX Filtering for Hierarchy sibling
I have A6 as the parent list and two sibling lists: A6.5 and A7. The A7 list includes an attribute that maps to A6.5. In the UX, the user will select A6.5 using a content selector, and the grid (dimensioned by A7) should display only the A7 items associated with that selected A6.5. Since the selector is a page context and…
A reminder of Bulk Copy - Current year to Prior year version for YoY analysis
A quick reminder of the Bulk Copy functionality. Bulk Copy allows you copy large volumes of data from one slice of a model to another in a single, optimised operation, instead of using formulas or imports. Use case: copy a version (RF1) into a prior year version (PY RF1) using a versions list to allow for year-on-year…
Looking for Anaplan End-Users to take a short survey!
We are looking for Anaplan end-users to provide feedback on their experiences with the Excel add-in. Interested individuals will respond to this 5-minute survey to help us understand personal needs and behavior when using the add-in. The feedback provided by survey takers is essential to the roadmap of Anaplan's products.…