I have an extracted CSV file from a system (which I have no control over)
The CS data extracted has commas (,) as delimiters, however, they aren’t enclosed in quotations like “,”.
The data I extract has a free text column, and the sentences there also have commas (,) in them. Because of this, when I open it in Excel, the data and column headers are mostly misaligned.
The other columns do not have commas (,) it is only this one column.
However, thankfully the two columns between the ‘free text’ column, has fixed structures (one is a datetime format, the other is a custom ID structure where it has 2 alphabets, and a 3 numbers - eg:AC123).
So the structure looks like this:
ID, Text, Date
AC123, Text, dd/mm/yyyy hh:mm:ss
I am trying to figure out a workflow to get UI Path to read the csv file, determine the two columns between the ‘free text’ column, and place quotation marks around the commas, and then write it over the csv file (clearing the old data, and appending the newly corrected data). And I want my workflow to do this for every data row.
The data structure should look like this after correctioin:
ID, Text, Date
AC123"," Text"," dd/mm/yyyy hh:mm:ss
This is so that it can be passed on to the next workflow through Excel (fixed process)
Can anyone help write the specific activities I should use? I know RegEX would help with identifying the column datas between the text data, but how do I make it so it finds the position based on the RegEx word, and get the commas (,), either before, and one for after?
(I’m quite new to RPA so a detailed explanation of how this is performed will help greatly)
Status,ID,Title,Open Date,Close Date
Open,PO102,THIS IS WHERE THE,TEXT WILL BE,04/25/2020 01:25:10 AM,04/25/2020 03:43:50 PM
Open,PO293,THIS IS,WHERE THE,TEXT WILL BE,04/25/2020 12:22:40 AM,04/25/2020 04:43:22 PM
Open,PO029,SOMETIMES HAS NO COMMAS,04/25/2020 03:08:00 AM,04/25/2020 05:08:23 PM
Open,PO502,SOMETIMES,HAS,COMMAS,04/25/2020 03:00:00 PM,04/25/2020 05:19:25 PM