- The article discusses the use of Regex in format mapping when working with files that contain escape characters like commas in CSVs or EDI messages.
- The author suggests creating a new text import format by parsing the whole line and setting special delimiters for proper mapping.
- To handle non-escaped commas, the strategy involves substituting them with an unusual symbol like § and then splitting them using that symbol.
- A calculated field is created to replace the unusual symbol with a white space to ensure proper parsing.
- Regex is utilized to find all non-escaped commas in the text for accurate formatting.
- The formula INDEX is used to find all fields in the text, with special handling for the last column to convert it to a date format.
- The article concludes by recommending testing the format mapping with 'Run' and selecting it as the default option if successful.
Registered users can view the full text for FREE!
Sign In Now!
