Written June 2025
CSV2XL provides two mechanisms whereby the format applied to a column of data can be specified by identifying the pattern of the column title. Regular expressions (regex) are used for the pattern recognition.
Format Column As Text
A common problem in Excel conversions is that Excel treats all numeric-looking data as numbers. That is fine most of the time, but not when the numeric-looking data are part numbers, or postal codes, or other "text data that just happens to be numbers." Using the ColTitleTextRegex directive, CSV2XL basically says "If the column title looks like the specified regular expression, then the data in the column should be treated as text rather than numbers."
Example1
ColTitleTextRegex=zip|(part *(no|num|#))|phone|sku
This expression would identify any column title containing "zip", "phone", or "part" followed by optional space and then "no","num", or "#." The data in the column would therefore be treated as text.
Matching is case insensitive, not anchored. Use ^ and $ for start/end anchoring.
Example2
ColTitleTextRegex supports directives from 1 to 9 to make it easier to edit a long pattern list in the ini file. The maximum combined length is 500 characters, but you can break it up, e.g.
ColTitleTextRegex=zip|(part *(no|num|#))|phone|sku|invno|wo|code|po
ColTitleTextRegex2=acct *(no|num|#)|status|icd|
ColTitleTextRegex3=diag|m\d|diag\d
ColTitleTextRegex4=street #|house #
ColTitleTextRegex5=Dept#|^id|^prov|^loc|^pos
...
ColTitleTextRegex9=Numero|Cantidad
Note that it doesn't matter whether each line ends with a pipe (|) or not; they will be concatenated with pipe separators as required.here.
Format Column As Anything
The directive described above, ColTitleTextRegex, was created near the beginning of CSV2XL development. As time went by, it became clear that CSV2XL needed a more general and comprehensive "format by recognition" directive. Users liked the "recognize" part but wanted more flexibility with the "format" part. This led to the creation of the ColTitleFormatRegex directive, which works exactly as above but provides the ability to format the column as text or any other supported Excel format. Note that "text" is simply one of the available number formats, making the original ColTitleTextRegex directive completely redundant; it continues to work only for backward compatibility.
CSV2XL supports up to four format directives to associate formats with column titles. Unlike the otherwise similar ColTitleTextRegex directives, these are each independent, associating a pattern with a number format:
ColTitleFormatRegex1=format,pattern
ColTitleFormatRegex2=format,pattern
ColTitleFormatRegex3=format,pattern
ColTitleFormatRegex4=format,pattern
As with the NumFormat directive, the format may be either a predefined NUMFMT_xxx symbol or a mask; see the //XL,SetCol directive for details. Note that unlike most other directives, these take two arguments, separated by a comma. If either the format or the pattern contains commas, you must enclose it in quotes, e.g.:
ColTitleFormatRegex1="##,###.00[red]##,###.00",Amount|Total|Price|Value
