wilsonric wrote:I'm trying to understand the purpose of the Quote Char field in a TI process as I have deleted the " character to avoid errors loading some source data containing ". This has worked - no error log was created, but I'm worried I may be creating another problem by doing this.
I have little influence over the source data as it is managed by the Micros point of sale team. They want to see Pizza Hawaii 11" and Pizza Veg 11" on the sales docket.
The quote char tells TI which characters surround a string to indicate that it's a
complete string. This is sometimes needed when characters which are otherwise delimiters can be embedded in your fields.
Suppose that your source is a comma delimited file. The first record might be:
No problem, TI will just split that up by the commas.
But suppose that the next line is:
Code: Select all
100,Product A, Deluxe Edition,Jan 10
TI (or any other ETL program) could well be confused when it hits the comma between
A and
Deluxe; is that a field separator, or is it just punctuation? How would the upload program know?
So instead the export program will probably output the row as (say):
Code: Select all
100,"Product A, Deluxe Edition",Jan 10
When you specify the quote character, you're telling TI "take anything that is between a pair of those characters, and treat it as a single field". If the character was set to double quotes in this example, TI will see the commas only between the 100 and the product name, and between the product name and the month. It will ignore the comma in the product name itself, because it sits inside the quote characters.
In addition, TI will skip the quote characters when it reads the field. It will see the field as
Product A, Deluxe Edition without quotes on either end.
However when the quote character is a legitimate part of a field content (as is the case with your data) you shouldn't use it as the quote character (as you've found). You either clear it in the GUI, or set a different character (or no character) via the DatasourceASCIIQuoteCharacter variable in the Prolog.
Provided that there aren't any delimiters that are likely to be embedded in your data fields, that won't be a problem. If there will be, you need to use a different quote character. (Though this will also involve modifying the source data.) Indeed in such cases you may be better off switching from a character delimited format to a fixed width format, even though the latter is a bit of a pain to set up.