Power Platform Community Forum Thread Details

I’m building a flow in Power Automate where I loop through JSON lines and then append values into an array. Later I use the array to create a CSV file.

One of the fields is a Business Unit Code, which is a string (exported from JSON code). The value will have a leading 0 i.e. 03
When I check the output in the CSV, I just see 3 — the leading zero is stripped.

Here’s what I’ve tried so far:

string(...) → still gives me 3.
formatNumber(int(...), '00') → works for padding single digits, but fails when the value is blank. I tried wrapping it in an if(empty(...), '', formatNumber(...)) but the result still shows as 3 when I open the CSV.
Quoting the value ("03") or ( '03)works but I don’t want quotes or a dash in the CSV.
Looked at padLeft(), but that isn’t available in Power Automate.
Tried text tricks like concatenating ' or other characters, but Excel is very good at auto-formatting CSV data into numbers.

It feels like the CSV file does contain 03, but as soon as Excel opens it, it reinterprets it as 3. I’d like to avoid requiring users to do a manual import into Excel (Data > From Text/CSV > set column to Text).

Does anyone know a reliable way to force Excel to respect leading zeros in CSVs generated from Power Automate without adding visible quotes?

Categories:

Building flows

Using FormatNumber() with a '00' mask is the right way to do it. The problem is that if you put a 03 inside a CSV without using ""'s around it then it will be read as a simple 3 when you open the file. That's not a problem with the creation of the CSV, its the way a CSV is designed to work. If you want the CSV to read 03 as a string then it has to be in quotes.

In short, there is no way to get Excel to respect leading zeros in a CSV without using visible quotes. In a true .xlsx file there is because the cell can have a format applied. But CSVs are just values. There is no schema or format for the columns.

----------------------------------------------------------------------------------
If this Post helped you, please click "Does this answer your question" and give it a like to help others in the community find the answer too!

Paul Papanek Stork, MVP
Blog: https://www.dontpapanic.com/blog