I trying to find a solution, of this process like read text from txt file and write it on excel. and work.txt (74 Bytes)
where i getting output like:
same as in text file, I want a output like column wise like:
name | Id | city entered the value as below on the excel.
You can add row to a DataTable (or directly write into an excel file but I don’t think its optimal)
To CSV
Replace headers and separators with string replace method or you can still use Regex like the following:
You’ll find an example in attachment. Here is a short explanation. Please update the path variables before running it
Raw = "Your text that you can read with Read Text File activity"
Pattern = \|Name\|(?<name>[^\|]+)\|\s*?\|id\|(?<id>[^\|]+)\|\s*?\|city\|(?<city>[^\|]+)\|
CsvText = System.Text.RegularExpressions.Regex.Replace(Raw, Pattern, "$1;$2;$3")
CsvHeader = "Name;ID;City"
CsvTextWithHeader = CsvHeader & vbCrLf & CsvPath
Then:
Write Text File with CsvText (or CsvTextWithHeader)
Read CSV with produced text file
Read Range (workbook flavor) to write produced DataTable into an .xlsx file