Import SQL data in Anaplan

rahulnair

Hello Team,

I am totally new to Anaplan, requirement is we have to dump SQL table data to Anaplan. In this case, I need to do Import action 

 

set AnaplanUser="myemailId"
set WorkspaceId="myworkspaceid"
set ModelId="mymodelid"
set ServiceUrl="https://api.anaplan.com"
set AuthUrl="https://auth.anaplan.com"
set FileName="Test.csv"
set FilePath="C:\Workspace\Anaplan\anaplan-connect\import\Test.csv"
set ImportName="NewImportTest"
set Chunksize=1
set JDBC_Prop="C:\Workspace\Anaplan\anaplan-connect\examples\example-jdbc-query.properties"

 

set Operation=-debug -service %ServiceUrl% -auth %AuthUrl% -workspace %WorkspaceId% -model %ModelId% -jdbcproperties %JDBC_Prop% -import %ImportName% -execute -output "C:\Workspace\Anaplan\anaplan-connect\errors\My Errors.txt"

 

My code is ready but How do I create import action "NewImportTest" in Anaplan. I see CreateItem (below screenshot) blank. Not able to create one. 

 

 

ben_speight

You're probably re-running the import with the original source file.

To upload the new file before running the import, insert '-file %FileName% -put %FilePath%' before the -import in the Operation variable.
To perform a SQL query, insert '-file %FileName% -jdbcproperties %JDBC_Prop%' instead.

NikolayStarostin

Hello, I assume that you are trying importing to a list. You can do this by opening the list and clicking the import button. Then you choose the file and map it the way you need. After that, you will be able to find import action in the actions list.

 

alexpavel

@rahulnair You cannot launch an import action that imports SQL data directly from Anaplan. This needs to be triggered outside of Anaplan.

 

Below a very simplified version of how Anaplan integration works:

 

1. Import actions with data outside of Anaplan work exclusively with TEXT files.

So, you need first to create from your SQL data a TXT file example with exactly the same structure columns and rows of your SQL query. 

Creating import actions from TXT file is part of the Level 1 training course. 

 

2. your script seems to be Anaplan connect script which needs to be triggered from outside of Anaplan using batch files. Using Anaplan Connect, indeed you can directly connect to an SQL database that retrieves data launching the SQL query and launching also the action created in Step 1. 

What Anaplan Connect does in back-end is to launch the SQL query and creates a recordset similar with the TEXT file from the one from Step 1, uploads to Anaplan this recordset and launches the import action from Step 1. 

 

Here more about Anaplan Connect:

https://community.anaplan.com/t5/On-Demand-Courses/Anaplan-Connect-Guidelines/ta-p/81650

 

Hope it helps

Alex

rahulnair

Thanks @alexpavel  for the reply,

 

I went through training video, but action was already created with respect to List. In my case, I created a new list "ITTestList" but not sure how to import new data into list using .txt file or SQL.

@alexpavel : your script seems to be Anaplan connect script which needs to be triggered from outside of Anaplan using batch files.  -  I created a batch file in anaplan-connect folder (code mention in first post) will that be fine? So when I run that batch file it should get data from SQL, create Txt file and txt file should import in my list. Here again, no sure what name should I specify for import action.   

 

Sorry, i might be asking weird questions, its just 3 days in Anaplan 🙂

 

  

alexpavel

@rahulnair  I strongly advise following the Level 1 Training from the learning center.

It will guide you through all these basic steps.

 

In short, this is what @NikolayStarostin  posted: go in the "General Lists" section, select your list and push the button "Import" and it will ask what is the source and you can choose the File. 

 

 

 

rahulnair

Thanks @alexpavel  & @NikolayStarostin  for reply. I could create action.

 

I created test list with test data (Below screenshot for your reference).

Now I want to insert data to this list using attached file. 

set AnaplanUser="myuserid"
set WorkspaceId="myworkspaceid"
set ModelId="mymodelid"
set ServiceUrl="https://api.anaplan.com"
set AuthUrl="https://auth.anaplan.com"
set FileName="Testvalues.txt"
set FilePath="C:\Workspace\Anaplan\anaplan-connect\import\Testvalues.txt"
set ImportName="TestlistImport"
set DumpName="Client Location for Dumping the Errored data from Import"
set Chunksize=1
set JDBC_Prop="C:\Workspace\Anaplan\anaplan-connect\examples\example-jdbc-query.properties"

 

set Operation=-debug -service %ServiceUrl% -auth %AuthUrl% -workspace %WorkspaceId% -model %ModelId% -chunksize %Chunksize% -import %ImportName% -execute -output "C:\Workspace\Anaplan\anaplan-connect\errors\My Errors.txt"

I could see rows inserted successfully but List in Anaplan is not getting updated.