cancel
Showing results for 
Search instead for 
Did you mean: 

Using Excel file as input to Soap call. Date issue uses slash separation.

SOLVED
Highlighted
New Contributor

Using Excel file as input to Soap call. Date issue uses slash separation.

Hi 

 

I'm trying to call a soap method where one of the inputs is a date. All the other input work fine. 

The date-format in the excel sheet is DD-MM-YYYY but when SoapUI Pro reads it, it comes out as DD/MM/YYYY. I.e. it is separated by slashes instead of hyphens. Now the request does not adhere to the xsd schema and hence I cannot send it. 

 

So the question is how do I change the date format of the input in SoapUI?

 

Thanks in advance.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Hero

Re: Using Excel file as input to Soap call. Date issue uses slash separation.

Hey @MikeD1,

I'm actually almost 100% positive Vallarasu actually gave you the correct answer.

The reasons for this is that ReadyAPI! shouldn't be changing anything, it doesn't even include the functionality to change whats been read in without some effort by yourself and seeing as i'm assumimg you haven't created any event handlers, etc. to change the date format, it must be due to Excel.

Also, Excel doesn't always display the format of the value that is actually being stored, especially with non string types.
The easiest way to check that excel is the issue would be to source the date value from a notepad file instead....you'll see that whatever is written in file will be picked up by ReadyAPI!

Excel doesn't always display the value that is represented by the 'type' specified and i know there is an issue with Excel setting the type on a field AFTER the field has had a value in it (google it youll see). You might have to create a new worksheet, set the datatype to what you want and then add the values to it.

To summarise: excel doesnt necessarily display the value with the type that is set, especially if you try altering the type after a value has been stored in the field. To prove its excel, source the date value from a .txt file (that has no formatting) and youll see whatever value is sourced is what is saved in the text file.

Cheers,

Rich
if this helped answer the post, could you please mark it as 'solved'? Also if you consider whether the title of your post is relevant? Perhaps if the post is solved, it might make sense to update the Subject header field of the post to something more descriptive? This will help people when searching for problems. Ta

View solution in original post

4 REPLIES 4
Highlighted
Frequent Contributor

Re: Using Excel file as input to Soap call. Date issue uses slash separation.

Hi Mike,
The simple way of handling this is by changing the format of excel column as string. Then save the date in required format.So it doesn't auto change during execution.

Hope this helps

With Regards
Vallalarasu Pandiyan | LinkedIn |Vallalarasu.p@gmail.com
Highlighted
New Contributor

Re: Using Excel file as input to Soap call. Date issue uses slash separation.

Hi Vallalarasu

 

Thank you for your reply. 

 

However your proposed solution does not fix the problem. 

The problem is that SoapUI changes the dateformat compared to the format in the Excel file and then the request does not validate against the xsd schema. So I cannot send the request.

I do not have the possibility to change the schema that is validated against. 

 

Best regards

Michael

Highlighted
Community Hero

Re: Using Excel file as input to Soap call. Date issue uses slash separation.

Hey @MikeD1,

I'm actually almost 100% positive Vallarasu actually gave you the correct answer.

The reasons for this is that ReadyAPI! shouldn't be changing anything, it doesn't even include the functionality to change whats been read in without some effort by yourself and seeing as i'm assumimg you haven't created any event handlers, etc. to change the date format, it must be due to Excel.

Also, Excel doesn't always display the format of the value that is actually being stored, especially with non string types.
The easiest way to check that excel is the issue would be to source the date value from a notepad file instead....you'll see that whatever is written in file will be picked up by ReadyAPI!

Excel doesn't always display the value that is represented by the 'type' specified and i know there is an issue with Excel setting the type on a field AFTER the field has had a value in it (google it youll see). You might have to create a new worksheet, set the datatype to what you want and then add the values to it.

To summarise: excel doesnt necessarily display the value with the type that is set, especially if you try altering the type after a value has been stored in the field. To prove its excel, source the date value from a .txt file (that has no formatting) and youll see whatever value is sourced is what is saved in the text file.

Cheers,

Rich
if this helped answer the post, could you please mark it as 'solved'? Also if you consider whether the title of your post is relevant? Perhaps if the post is solved, it might make sense to update the Subject header field of the post to something more descriptive? This will help people when searching for problems. Ta

View solution in original post

Highlighted
New Contributor

Re: Using Excel file as input to Soap call. Date issue uses slash separation.

 @richie 

Thank you for taking you time to reply. 

 

I have investegated further, and it does seem to have something to do with Excel. It is a strange issue I think. In Excel I have formatted the Date-field (YYYY-MM-DD) which is expected, but it still shows up in as YYYY/MM/DD. 

 

Anyway in Excel, changing the column/cell from type Date to type Text and then format the (text) string as a date (YYYY-MM-DD) seems to be a workaround that works (like Vallarasu suggested), but not really the solution I was looking for as it is quite tedious changing hundreds of date (types) to string types in Excel. 

 

*EDIT*... or perhaps not so tedious using the TEXT function in Excel 🙂

 

Best regards

Michael

New Here?
Join us and watch the welcome video:
Announcements
TechCorner Leaderboard
Compete with community members in the TechCorner Challenge and get into the Leaderboard!

Rank Participant Points Earned
1 msiadak 12
2 nmrao 8
3 HimanshuTayal 3
Top Kudoed Authors