CSV format not retained in Lookup - Azure Data factory

Anitha Eswaran 41 Reputation points MVP
2021-02-12T09:10:06.417+00:00

Hi experts,

I am trying to call D365FO Recurrence job in my pipeline.

I am facing the issue in the format of the data sent from Lookup activity.
67250-image.png

My lookup activity has the source data set as CSV file , in my case a customer group file. (Seperated by commas , first row as header).

The output value of the lookup activity is

67411-image.png

The web activity inside the for each loop in which I am calling the recurrence job configured in D365FO for import. The data is expected in the format of CSV file like

67307-image.png

Though the job in D365FO receives the data, it ends up in error with this format sent in the body
67297-image.png

Is there any alternate approach to send the data in the exacr csv format?

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,899 questions
0 comments No comments
{count} votes

Accepted answer
  1. Nasreen Akter 10,766 Reputation points
    2021-02-12T18:56:06.73+00:00

    Hi @Anonymous ,

    There is a workaround you can do:

    1. Create a Dataset with Column Delimiter --> Tab(\t) and unselect First row as header
    2. In the lookupActivity, Skip line count --> 1
    3. In the Foreach, Setting --> Items --> @activity('Lookup1').output.value
    4. In the Web Activity, Body --> @item().Prop_0

    Please see the screenshots for details. Hope this helps! Thanks!

    67490-img1.jpg
    67456-img-2.jpg
    67457-img-3.jpg
    67400-img-4.jpg
    67521-img-5.jpg

    ----------

    If the above response is helpful, please accept as answer and upvote it. Thanks!


2 additional answers

Sort by: Most helpful
  1. Anitha Eswaran 41 Reputation points MVP
    2021-02-13T16:24:42.373+00:00

    Thanks much Nasreen.

    I did the same earlier, but i tried as '@activity('Lookup1').output.value.Prop_1'.
    And this was not giving me the result.

    Your tips helped me to address this issue.

    Thanks again.


  2. Anitha Eswaran 41 Reputation points MVP
    2021-02-23T04:49:25.573+00:00

    Hi @Nasreen Akter ,

    I am facing the same issue again.

    I notice the format is lost in CSV when the data is copied from SQL.

    I am trying to copy the data from Azure SQL to CSV and the above format is not retained during copy.

    The preview data shows

    70913-image.png

    Is there any way the format can be retained when copied from sql? Or do i need to tune my approach?

    0 comments No comments