How to grab column value from ADLS gen 2 csv file and use the column value in the body of the email,also send blob data as attachment to outlook mail

huangapple go评论58阅读模式
英文:

How to grab column value from ADLS gen 2 csv file and use the column value in the body of the email,also send blob data as attachment to outlook mail

问题

以下是翻译好的部分:

"这是我的情景,
每天都会将一个 CSV 文件放入 Blob 存储中,然后由我的 ADF 数据流进行处理,并生成一个输出文件夹中的 CSV 文件。

现在,使用逻辑应用,我需要将该 CSV 文件(小于 10 MB)作为附件通过 Outlook 连接器发送给客户。
此外,我的电子邮件正文必须包含来自该 Blob CSV 的动态值。
例如,'AppWorks' 是列 'Works/not' 中的列值。有时它可能是 'AppNotWorks'。那么如何在 Azure 逻辑应用中处理这种情况呢?"

英文:

Here is my Scenario,
There will be a drop of csv file into blob storage every day ,that will be processed by my dataflow in ADF and generate a csv in output folder.

Now Using logic apps, I need to send that csv file (less than 10 mb ) as an attachement to customer via Outlook connector.
Besides ,My body of the email must have dynamic value coming from that blob csv .
For example 'AppWorks' is the column value in column 'Works/not'. Sometimes it may be "AppNotWorks".So How to handle this scenario in Azure logic apps

答案1

得分: 0

你可以使用数据工厂和逻辑应用的组合来实现这一点。使用“查找”活动获取文件的第一行(由于整个列的值将相同,我们可以从一行中获取所需的值)。

  • 现在使用网络活动触发逻辑应用。将逻辑应用的HTTP请求URL传递给网络活动。在请求正文中,传递以下动态内容:
@activity('Lookup1').output.firstRow
  • 当你调试管道时,逻辑应用将成功触发。我已经提供了请求正文JSON模式以获取单独的值。对于我所采用的示例,它将如下所示:
{
    "properties": {
        "customer": {
            "type": "string"
        },
        "id": {
            "type": "string"
        }
    },
    "type": "object"
}
  • 创建一个连接到存储账户以关联所需文件。

  • 现在,使用Outlook连接器发送电子邮件。

  • 以下是整个逻辑应用的JSON:

{
    "definition": {
        "$schema": "https://schema.management.azure.com/providers/Microsoft.Logic/schemas/2016-06-01/workflowdefinition.json#",
        "actions": {
            "Get_blob_content_(V2)": {
                "inputs": {
                    "host": {
                        "connection": {
                            "name": "@parameters('$connections')['azureblob']['connectionId']"
                        }
                    },
                    "method": "get",
                    "path": "/v2/datasets/@{encodeURIComponent(encodeURIComponent('AccountNameFromSettings'))}/files/@{encodeURIComponent(encodeURIComponent('JTJmZGF0YSUyZnNhbXBsZTEuY3N2')}/content",
                    "queries": {
                        "inferContentType": true
                    }
                },
                "metadata": {
                    "JTJmZGF0YSUyZnNhbXBsZTEuY3N2": "/data/sample1.csv"
                },
                "runAfter": {},
                "type": "ApiConnection"
            },
            "Send_an_email_(V2)": {
                "inputs": {
                    "body": {
                        "Attachments": [
                            {
                                "ContentBytes": "@{base64(body('Get_blob_content_(V2)'))}",
                                "Name": "sample1.csv"
                            }
                        ],
                        "Body": "<p>Hi @{triggerBody()?['customer']},<br><br>Random description</p>",
                        "Importance": "Normal",
                        "Subject": "sample data",
                        "To": "<to_email>"
                    },
                    "host": {
                        "connection": {
                            "name": "@parameters('$connections')['office365']['connectionId']"
                        }
                    },
                    "method": "post",
                    "path": "/v2/Mail"
                },
                "runAfter": {
                    "Get_blob_content_(V2)": [
                        "Succeeded"
                    ]
                },
                "type": "ApiConnection"
            }
        },
        "contentVersion": "1.0.0.0",
        "outputs": {},
        "parameters": {
            "$connections": {
                "defaultValue": {},
                "type": "Object"
            }
        },
        "triggers": {
            "manual": {
                "inputs": {
                    "schema": {
                        "properties": {
                            "customer": {
                                "type": "string"
                            },
                            "id": {
                                "type": "string"
                            }
                        },
                        "type": "object"
                    }
                },
                "kind": "Http",
                "type": "Request"
            }
        }
    },
    "parameters": {
        "$connections": {
            "value": {
                "azureblob": {
                    "connectionId": "/subscriptions/xxx/resourceGroups/xxx/providers/Microsoft.Web/connections/azureblob",
                    "connectionName": "azureblob",
                    "id": "/subscriptions/xxx/providers/Microsoft.Web/locations/westus2/managedApis/azureblob"
                },
                "office365": {
                    "connectionId": "/subscriptions/xxx/resourceGroups/v-sarikontha-Mindtree/providers/Microsoft.Web/connections/office365",
                    "connectionName": "office365",
                    "id": "/subscriptions/xxx/providers/Microsoft.Web/locations/westus2/managedApis/office365"
                }
            }
        }
    }
}
  • 以下是用于参考的邮件的图像:

