Different date-format in Excel vs. Ranorex

Ask general questions here.
User avatar
Mudhenn
Posts: 20
Joined: Fri Apr 22, 2016 12:28 pm
Location: Vienna, Austria

Different date-format in Excel vs. Ranorex

Post by Mudhenn » Fri Apr 22, 2016 12:42 pm

Hello
I am using an Data-Connector to an Excel-Sheet. In that sheet I have (beside other data) some columns with dates. I use the german date-format like dd.mm.jjjj in my Excel-Sheet and on the webpage I'd like to test, this is also my systemwide setting for dates. Now on some data-bindings, Ranorex changes the "01.05.2016" from Excel to "05/01/2016 00:00:00" an I can't figure out how to change that behavior. On some connections it works properly, on some it doesn't. Has onyone a hint where to look?

User avatar
Support Team
Site Admin
Site Admin
Posts: 12145
Joined: Fri Jul 07, 2006 4:30 pm
Location: Houston, Texas, USA
Contact:

Re: Different date-format in Excel vs. Ranorex

Post by Support Team » Mon Apr 25, 2016 2:58 pm

Hello Mudhenn,

Ranorex does not change values which are retrieved via a data connector.

Every correctly specified date is stored as a DateTime element within Excel, for example "05/01/2016 00:00:00", depending on your language settings. Other elements like "01.05.2016" might just be stored as a normal string within Excel.

In order to overcome that issue, you could either specify all dates as string values within Excel or adapt the passed values from the data connector before actually using the data:

For example:
try {
       			var convertedDateTime = Convert.ToDateTime(VariableLinkedToDataConnector);
       			Report.Info(convertedDateTime.ToString("dd.MM.yyyy"));
       			
       		} catch (Exception ex) {
       			Report.Error("Failed to convert data: " + ex.Message);
       		}
Hope this information helps.

Sincerely,
Robert

User avatar
Mudhenn
Posts: 20
Joined: Fri Apr 22, 2016 12:28 pm
Location: Vienna, Austria

Re: Different date-format in Excel vs. Ranorex

Post by Mudhenn » Tue Apr 26, 2016 9:09 am

Hello

First of all, thank you for your help, I will try something like that (I use VB in that project, but it should be easy to adapt).

Second, I also guessed that the error happens in excel so I changed the file to .csv and it still happens. I think it is some buggy part on the data-connector, since today it started to throws erroros like "System.InvalidOperationException: Invoke oder BeginInvoke kann für ein Steuerelement erst aufgerufen werden, wenn das Fensterhandle erstellt wurde." when I try to preview the data set. Maybe it is the best to start the whole project over and/or re-bind the the data-sources.

Diddy
Posts: 7
Joined: Tue Feb 09, 2016 4:00 pm

Re: Different date-format in Excel vs. Ranorex

Post by Diddy » Tue Apr 26, 2016 11:38 am

Mudhenn wrote:[...]since today it started to throws erroros like "System.InvalidOperationException: Invoke oder BeginInvoke kann für ein Steuerelement erst aufgerufen werden, wenn das Fensterhandle erstellt wurde." when I try to preview the data set.[...].
This should be fixed since 5.4.6... Which version of Ranorex do you use currently?

User avatar
Mudhenn
Posts: 20
Joined: Fri Apr 22, 2016 12:28 pm
Location: Vienna, Austria

Re: Different date-format in Excel vs. Ranorex

Post by Mudhenn » Tue Apr 26, 2016 12:31 pm

Diddy wrote:
Mudhenn wrote:[...]since today it started to throws erroros like "System.InvalidOperationException: Invoke oder BeginInvoke kann für ein Steuerelement erst aufgerufen werden, wenn das Fensterhandle erstellt wurde." when I try to preview the data set.[...].
This should be fixed since 5.4.6... Which version of Ranorex do you use currently?
You are right, thank you for that hint, I made an Update (5.4.4 to 5.4.6) and at least that error disappeared.

Diddy
Posts: 7
Joined: Tue Feb 09, 2016 4:00 pm

Re: Different date-format in Excel vs. Ranorex

Post by Diddy » Tue Apr 26, 2016 1:42 pm

Nice to hear that! :)

Back to the topic: Think you must not format the cells with "Date"... or is it possible to start the date values with a ' in your Excel file?

Have fun!

PS: Save the XLSX as CSV and use CSVConnector works for me so it has to be some Excel issue (?)...

User avatar
Mudhenn
Posts: 20
Joined: Fri Apr 22, 2016 12:28 pm
Location: Vienna, Austria

Re: Different date-format in Excel vs. Ranorex

Post by Mudhenn » Thu Apr 28, 2016 9:01 am

Hello,

just a short update how I solved it eventually: I convertet all cells in Exel to "Text" so I get the Date as a string which works so far.

I think there is something fishy in the interaction with excel when formatted as a date and when the excel sheet is kept on a share and not local (not everyone can choose the path freely, normal users here have nearly no access to or at least should not use their local drives)

Thanks again for your help.

Best regards,
Frank

krstcs
Posts: 2683
Joined: Tue Feb 07, 2012 4:14 pm
Location: Austin, Texas, USA

Re: Different date-format in Excel vs. Ranorex

Post by krstcs » Thu Apr 28, 2016 1:07 pm

You should NOT be using a share for ANY files in your test, including data. If you read the documentation/user guide, it is very clear that Ranorex does support running any part of the system from a share. This is because Windows does not update/read/write share data the same as it does local data.

Move your XLS file into your project directory on your local drive and add it to your project and your source control scheme (you should be using source control, if you aren't, such as git, SVN, TFS, etc.).
Shortcuts usually aren't...