What is the purpose of "Quote Char" in TI data source?

Post Reply
User avatar
wilsonric
Posts: 15
Joined: Mon Jan 10, 2011 4:36 am
OLAP Product: TM1
Version: 2.0.9
Excel Version: 365 MSO 16-0-13127
Location: Sydney, Australia

What is the purpose of "Quote Char" in TI data source?

Post by wilsonric »

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.
Regards,
Richard
User avatar
Alan Kirk
Site Admin
Posts: 6606
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: PA2.0.9.18 Classic NO PAW!
Excel Version: 2013 and Office 365
Location: Sydney, Australia
Contact:

Re: What is the purpose of "Quote Char" in TI data source?

Post by Alan Kirk »

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:

Code: Select all

100,Product A,Jan 10
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.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
Post Reply