[How to grab column value from ADLS gen 2 csv file and use the column value in the body of the email,also send blob data as attachment to outlook mail]

英文:

You can use the combination of both data factory and logic apps to do this. Use look up activity to get the first row of the file (Since the entire column value will be same, we can get the required value from one row).

How to grab column value from ADLS gen 2 csv file and use the column value in the body of the email,also send blob data as attachment to outlook mail

  • Now use web activity to trigger the logic app. Pass the logic app's HTTP request URL to web activity. In the body, pass the following dynamic content:
@activity(&#39;Lookup1&#39;).output.firstRow

How to grab column value from ADLS gen 2 csv file and use the column value in the body of the email,also send blob data as attachment to outlook mail

  • When you debug the pipeline, the logic app will be successfully triggered. I have given the Request Body JSON schema to get values individually. For the sample I have taken, it would look as shown below:
{
    &quot;properties&quot;: {
        &quot;customer&quot;: {
            &quot;type&quot;: &quot;string&quot;
        },
        &quot;id&quot;: {
            &quot;type&quot;: &quot;string&quot;
        }
    },
    &quot;type&quot;: &quot;object&quot;
}
  • Create a connection to storage account to link the required file.

How to grab column value from ADLS gen 2 csv file and use the column value in the body of the email,also send blob data as attachment to outlook mail

  • Now, using the Outlook connector, send the Email.

How to grab column value from ADLS gen 2 csv file and use the column value in the body of the email,also send blob data as attachment to outlook mail

  • The following is the entire Logic app JSON:
{
    &quot;definition&quot;: {
        &quot;$schema&quot;: &quot;https://schema.management.azure.com/providers/Microsoft.Logic/schemas/2016-06-01/workflowdefinition.json#&quot;,
        &quot;actions&quot;: {
            &quot;Get_blob_content_(V2)&quot;: {
                &quot;inputs&quot;: {
                    &quot;host&quot;: {
                        &quot;connection&quot;: {
                            &quot;name&quot;: &quot;@parameters(&#39;$connections&#39;)[&#39;azureblob&#39;][&#39;connectionId&#39;]&quot;
                        }
                    },
                    &quot;method&quot;: &quot;get&quot;,
                    &quot;path&quot;: &quot;/v2/datasets/@{encodeURIComponent(encodeURIComponent(&#39;AccountNameFromSettings&#39;))}/files/@{encodeURIComponent(encodeURIComponent(&#39;JTJmZGF0YSUyZnNhbXBsZTEuY3N2&#39;))}/content&quot;,
                    &quot;queries&quot;: {
                        &quot;inferContentType&quot;: true
                    }
                },
                &quot;metadata&quot;: {
                    &quot;JTJmZGF0YSUyZnNhbXBsZTEuY3N2&quot;: &quot;/data/sample1.csv&quot;
                },
                &quot;runAfter&quot;: {},
                &quot;type&quot;: &quot;ApiConnection&quot;
            },
            &quot;Send_an_email_(V2)&quot;: {
                &quot;inputs&quot;: {
                    &quot;body&quot;: {
                        &quot;Attachments&quot;: [
                            {
                                &quot;ContentBytes&quot;: &quot;@{base64(body(&#39;Get_blob_content_(V2)&#39;))}&quot;,
                                &quot;Name&quot;: &quot;sample1.csv&quot;
                            }
                        ],
                        &quot;Body&quot;: &quot;&lt;p&gt;Hi @{triggerBody()?[&#39;customer&#39;]},&lt;br&gt;\n&lt;br&gt;\nRandom description&lt;/p&gt;&quot;,
                        &quot;Importance&quot;: &quot;Normal&quot;,
                        &quot;Subject&quot;: &quot;sample data&quot;,
                        &quot;To&quot;: &quot;&lt;to_email&gt;&quot;
                    },
                    &quot;host&quot;: {
                        &quot;connection&quot;: {
                            &quot;name&quot;: &quot;@parameters(&#39;$connections&#39;)[&#39;office365&#39;][&#39;connectionId&#39;]&quot;
                        }
                    },
                    &quot;method&quot;: &quot;post&quot;,
                    &quot;path&quot;: &quot;/v2/Mail&quot;
                },
                &quot;runAfter&quot;: {
                    &quot;Get_blob_content_(V2)&quot;: [
                        &quot;Succeeded&quot;
                    ]
                },
                &quot;type&quot;: &quot;ApiConnection&quot;
            }
        },
        &quot;contentVersion&quot;: &quot;1.0.0.0&quot;,
        &quot;outputs&quot;: {},
        &quot;parameters&quot;: {
            &quot;$connections&quot;: {
                &quot;defaultValue&quot;: {},
                &quot;type&quot;: &quot;Object&quot;
            }
        },
        &quot;triggers&quot;: {
            &quot;manual&quot;: {
                &quot;inputs&quot;: {
                    &quot;schema&quot;: {
                        &quot;properties&quot;: {
                            &quot;customer&quot;: {
                                &quot;type&quot;: &quot;string&quot;
                            },
                            &quot;id&quot;: {
                                &quot;type&quot;: &quot;string&quot;
                            }
                        },
                        &quot;type&quot;: &quot;object&quot;
                    }
                },
                &quot;kind&quot;: &quot;Http&quot;,
                &quot;type&quot;: &quot;Request&quot;
            }
        }
    },
    &quot;parameters&quot;: {
        &quot;$connections&quot;: {
            &quot;value&quot;: {
                &quot;azureblob&quot;: {
                    &quot;connectionId&quot;: &quot;/subscriptions/xxx/resourceGroups/xxx/providers/Microsoft.Web/connections/azureblob&quot;,
                    &quot;connectionName&quot;: &quot;azureblob&quot;,
                    &quot;id&quot;: &quot;/subscriptions/xxx/providers/Microsoft.Web/locations/westus2/managedApis/azureblob&quot;
                },
                &quot;office365&quot;: {
                    &quot;connectionId&quot;: &quot;/subscriptions/xxx/resourceGroups/v-sarikontha-Mindtree/providers/Microsoft.Web/connections/office365&quot;,
                    &quot;connectionName&quot;: &quot;office365&quot;,
                    &quot;id&quot;: &quot;/subscriptions/xxx/providers/Microsoft.Web/locations/westus2/managedApis/office365&quot;
                }
            }
        }
    }
}
  • The following is the resulting Mail image for reference:

How to grab column value from ADLS gen 2 csv file and use the column value in the body of the email,also send blob data as attachment to outlook mail

huangapple
  • 本文由 发表于 2023年2月16日 18:31:53
  • 转载请务必保留本文链接:https://go.coder-hub.com/75470967.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定