Answered

Excel Column size determines value read?

(0) Share

Report

Posted on by bpearce

Surely this is a bug. Imagine a column of dates in Excel such as this:

Why the hashes in cell A4? Well Excel will display hashes when the column width is too small to display the date - but the underlying value of the cell is 10/10/2021. By simply expanding the column width Excel will display 10/10/2021 (trust me). Despite how the data is displayed in Excel, wouldn't you expect Power Automate Desktop (PAD) to read the date value when cell A4 is read? Well in the voice of Gomer Pyle "Surprise Surprise Surprise..." PAD reads the string value "#########" for cell A4. This of course wrecks havoc later in the flow when "#########" is treated as a date value. Surely this is not the intended behavior...

If anyone can shed some light on this I would greatly appreciate it. If it is a bug, how should I report it?

Categories:

Power Automate Desktop

I have the same question (0)

All responses (5)

Answers (1)

Sort by

NikosMoutzou Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hello @bpearce !

I was not able to reproduce this issue, as you may observe from the following screenshot:

Could you please share the Power Automate Desktop version you are using? You may find it in the Console component, in the About option of Help menu.

Was this reply helpful? Yes No
Verified answer

Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hello @bpearce @NikosMoutzou

I have tried and found that how to reproduce.

In the action "Read from Excel worksheet", if you set "Get cell contents as text" to ON in Advanced options, you will have "########" as value.

However, if you set "Get cell contents as text" to OFF (default), there is no problem and you can get date as datetime type of value.

PAD Version: 2.13.163.21263

I myself guess this behavior is by design of PAD software...

Thank you.

Was this reply helpful? Yes No
VJR 7,635 on at

Like
a
(1)

Report
Copy link

Link copied!

That's correct, this is by design as per the Excel Object model.

In VBA
.Text shows as ### in the "Immediate" output window at the bottom
.Value shows the actual cell value

Was this reply helpful? Yes No
bpearce 26 on at

Like
a
(0)

Report
Copy link

Link copied!

I really appreciate the insights provided. What I now understand is the PAD setting to "get cell content as text" by design will get "#########" if the column is too narrow. Since I have no control over the column width of my source data files AND PAD has no ability to change a column's width I have no option but to read the Excel data as the default data type which in my case is datetime. This complicates my flow, but it is workable.

Ever stop to think that 10/10/2021 is the first date in the calendar year that has 10 characters? I've been running this flow for months just fine until this week... now crashing because of a column width... just seems ridiculous... Knowing this why would anyone ever use "get cell content as text" in a production flow???

Thanks again for all the help. Great community!!!

Was this reply helpful? Yes No
MichaelAnnis 5,727 Moderator on at

Like
a
(1)

Report
Copy link

Link copied!

The only time I can think of legitimately would be something that would come across as the wrong format.

For instance, if a cell had a value 0001, PAD may convert this to 1 as a number type variable vs by checking that option, you would get 0001 as text.

Was this reply helpful? Yes No