Solved

DD/MM/YY date issues in CSV file

geoffp

Hi all. 

Is there a default date-culture setting in RPA? I have CSV files to process, but they include dd/mm/yy dates which are being converted automatically to US date format when opening the file, so dates up to 12th of the month convert to a valid (but incorrect) mm/dd/yyyy date, while the remaining dates are left as text in the original format.

 

I tried using a date-culture formula to clean it up, but it fails with the two different date formats in the one column. 

 

I tried importing as data or a list to set the column as text, but the files have 7 header rows with only two columns, so the import ignores the remaining columns. 

 

If I open the files in Excel manually (not using RPA), the dates all show correctly in AU format. I feel like I'm missing something obvious here - can anyone tell me where I'm going wrong?

Thanks. 

icon

Best answer by KyleDierking 29 May 2020, 15:29

View original

2 replies

KyleDierking

When you turn Culture Support ON in your botflow settings this will allow you to set the culture of individual variables. Also it will open up access to the "Convert Variable" and the "Convert Field" actions which should help you get your data into the right format.


 



 

geoffp

Thanks Kyle. I had already enabled that setting, but the problem I found was that although I could then use the ConvertDateCulture formula, it didn't give uniform results for the whole column. I assumed that was because almost half the rows had been converted to US date format, while the remainder were still AU date formatted text.


 


In this case, I worked around the issue with IF statements to reformat the dates depending on whether they ended in /yy (original) or not (the incorrectly formatted fields had changed to /yyyy), and then switched them all to yyyy/mm/dd. That seems a bit clunky to me but it worked. 

Reply


View our Community Site Map

Nintex Community Sitemap
Terms & ConditionsCookie settings