How to Import Data from one drive into Anaplan Using Power Automate

Hi Community,

We are trying to import the CSV file from one drive to Anaplan using the power automate custom connector.

But we are having a challenge importing the data into Anaplan, it is not coming as expected in tabular format.

Any thoughts and solutions on this one?

@JaredDolich

Thanks & Regards

Badam Anjani Prasad

Answers

  • @Badam

    Great to see this feature being used. I'm assuming, of course, that you're referring to PowerBI. PowerBI uses the same API that all native integrations use, including the ones for Microsoft Office. So the first step is to make sure the import file is delimited. Most will use a comma but a tab delimiter will work too. Then you have to create the import in Anaplan and make sure you save it as "Everyone" and that it uses the delimiter in the test file. The last thing you have to keep in mind is that in order to use PowerBI your default tenant must be the one that contains the import. So if your email is jared@retailitix.com and your default tenant is retailitix then only imports from retailitix will work. Hope that helps!

  • Hi Jared, thanks for the spontaneous response.

    We have done this one, but the challenge is the data format import into the Anaplan Using File Upload API of power automate.

    Power Automate Flow

    Please suggest if we are not encoding the data properly or if we are not passing the right body into.

    @JaredDolich

    Thanks & Regards

    Badam Anjani

  • @Badam

    So, I like where you're going with this but first thing is to make sure your import action matches the delimter being used in the file. Also make sure your text encoding matches. I see you saved it as CSV. Is your file comma delimited? Make sure your import action is also comma delimited.

  • Hi @JaredDolich

    Yes import action was made using the mentioned delimiters and encoded with utf-8 only. But still facing the same challenge.

    Any thoughts are appreciated.

  • So if data is showing up then you're definitely in good shape with the Anaplan REST API. Try importing the data manually to make sure it comes in correctly.

    https://www.vahiddm.com/post/call-power-bi-rest-api-with-power-automate#:~:text=By%20using%20the%20HTTP%20action,POST%20%3D%20write

  • Hi @JaredDolich,

    Thanks for the suggestion, I think power automate custom connect has constraints in sending the body format. It accepts only JSON, but we need binary data format for import. So we used the general HTTP request.

    Thanks & Regards

    Badam Anjani Prasad

  • Sure @Badam - that's why a lot of people just use the Anaplan native connector for PowerBI. you can only sent delimited files to Anaplan. I've seen demonstrations of people running their JSON files through Python to generate the delimited files so that may be an option too for you. I suppose any programming language supported within PowerBI would work as long as it produces a delimited file. From there, it's academic getting it imported into Anaplan.

  • Hello, Check CSV file format: Verify that the CSV file you are importing is properly formatted. Ensure that the data is delimited correctly using commas or another specified delimiter, and that each row represents a separate record with consistent column headers. Data mapping and transformation: Use Power Automate's custom connector to map and transform the data from the CSV file before importing it into Anaplan. Ensure that the column headers and data types match the expected format in Anaplan. You may need to perform additional data manipulation or formatting using Power Automate actions or expressions to align the data correctly.

    https://www.myadvocateaurora.net/

  • Hi @Badam, you can confirm if the API request format is correct in our Apiary guide. There are many different ways you can upload a file via HTTP request. For example, if you are looking to upload a file in a single chunk, you can use a file or a stream option. In both cases you will be making a PUT request to a slightly different endpoints. In file option, your header Content-Type needs to be set to application/octet-stream (I think you have already done this). Body needs to be set to binary and you are sending the file you wish to upload in that body. If you choose a stream option, the Content-Type header stays the same, while body needs to be set as a raw text, and file content is passed in it. All of these details are available in our Apiary guide. I hope this helps!