Answered

Excel "Save as txt" results in unexpected date format

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

Dear community,

I'm rather new to power automate and hope to get an answer to a rather simple question: How to get the correct date format when saving an excel file as text file?

What am I doing:

I'm parsing a bunch of xlsx files to convert them into tab-delimited text files which works great and easy unless date columns are used.

- The power automate excel save-as routine converts a date column formated ad dd.mm.yyyy to mm-dd-yyyy.

- Doing the same manually in Excel by using save as tab-delimited text file results in the same format (dd.mm.yyyy).

My regional settings are all set to German (de-DE) and I would expect this format to be used in power automate desktop, too.

Any hints are greatfully appreciated.

Thanks in advance

Stefan

Categories:

Power Automate Desktop

All responses (11)

Answers (1)

Sort by

XRQ57 206 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi,
do you convert the date in text?
You can personalize the format then.
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

No, I don't. Why would I need this?
I just use the excel.save function (with save as text selected) as I do when using excel myself.
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

The reason is that the date can be in various columns and I don't know in which column a date is stored.
Any other hints?
ryule 929 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!
I'm not sure why it behaves this way, but you may try this for a workaround. I had to do something similar to get the commas out of my numbers, and this was the only way I could do it:
Read the column
Insert a clean column next to it
set a count variable
Loop through the column
The highlighted portion is where I replaced my comma with nothing. Here is where you could have a few steps to get CurrentItem[0] and format the date.
Write the new value to the column and row with the count variable
increase the variable
Delete the original column (which is now one column further to the right, so my original Q became R)
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Thanks for the above workaround which requires to know the date column. In fact that is what I don't know, as the file is very dynamic and a date can be in any column and sometimes the column is having a general format which makes it even harder to find for me.
What about using ui-commands to simulate the online behaviour? Would that be a suitable workaround?

Cheers
Stefan
ryule 929 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

I'd say it's definitely worth a shot and let us know how it goes or if you run across any other issues!
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

I see two problematic areas which are related to popups.
1) If the file already exists a popup comes up to confirm overwriting
2) if the sheet contains more than one sheet another popups needs to be confirmed

In fact, I don't know how to deal with random cases in PAD. Help would be appreciated.
ryule 929 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

Yes, random cases are not ideal for RPA development.
However, it there are only a certain number of "random" scenarios, you can build your bot to handle them, however it is much more complex.
Sounds like you have a lot of random scenarios with popups, dates that are dates, dates that are general format, dates that show up in random columns, so it may not be an ideal use case for RPA development until you standardize the process itself.
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

The process is standardised and pretty flexible, it's only the saveas function which creates the issue. If that would do proper job, no reason to consider workarounds.
Verified answer

ryule 929 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

Perhaps I'm not understanding, as previously you stated "Thanks for the above workaround which requires to know the date column. In fact that is what I don't know, as the file is very dynamic and a date can be in any column and sometimes the column is having a general format which makes it even harder to find for me.", which makes it sound like it's not standardized.

Perhaps you can report this as a bug and/or reach out to support if you don't think it is working properly.