How to when a new email arrives attachment with csv file, save in SQL database power automate microsoft

Question

How to use Microsoft Power Automate to automate the process of detecting when a new email arrives with an attachment containing a CSV file, and then seamlessly save that CSV file into your SQL database for efficient data management.

Answer

To automate the process of detecting when a new email arrives with an attachment containing a CSV file and then saving that CSV file into your SQL database using Microsoft Power Automate, follow these steps:

Step 1: Create a Flow in Power Automate

Sign in to Power Automate: Go to Power Automate and sign in with your Microsoft account.

Create a New Flow:

Click on "Create" in the left-hand menu.

Choose "Automated cloud flow".

Set Trigger:

- Name your flow, e.g., "Save Email CSV to SQL".

   - Choose the trigger "When a new email arrives (V3)".

      - Click "Create".

Step 2: Configure the Trigger

Specify the Folder:

In the "Folder" field, specify the folder you want to monitor, e.g., "Inbox".

Add Conditions (Optional):

  - You can add conditions to filter emails, e.g., only emails with attachments or from a specific sender.

Step 3: Add Steps to Process the Email

Condition to Check Attachments:
- Click on "New step".
  - Choose "Condition".
    - Set the condition to check if the email has attachments:
```
- "Has Attachments" is equal to "Yes".
```

Step 4: Save the Attachment

Add Apply to Each Step:

Inside the "If yes" branch, click "Add an action".

Search for "Apply to each".

Select "Attachments" from the "When a new email arrives" dynamic content.

Get Attachment Content:

- Inside the "Apply to each", click "Add an action".

   - Search for "Get attachment content (V2)".

      - Set:

            - "Message Id": `Message Id` (dynamic content).

                  - "Attachment Id": `Attachment Id` (dynamic content).

Step 5: Parse the CSV and Save to SQL

Compose Step to Parse CSV:

Click on "Add an action" within the "Apply to each".

Choose "Compose".

In the "Input" field, add the "Attachment Content" dynamic content.

Initialize Variables to Hold CSV Data:

- Click on "Add an action".

   - Choose "Initialize variable".

      - Create variables to store CSV headers and rows.

      **Parse CSV Data**:

         - Add logic to parse the CSV content into structured data. This might involve splitting the content by line breaks and commas.

            - This step can be complex depending on the CSV structure and may require using expressions in Power Automate.

            **Insert Data into SQL Database**:

               - Click on "Add an action".

                  - Search for "SQL Server" and choose "Insert row".

                     - Configure the connection to your SQL database.

                        - Map the parsed CSV data to the corresponding columns in your SQL table.

Example of Parsing CSV (Simplified)

plaintextSao chép mã
// Initialize variables
initialize variable: varCsvRows
initialize variable: varCsvHeaders

// Parse CSV
compose: split(outputs('Get_attachment_content')?['body'], '
')
set variable: varCsvHeaders = first(outputs('Compose'))
apply to each: skip(outputs('Compose'), 1)
    set variable: varCsvRows = current item
    // Map CSV fields to SQL columns and insert rows

Step 6: Test Your Flow

Save and Test:
- Save the flow.
  - Send a test email with a CSV attachment to see if the flow triggers and processes the CSV correctly.

Step 7: Monitor and Troubleshoot

Check Run History:
- Monitor the flow run history in Power Automate to ensure it runs as expected.
  - Review any errors and adjust the flow accordingly.

By following these steps, you should be able to automate the process of detecting new emails with CSV attachments and saving the data into your SQL database using Microsoft Power Automate. Adjustments might be needed based on your specific CSV structure and SQL schema.To automate the process of detecting when a new email arrives with an attachment containing a CSV file and then saving that CSV file into your SQL database using Microsoft Power Automate, follow these steps: