lookup activity output ,read custom activity and generate output .txt file

Question

Hello,

I have a lookup activity which reads mysql table, Its output is in this format.

"firstRow": {
         "FirstName": "John",
        "LastName": "Doe",
       "LocationName": "MA"
    },

I need a python script inside custom activity which would read the firstrow of lookup activity and generate an output .txt file.

Could you please advice. small correction: Move all rows in lookup activity not just first row.

Answer 1

Hi @N2120 , Thankyou for using Microsoft Q&A platform and thanks for posting your query here. As I understand your ask , you are trying to fetch the firstrow json out of the lookup activity and store it in the .txt file using python script via custom activity in ADF. Please let me know if that is not the ask here. To repro your case, I created a pool under the batch account . Also, stored the below python script in my storage account which is supposed to read the input argument and store the same in output.txt file in document form:

from sys import argv
print(argv[1])

output=argv[1]
outdic={}
outdic["firstrow"]=output


# Write output text to file
with open('output.txt', 'w') as f:
    f.write(str(outdic))

1. Use look up activity to read the sql table data.
2. Use Foreach activity to iterate through the lookup output, since you need only firstrow in the output file, you can use below expression in foreach , or you can directly use custom activity .

   @array(activity('Lookup1').output.value[0])

3.Use custom activity inside foreach, and use this expression in command :

   @concat('python firstrow.py ', item())

Below are the videos of implementation:

You can see the output.txt file in the batch account job under the root folder .

For more details on creation of batch account and pool, you can go through the following videos: Azure Data Factory - Execute Python script from ADF Azure Data Factory - Access Custom Activity Output from subsequent activities in ADF

Hope it helps. Kindly accept the answer as accepted answer helps community. Thankyou

Answer 2

Here's a Python script that reads the first row of the Lookup activity's output and generates an output file. Replace <lookup_output_file> with the path to your Lookup activity's output file, and <output_file> with the desired path for the output file. The script reads the Lookup output file as JSON, extracts the first row data, generates the desired output text, and writes it to the output file.

import json

# Read Lookup activity output file
with open('<lookup_output_file>.json') as f:
    lookup_output = json.load(f)

# Extract first row data
first_row = lookup_output['firstRow']

# Generate output text
output_text = f"Name: {first_row['FirstName']} {first_row['LastName']}\nLocation: {first_row['LocationName']}"

# Write output text to file
with open('<output_file>.txt', 'w') as f:
    f.write(output_text)