TL;DR: One Office Script that turns any CSV into a JSON array inside a flow. Pass the CSV text in, get the JSON back. Column names come from the header row, the delimiter (comma, semicolon, tab, pipe) is detected automatically, and quoted values with commas or line breaks inside are parsed correctly. No premium connector, no external service.

📥 Download

• csv-to-json.osts, the Office Script file for Excel
• csv-to-json.ts, the same script as plain TypeScript text (open it, copy the code)

🔧 Install the script

1. Open any workbook in Excel for the web and go to Automate > New Script > Create in Code Editor.
2. Delete the template code, paste the full content of csv-to-json.ts and select Save. Name the script CSV to JSON.

Alternative: upload csv-to-json.osts to the Documents/Office Scripts folder in your OneDrive. Scripts stored there show up in the Script Gallery (Office Scripts file storage).

The script never reads or changes the workbook. It runs on any workbook, even an empty one. Create a blank Dummy.xlsx in OneDrive or SharePoint, because the Run script action needs a workbook to run against.

🔧 Use it in a flow

1. Get the CSV text. Add Get file content (SharePoint or OneDrive for Business). A .csv file comes back as plain text, see Tip 124. Any other source works too: an email body, an HTTP response, a Compose.

2. Run the script. Add Run script from the Excel Online (Business) connector:

Field	Value
Location, Document Library, File	the blank Dummy.xlsx
Script	CSV to JSON
csvText	string(body('Get_file_content'))
delimiter	auto (default)
hasHeader	true (default)
detectTypes	false (default)

3. Use the JSON. The action returns four values as dynamic content:

Output	Content
json	the JSON array as text
rowCount	number of data rows
delimiter	the delimiter that was used
columns	the final column names

Turn the text into an array with json(body('Run_script')?['result']?['json']). Loop over it with Apply to each and read values by column name: item()?['Name']. The columns can change from file to file, and a missing column returns null instead of failing the flow. If the column names are fixed, add Parse JSON with body('Run_script')?['result']?['json'] as content and generate the schema from a sample.

Example input and output:

Name;City;Amount
Müller;Zürich;12.50
"Meier, Hans";Bern;7

[
  { "Name": "Müller", "City": "Zürich", "Amount": "12.50" },
  { "Name": "Meier, Hans", "City": "Bern", "Amount": "7" }
]

Schema for Parse JSON (for this example):

{
  "type": "array",
  "items": {
    "type": "object",
    "properties": {
      "Name": { "type": "string" },
      "City": { "type": "string" },
      "Amount": { "type": "string" }
    }
  }
}

⚙️ Parameters

Parameter	Default	Meaning
csvText	required	The CSV content as text.
delimiter	auto	auto detects comma, semicolon, tab or pipe. Or set comma, semicolon, tab, pipe or any single character. A first line sep=; as written by Excel is respected.
hasHeader	true	true: the first row holds the column names. false: columns are named column1, column2, and so on.
detectTypes	false	false: every value stays text, exactly as in the file. true: 42 and 3.14 become numbers, true and false become booleans. Values with leading zeros (007), a plus sign (+41...) or more than 15 digits, decimal commas (3,14) and Swiss thousands separators (1'250.50) always stay text.

✅ What the script handles

🔸 Quoted values with the delimiter, line breaks or doubled quotes ("") inside (RFC 4180).

🔸 Windows (CRLF), Unix (LF) and old Mac (CR) line endings, and a UTF-8 BOM at the start.

🔸 Empty lines and lines that contain only delimiters, such as the ;;; rows Excel writes at the end of an export.

🔸 Empty or duplicate column names: they become column3 or Name_2, so no column is lost.

🔸 Rows with fewer or more values than the header: missing values become "", extra values get their own columnN.

🔸 An unclosed quote stops the script with an error that names the line, instead of returning shifted data.

⚠️ Good to Know

🔸 License: Office Scripts in Power Automate require a Microsoft 365 business license. Office 365 E1 and F3 can use scripts with Power Automate but have no Power Automate integration inside Excel. The Excel Online (Business) connector is a standard connector, so no premium license is needed (Run Office Scripts with Power Automate).

🔸 Limits (Platform limits): 1,600 Run script calls per user per day (resets at 12:00 AM UTC), a 120-second timeout per call, and 30,000,000 bytes (28.6 MB) for the parameters of one call. For very large CSV files, split the text into chunks of whole lines and call the script per chunk.

🔸 Encoding: the script receives text. A CSV saved in a legacy encoding such as Windows-1252 arrives with � instead of umlauts. Save it as CSV UTF-8.

🔸 Auto-detection compares the first 10 lines and picks the delimiter that appears equally often in most of them. If two delimiters tie, for example a header like Name,First name;Age with one comma and one semicolon in every line, set delimiter explicitly.

🔸 After changing the script (parameters or return values), delete the Run script action and add it again. The flow stores the script signature when the action is created (Troubleshoot Office Scripts in Power Automate).

🔸 No external calls: the script contains no fetch and no library, which is why it runs inside Power Automate.

🔗 Related Tips

• #PowerPlatformTip 125: Convert CSV to JSON, the version with standard actions only, for CSV files without quoted values.
• #PowerPlatformTip 157: Run Office Scripts from Power Automate, how the Run script action, parameters and return values work.
• #PowerPlatformTip 124: Recognize Plain Text File Formats, how Get file content returns CSV and other text files.