# Email data into Excel automatically: three methods tested > The label-split recipe most tutorials teach got 8 of our 20 test emails right when we emulated it. A script and an AI prompt read all 100 fields. - Published: 2026-10-20 - Author: 28creation Desk - Format: Guide - URL: https://28creation.com/articles/email-data-into-excel/ ## The short read **In our emulation, the tutorial email-to-Excel recipe failed on 8 of 20 test emails and botched 4 more.** - It splits the text on labels like Name:, so a table layout, a renamed label or a missing field breaks it. - An 85-line Python script and an AI prompt each read all 100 fields, with their own limits. ### What you can do with this > Build one flow per sender format with the guarded expression below, add a Missing column, and check it weekly. Rows that break then show up instead of disappearing. --- If web forms, order systems or suppliers send you emails with the same fields every time, you can stop copying those fields into Excel by hand. A Power Automate flow, a short script or an AI prompt can fill the table for you. The catch is the method nearly every tutorial teaches: cut the text after a label such as `Name:`. It works until one sender changes its layout. We built 20 sample emails in six layouts that real inboxes get and ran them through three methods. The label-split recipe, as tutorials write it, got 8 of the 20 rows fully right. A script we wrote got all 20, and so did an AI prompt. Below are the steps for each, the code, and the limits Microsoft documents. All documentation was read on October 20, 2026. ## Why the label-split recipe breaks The no-code recipe has three moves. A flow starts when an email arrives, turns the email's HTML into plain text, then cuts out each field with text functions. In Power Automate that means the Office 365 Outlook trigger and the **Html to text** action from the [Content Conversion connector](https://learn.microsoft.com/en-us/connectors/conversionservice/). Then the [`split()` function](https://learn.microsoft.com/en-us/azure/logic-apps/expression-functions-reference) takes everything after `Phone:`, and everything before the next line break. That logic assumes every email has the label, spelled the same way, with the value on the same line. Microsoft's documentation shows three ways the assumption fails before any sender changes anything. - **Tables lose their colons.** Html to text puts [3 spaces between data table columns](https://learn.microsoft.com/en-us/connectors/conversionservice/), so a form that sends a two-column table arrives as `Phone +1 213 555 0160`, with no `Phone:` to find. - **Links grow a tail.** A link becomes `text[link]` unless the text and the address are the same. By that documented rule, an email address sent as a mailto link comes out as `dpark@parkdental.example[mailto:dpark@parkdental.example]`. - **Long lines are cut.** The converter's "max line length is 80 characters, afterwards a line break will follow". A message longer than that loses everything after the 80th character, because the recipe stops at the first line break. A missing label is worse. When the delimiter is not in the text, `split()` [returns an array with a single element](https://learn.microsoft.com/en-us/azure/logic-apps/expression-functions-reference), so the tutorial expression asks for a second item that does not exist. In our emulation that counts as a failed run, and the row never reaches Excel. ## Our test: 20 emails, six layouts, three methods We wrote 20 web-form notification emails as standard .eml files with Python's email library, one lead per email, five fields each (Name, Email, Phone, Company, Message). That makes 100 values to get right. The names and companies are invented, on `.example` addresses. The layouts: | Emails | Format | What it tests | |---|---|---| | 1 to 8 | Plain text, `Name: value` on each line | The happy path; email 5 has a three-line message, email 7 has no phone line | | 9 to 12 | HTML table, label in one cell, value in the next | No colons after conversion; email address as a mailto link | | 13 to 15 | Plain text with `Company Name:` above `Name:`, and `E-mail:` | A label inside another label; a spelling variant | | 16 and 17 | HTML, label in one paragraph, value in the next | Value on the line below the label | | 18 and 19 | Forwarded by a colleague | Extra header lines above the form | | 20 | Replied to, with the colleague's signature on top | A second `Phone:` and `Email:` before the real ones | We could not run Power Automate in our sandbox, which has no Microsoft 365 tenant. So we emulated it. Our Python code converts HTML by the rules Microsoft documents for Html to text, then applies the same split logic with the labels from the first layout. Plain-text emails went to the split step directly. We tested it two ways. The tutorial form fails when a label is missing. Our guarded form, shown in step 2, returns an empty cell instead. The script ran for real, and we gave the AI prompt to a Claude model from Anthropic in our sandbox. The results: | Method | Fields right (of 100) | Rows fully right (of 20) | What went wrong | |---|---|---|---| | Label split, tutorial form | 47 | 8 | 8 runs fail, so those rows never arrive; 4 rows written wrong or blank | | Label split, guarded | 63 | 10 | No failures, but 6 rows arrive blank, 3 lose two fields and 1 message is cut | | Our Python script | 100 (99 on the first run) | 20 | One missing space, fixed | | AI prompt | 100 | 20 | Nothing in this run | ## The tutorial recipe broke in seven ways The failures follow the layouts exactly. The tutorial split failed on the missing phone line (email 7), on all four tables, and on all three `E-mail:` emails. It read `Carter CPA` as Ben Carter's name, because `Name:` first appears inside `Company Name:`. It kept only the first line of email 5's three-line message. It wrote empty rows for the two stacked layouts. And on the reply, it filled the lead's Email and Phone with the colleague's signature. The guarded version fixed three of those. The missing phone no longer stops the run, and by reading the last `Name:` and the last `Phone:`, it got the names and the reply right. Its failures are quieter, which is a different problem. A failed run shows up in the flow's history; a blank row looks like a lead who skipped the form. Two caveats are ours. We wrote the script while looking at all six layouts, so it is a best case: a new seventh layout will break it until you add the label. And we ran the AI prompt once. Twenty emails is a small sample, so 100 out of 100 says the method can work on messy emails, not that it never errs. ## 1. Pick the method by how many formats you receive The number of different layouts in your inbox decides this more than price does. | | Power Automate flow | Python script | AI prompt in a flow | |---|---|---|---| | Best for | One sender, one fixed layout | A backlog, any mailbox, mixed layouts | Layouts that change or come from many senders | | Runs | By itself when mail arrives | When you run it | By itself when mail arrives | | Cost | [Free license](https://learn.microsoft.com/en-us/power-platform/admin/power-automate-licensing/types) for work and school accounts | Free; needs Python 3 | [Credits per run](https://learn.microsoft.com/en-us/ai-builder/credit-management), billed by tokens | | Breaks when | A label changes, moves or goes missing | A sender uses a label you did not list | Not in our one run; each run costs credits | | Main limit | [25 MB workbook](https://learn.microsoft.com/en-us/connectors/excelonlinebusiness/); don't edit it while the flow writes | You save the emails yourself first | Email text goes to an AI service | Our view: if one website form sends all the email, build the flow. If three suppliers each send their own layout, build three flows, each filtered to one sender, rather than one flow with clever expressions. Reach for AI when the layouts are too many to keep up with. ## 2. Build a flow for one sender's format This needs a Microsoft 365 work or school account. All three connectors used here, [Office 365 Outlook](https://learn.microsoft.com/en-us/connectors/office365/), [Content Conversion](https://learn.microsoft.com/en-us/connectors/conversionservice/) and [Excel Online (Business)](https://learn.microsoft.com/en-us/connectors/excelonlinebusiness/), are standard connectors, which the [free license](https://learn.microsoft.com/en-us/power-platform/admin/power-automate-licensing/types) covers. That license doesn't let you share the flow, so it runs in one person's account. 1. **Make the table first.** Create a workbook in OneDrive for Business or SharePoint. Type the headers `Received`, `Name`, `Email`, `Phone`, `Company`, `Message`, `Missing` in row 1, select them and choose **Insert** and then **Table**. The connector's [Add a row action needs a table](https://learn.microsoft.com/en-us/connectors/excelonlinebusiness/), in an .xlsx or .xlsb file. Avoid headers that are only a number; Microsoft lists them as a cause of unexpected behavior. 2. **Add the trigger.** Create an automated cloud flow with **When a new email arrives (V3)**. Set **From** to the one sender address, for example `noreply@yourform.example`, and **Subject Filter** to text that is always in the subject. The trigger reacts to new mail, so it fills the table from today; mail already in the inbox needs step 3. 3. **Convert the body.** Add **Html to text** and give it the trigger's **Body**. Its [documented limits](https://learn.microsoft.com/en-us/connectors/conversionservice/): 5 MB of content, and lines broken at 80 characters. Microsoft also says line breaks in the input "will be collapsed into space". If your sender's emails are plain text, check the trigger's **Is HTML** output on a test run; when it is false, skip this step and split the **Body** itself. 4. **Cut out each field with a guard.** Add a **Compose** action per field, named after the field, with this expression for Phone: ``` if(contains(body('Html_to_text'), 'Phone:'), trim(first(split(last(split(body('Html_to_text'), 'Phone:')), decodeUriComponent('%0A')))), '') ``` `body('Html_to_text')` must match your conversion action's name. [`contains()`](https://learn.microsoft.com/en-us/azure/logic-apps/expression-functions-reference) checks that the label is there before anything is split, and it is case-sensitive, so `phone:` will not match. `last()` takes the text after the last `Phone:`, which skips a colleague's signature at the top of a reply. `decodeUriComponent('%0A')` is a line break. Copy it for each field and change the label. 5. **Flag the gaps.** Add one more **Compose** named `Missing` that lists empty fields, for example `concat(if(empty(outputs('Name')), 'Name ', ''), if(empty(outputs('Phone')), 'Phone ', ''))`, extended to every field. 6. **Write the row.** Add Excel Online (Business) **Add a row into a table**, pick the file and table, and map each column to its Compose output. Map `Received` to the trigger's **Received Time**. ## Test the flow with three emails before trusting it Send the form to yourself three times: once filled in fully, once with the phone left out, and once with a message longer than 80 characters. Then look at all three rows. The first should be complete, the second should name Phone in the Missing column, and the third shows whether the converter cut your message. ## Know the Excel limits before the table grows Microsoft's [Excel Online (Business) reference](https://learn.microsoft.com/en-us/connectors/excelonlinebusiness/) lists these, and the first two cause most of the trouble: - "Simultaneous file modifications made by other connectors, or manual edits are not supported." If someone keeps the workbook open and edits it while mail arrives, rows can conflict. Keep the flow's table in its own workbook, and point your reports at it. - A file "may be locked for an update or delete up to 6 minutes since the last use of the connector", and a new row can take up to 30 seconds to appear. - The workbook can be at most 25 MB, and each connection gets 100 calls a minute. The Outlook connector allows [300 a minute](https://learn.microsoft.com/en-us/connectors/office365/). - Heavy formulas in the table can time out the connector, and its retry can then insert the same row more than once. Keep calculations on another sheet, or set the workbook to manual calculation, as Microsoft suggests. ## 3. Run the script on saved emails The script works on any mailbox, including a personal one, the new Outlook and Outlook on the web. It also clears a backlog the flow can't see. Its weakness is the first step: you save the emails yourself. In Outlook on the web, select a message, then **More actions** and **Download**, which saves it as [an EML or MSG file](https://support.microsoft.com/en-US/Outlook/mail/save-an-outlook-message-as-a-eml-file-a-pdf-file-or-as-a-draft). The script reads only .eml files. 1. Save the emails as .eml files into one folder, for example `Downloads/leads`. 2. Save the script below as `email_to_csv.py`. Edit `FIELDS` so each column lists every label your senders use for it. 3. Run `python3 email_to_csv.py Downloads/leads leads.csv`. 4. In Excel, choose **Data**, then [**From Text/CSV**](https://support.microsoft.com/en-us/office/import-or-export-text-txt-or-csv-files-5250ac4c-663c-47ce-937b-339e391393ba) in the Get & Transform Data group, pick `leads.csv`, choose **Import**, check the preview and choose **Load**. ```python """Pull labelled fields out of a folder of .eml files into one CSV for Excel. Usage: python3 email_to_csv.py Edit FIELDS for your own forms. Standard library only. """ import csv, re, sys, pathlib, email, email.policy from email.utils import parsedate_to_datetime from html.parser import HTMLParser FIELDS = { # column: the labels senders use for it "Name": ["Name", "Full name", "Contact name"], "Email": ["Email", "E-mail", "Email address"], "Phone": ["Phone", "Telephone", "Mobile"], "Company": ["Company", "Company name", "Organization"], "Message": ["Message", "Comments"], } MULTILINE = {"Message"} # these keep reading until the next label QUOTE = re.compile(r"^(-{2,}\s*(Forwarded message|Original Message)\s*-{2,}|On .+ wrote:)$", re.I | re.M) STOP = re.compile(r"^(Sent from |--\s*$)", re.I) class Text(HTMLParser): # HTML to lines; a table row becomes "label: value" def __init__(self): super().__init__(); self.out, self.cells, self.cell = [], None, "" def handle_starttag(self, tag, attrs): if tag == "br" and self.cells is not None: self.cell += " " # line break inside a cell elif tag in ("p", "div", "br", "h1", "h2", "h3", "li"): self.out.append("\n") if tag == "tr": self.cells = [] if tag in ("td", "th"): self.cell = "" def handle_endtag(self, tag): if tag in ("td", "th") and self.cells is not None: self.cells.append(self.cell.strip()) if tag == "tr" and self.cells: self.out.append("\n" + self.cells[0].rstrip(":") + ": " + " ".join(self.cells[1:]) + "\n") self.cells = None def handle_data(self, data): if self.cells is not None: self.cell += data else: self.out.append(data) def body_text(msg): part = msg.get_body(("plain", "html")) if part is None: return "" text = part.get_content() if part.get_content_subtype() == "html": p = Text(); p.feed(text); text = "".join(p.out) return text.replace("\r\n", "\n").replace("\xa0", " ") def label_pattern(): alts = {re.escape(l.lower()): col for col, ls in FIELDS.items() for l in ls} rx = re.compile(r"^\s*(" + "|".join(sorted(alts, key=len, reverse=True)) + r")\s*:\s*(.*)$", re.I) return rx, alts def parse(text): m = list(QUOTE.finditer(text)) if m: text = text[m[-1].end():] # forwarded or replied: read only the original rx, alts = label_pattern() lines = [l.strip() for l in text.split("\n")] row, col = {}, None for line in lines: hit = rx.match(line) if hit: col = alts[re.escape(hit.group(1).lower())] if col in row: col = None; continue # keep the first value of each field row[col] = hit.group(2).strip() elif col and line and not STOP.match(line): if not row[col]: row[col] = line # value on the next line elif col in MULTILINE: row[col] += " " + line else: col = None elif not line and col and row.get(col): col = None return row def main(src, out): files = sorted(pathlib.Path(src).glob("*.eml")) with open(out, "w", newline="", encoding="utf-8-sig") as f: # utf-8-sig: Excel reads å, ä, ö w = csv.writer(f) w.writerow(["File", "Received", *FIELDS, "Missing"]) for path in files: msg = email.message_from_bytes(path.read_bytes(), policy=email.policy.default) when = parsedate_to_datetime(msg["Date"]).strftime("%Y-%m-%d %H:%M") if msg["Date"] else "" row = parse(body_text(msg)) missing = [c for c in FIELDS if not row.get(c)] w.writerow([path.name, when, *(row.get(c, "") for c in FIELDS), " ".join(missing)]) print(f"{path.name}: {len(FIELDS) - len(missing)} of {len(FIELDS)} fields" + (f", missing {' '.join(missing)}" if missing else "")) print(f"wrote {len(files)} rows to {out}") if __name__ == "__main__": main(sys.argv[1], sys.argv[2]) ``` ### What it printed on our 20 emails This is its real output, run with Python 3.11 on Linux. Three runs took 0.07 to 0.08 seconds each. Email 7 has no phone line, and the script says so instead of failing: ``` 01.eml: 5 of 5 fields 02.eml: 5 of 5 fields 03.eml: 5 of 5 fields 04.eml: 5 of 5 fields 05.eml: 5 of 5 fields 06.eml: 5 of 5 fields 07.eml: 4 of 5 fields, missing Phone 08.eml: 5 of 5 fields 09.eml: 5 of 5 fields ... 20.eml: 5 of 5 fields wrote 20 rows to leads.csv ``` The first version got 99 of 100 fields. Email 11 has a two-line message inside a table cell, split by an HTML line break, and the script glued the lines together as `April.Can`. We added the line that turns a break inside a cell into a space, and the second run got all 100. That is the honest picture of this method: it is exact once you have taught it every quirk, and every new quirk is a fix you make yourself. How it handles the traps from the test: labels are matched only at the start of a line, so `Name` doesn't match inside `Company Name`. When a label line is empty, it takes the next line as the value. And if the email contains a forwarding or reply marker, it reads only the text after the last one, which drops the colleague's signature. ## 4. Use an AI prompt when layouts keep changing The AI route swaps the expressions for one instruction. This is the prompt we gave, once per email, with the raw body as input: ``` This is the body of an email notifying us of a website form submission. It may be HTML, forwarded or replied to. Extract the details of the person who filled in the form: Name, Email, Phone, Company, Message. Use only text in the email. If a field is not present, return an empty string. Keep the message text exactly as written, joining lines with a single space. Return JSON only. ``` It returned all 100 values correctly, including the tables and the stacked layouts. It also skipped the colleague's signature in the reply; the prompt only asked for "the person who filled in the form". That run used a Claude model in our sandbox, not Microsoft's AI tools, so treat it as evidence for the method, not for any one product. Inside Power Automate, the same idea is an AI Builder prompt action, and that is where the cost comes in. Microsoft's [credit management page](https://learn.microsoft.com/en-us/ai-builder/credit-management), updated October 8, 2026, says prompts always consume credits outside testing, priced on input and output tokens and the model you pick. New customers can no longer buy the AI Builder credit add-on and must buy Copilot Credits instead. The 5,000 AI Builder credits a month that came with each Power Automate Premium license are granted only up to November 1, 2026. When an environment runs out of AI Builder credits and has no Copilot Credits to fall back on, AI Builder actions fail with errors such as `QuotaExceeded`. Two more trade-offs. Every email body goes to an AI service, so check your company's rules before you send customer data there. And a wrong answer from a model looks exactly like a right one, so keep the Missing column and spot-check rows against the emails. ## 5. Skip Power Query's Exchange connector for new workbooks Excel can also read a mailbox directly. Power Query's [Microsoft Exchange Online connector](https://learn.microsoft.com/en-us/power-query/connectors/microsoft-exchange-online) imports mail into a workbook, and [`Text.BetweenDelimiters`](https://learn.microsoft.com/en-us/powerquery-m/text-betweendelimiters) can cut a field out of the body. That is the same label logic as the flow, with the same weak spots. The bigger problem is the date. Microsoft's page says the connector "will be replaced by new Exchange Mail and Calendar connectors in Excel Power Query by April 2027". Workbook owners must migrate connections by hand. The message center notice, [MC1461158](https://mc.merill.net/message/MC1461158), says existing workbooks "will not automatically transition", and that the new connectors need Microsoft 365 Apps or Office 2024. Our view: don't start a new workbook on a connector with a retirement date. If you already have one, list it now so the April deadline doesn't surprise you. ## Check the Missing column every week Whichever method you pick, the dangerous failure is a quiet one: a row that arrives blank or half full. Sort the table by the Missing column once a week and open the emails behind any row that has an entry. A cluster of misses from one sender means its layout changed, and your flow or script needs a new label. If you are still deciding whether this is the task to automate first, our [one-week tally](/articles/noticing-what-to-automate/) will tell you. When the emails carry invoices as attachments, [saving Outlook attachments automatically](/articles/save-outlook-attachments-automatically/) covers that half of the job. The [weekly report guide](/articles/automate-weekly-report/) can turn the finished table into a report on a schedule, and [our checks for scheduled scripts](/articles/tiny-automation-in-code/) make a broken run speak up. If your mail is in Gmail and your table in Google Sheets, [our Gmail to Google Sheets guide](/articles/gmail-to-google-sheets/) does the same job with a free Apps Script. More guides are under [automation](/topics/automation/). Start with the three test emails from the flow section today: full, missing a phone, long message. They will show you which kind of failure your setup has before a real lead falls into it. ## Sources - Microsoft Learn, [Content Conversion connector reference](https://learn.microsoft.com/en-us/connectors/conversionservice/) (Html to text behavior and limits) - Microsoft Learn, [Expression functions reference](https://learn.microsoft.com/en-us/azure/logic-apps/expression-functions-reference) (split, contains, last, decodeUriComponent; applies to Power Automate) - Microsoft Learn, [Office 365 Outlook connector reference](https://learn.microsoft.com/en-us/connectors/office365/) - Microsoft Learn, [Excel Online (Business) connector reference](https://learn.microsoft.com/en-us/connectors/excelonlinebusiness/) - Microsoft Learn, [Types of Power Automate licenses](https://learn.microsoft.com/en-us/power-platform/admin/power-automate-licensing/types) (updated September 22, 2026) - Microsoft Learn, [Licensing and AI Builder credits](https://learn.microsoft.com/en-us/ai-builder/credit-management) (updated October 8, 2026) - Microsoft Learn, [Microsoft Exchange Online Power Query connector](https://learn.microsoft.com/en-us/power-query/connectors/microsoft-exchange-online) - Microsoft Learn, [Text.BetweenDelimiters](https://learn.microsoft.com/en-us/powerquery-m/text-betweendelimiters) - Microsoft 365 message center MC1461158, Prepare for upcoming changes to the Excel Exchange connector (August 24, 2026), via the [merill.net mirror](https://mc.merill.net/message/MC1461158) - Microsoft Support, [Save an Outlook message as a .eml file, a PDF file, or as a draft](https://support.microsoft.com/en-US/Outlook/mail/save-an-outlook-message-as-a-eml-file-a-pdf-file-or-as-a-draft) - Microsoft Support, [Import or export text (.txt or .csv) files](https://support.microsoft.com/en-us/office/import-or-export-text-txt-or-csv-files-5250ac4c-663c-47ce-937b-339e391393ba) - 28creation test, October 20, 2026: 20 sample .eml files built with Python's email library; the label-split method emulated in Python from Microsoft's documented rules; the script above run on Python 3.11; the AI prompt run once on a Claude model