cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bpearce
Frequent Visitor

Excel Column size determines value read?

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

hash.jpg

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?

 

1 ACCEPTED SOLUTION

Accepted Solutions

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.

 

shindomo_1-1634212202502.png

 

shindomo_2-1634212284489.png


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.

 

shindomo_0-1634211968795.png

 

shindomo_3-1634212483593.png

 

PAD Version: 2.13.163.21263

 

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

 

Thank you.

View solution in original post

5 REPLIES 5
NikosMoutzou
Microsoft
Microsoft

Hello @bpearce !

 

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

NikosMoutzou_0-1634194408600.png

 

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.

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.

 

shindomo_1-1634212202502.png

 

shindomo_2-1634212284489.png


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.

 

shindomo_0-1634211968795.png

 

shindomo_3-1634212483593.png

 

PAD Version: 2.13.163.21263

 

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

 

Thank you.

View solution in original post

VJR
Solution Supplier
Solution Supplier

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

 

VJR_0-1634214856757.png

 

bpearce
Frequent Visitor

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!!!

MichaelAnnis
Impactful Individual
Impactful Individual

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.

Helpful resources

Announcements
MPA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Top Solution Authors
Top Kudoed Authors
Users online (2,555)