Did you forgot a
| at the end of first line?
I can see two approach at first glance:
- Build a DataTable, directly parse the text file and add values to a new row.
- Transform the text to a csv that can be read as a DataTable.
if you’re confortable with Regex handling, you can parse your text with the following pattern.
See it in action online in regex101.com
You can add row to a DataTable (or directly write into an excel file but I don’t think its optimal)
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
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
Regex_Replace_Fields.xaml (9.3 KB)
Regex_Replace_Fields.txt (74 Bytes)