Home > Archive > BizTalk > microsoft.public.biztalk.general > 2004-06   Shop / Science   Privacy   

RE: Flat file - Field delimiter in element string.

From: GilesTH (GilesTH_at_discussions.microsoft.com)
Date: 06/23/04


Date: Wed, 23 Jun 2021 09:14:02 -0700

Cheers David,

Thanks for this, very quick and works

""David Downing [MSFT]"" wrote:

> I modified your schema as follows:
>
> Changed HeaderInfo to Infix.
> Set ProductInfo to Infix <CR><LF>.
> Added a wrap character of " to the description field.
>
> Everything seems to parse correctly. I'm attaching the updated schema and
> adding it to the post just in case you can't access the attachment.
>
> Dave
>
> **** Schema Below ****
>
> <?xml version="1.0" encoding="utf-16"?>
> <xs:schema xmlns="BTSExample"
> xmlns:b="http://schemas.microsoft.com/BizTalk/2003"
> elementFormDefault="unqualified" targetNamespace="BTSExample"
> xmlns:xs="http://www.w3.org/2001/XMLSchema">
> <xs:annotation>
> <xs:appinfo>
> <b:schemaInfo allow_early_termination="true"
> count_positions_by_byte="false" standard="Flat File"
> root_reference="BTSExample" default_child_order="postfix" version="1.0" />
> <schemaEditorExtension:schemaInfo namespaceAlias="b"
> extensionClass="Microsoft.BizTalk.FlatFileExtension.FlatFileExtension"
> standardName="Flat File"
> xmlns:schemaEditorExtension="http://schemas.microsoft.com/BizTalk/2003/Schem
> aEditorExtensions" />
> </xs:appinfo>
> </xs:annotation>
> <xs:element name="BTSExample">
> <xs:annotation>
> <xs:appinfo>
> <b:recordInfo structure="delimited"
> preserve_delimiter_for_empty_data="false"
> suppress_trailing_delimiters="false" sequence_number="1"
> child_order="postfix" rootTypeName="BTSExample" child_delimiter_type="hex"
> child_delimiter="0x0D 0x0A" />
> </xs:appinfo>
> </xs:annotation>
> <xs:complexType>
> <xs:sequence>
> <xs:annotation>
> <xs:appinfo>
> <b:groupInfo sequence_number="0"
> xmlns:b="http://schemas.microsoft.com/BizTalk/2003" />
> </xs:appinfo>
> </xs:annotation>
> <xs:element name="HeaderInfo">
> <xs:annotation>
> <xs:appinfo>
> <b:recordInfo sequence_number="1" structure="delimited"
> preserve_delimiter_for_empty_data="true"
> suppress_trailing_delimiters="false" child_delimiter_type="hex"
> child_delimiter="0x0D 0x0A" child_order="infix" />
> </xs:appinfo>
> </xs:annotation>
> <xs:complexType>
> <xs:sequence>
> <xs:annotation>
> <xs:appinfo>
> <b:groupInfo sequence_number="0"
> xmlns:b="http://schemas.microsoft.com/BizTalk/2003" />
> </xs:appinfo>
> </xs:annotation>
> <xs:element name="Header">
> <xs:annotation>
> <xs:appinfo>
> <b:recordInfo sequence_number="1" structure="delimited"
> preserve_delimiter_for_empty_data="true"
> suppress_trailing_delimiters="false" child_delimiter_type="char"
> child_delimiter="," child_order="infix" />
> </xs:appinfo>
> </xs:annotation>
> <xs:complexType>
> <xs:sequence>
> <xs:annotation>
> <xs:appinfo>
> <b:groupInfo sequence_number="0"
> xmlns:b="http://schemas.microsoft.com/BizTalk/2003" />
> </xs:appinfo>
> </xs:annotation>
> <xs:element name="supplierDescription">
> <xs:annotation>
> <xs:appinfo>
> <b:fieldInfo justification="left"
> sequence_number="1" />
> </xs:appinfo>
> </xs:annotation>
> <xs:simpleType>
> <xs:restriction base="xs:string">
> <xs:pattern value="[0-9a-zA-Z]{0,40}" />
> <xs:maxLength value="40" />
> <xs:minLength value="1" />
> </xs:restriction>
> </xs:simpleType>
> </xs:element>
> <xs:element name="dept">
> <xs:annotation>
> <xs:appinfo>
> <b:fieldInfo justification="left"
> sequence_number="2" />
> </xs:appinfo>
> </xs:annotation>
> <xs:simpleType>
> <xs:restriction base="xs:string">
> <xs:maxLength value="4" />
> <xs:minLength value="4" />
> </xs:restriction>
> </xs:simpleType>
> </xs:element>
> <xs:element name="supplierNumber">
> <xs:annotation>
> <xs:appinfo>
> <b:fieldInfo justification="left"
> sequence_number="3" />
> </xs:appinfo>
> </xs:annotation>
> <xs:simpleType>
> <xs:restriction base="xs:string">
> <xs:maxLength value="7" />
> <xs:minLength value="7" />
> </xs:restriction>
> </xs:simpleType>
> </xs:element>
> <xs:element name="email" type="xs:string">
> <xs:annotation>
> <xs:appinfo>
> <b:fieldInfo justification="left"
> sequence_number="4" />
> </xs:appinfo>
> </xs:annotation>
> </xs:element>
> <xs:element name="type">
> <xs:annotation>
> <xs:appinfo>
> <b:fieldInfo justification="left"
> sequence_number="5" />
> </xs:appinfo>
> </xs:annotation>
> <xs:simpleType>
> <xs:restriction base="xs:string">
> <xs:maxLength value="1" />
> <xs:minLength value="1" />
> </xs:restriction>
> </xs:simpleType>
> </xs:element>
> <xs:element name="version">
> <xs:annotation>
> <xs:appinfo>
> <b:fieldInfo justification="left"
> sequence_number="6" />
> </xs:appinfo>
> </xs:annotation>
> <xs:simpleType>
> <xs:restriction base="xs:string">
> <xs:maxLength value="6" />
> <xs:minLength value="1" />
> </xs:restriction>
> </xs:simpleType>
> </xs:element>
> </xs:sequence>
> </xs:complexType>
> </xs:element>
> <xs:element maxOccurs="unbounded" name="ProductInfo">
> <xs:annotation>
> <xs:appinfo>
> <b:recordInfo structure="delimited"
> preserve_delimiter_for_empty_data="true"
> suppress_trailing_delimiters="false" sequence_number="2"
> child_order="infix" child_delimiter_type="hex" child_delimiter="0x0D 0x0A"
> />
> </xs:appinfo>
> </xs:annotation>
> <xs:complexType>
> <xs:sequence>
> <xs:annotation>
> <xs:appinfo>
> <b:groupInfo sequence_number="0"
> xmlns:b="http://schemas.microsoft.com/BizTalk/2003" />
> </xs:appinfo>
> </xs:annotation>
> <xs:element name="Products">
> <xs:annotation>
> <xs:appinfo>
> <b:recordInfo sequence_number="1"
> structure="delimited" preserve_delimiter_for_empty_data="true"
> suppress_trailing_delimiters="false" child_delimiter_type="char"
> child_delimiter="," child_order="infix" escape_char_type="char" />
> </xs:appinfo>
> </xs:annotation>
> <xs:complexType>
> <xs:sequence>
> <xs:annotation>
> <xs:appinfo>
> <b:groupInfo sequence_number="0"
> xmlns:b="http://schemas.microsoft.com/BizTalk/2003" />
> </xs:appinfo>
> </xs:annotation>
> <xs:element name="barCode">
> <xs:annotation>
> <xs:appinfo>
> <b:fieldInfo justification="left"
> sequence_number="1" />
> </xs:appinfo>
> </xs:annotation>
> <xs:simpleType>
> <xs:restriction base="xs:string">
> <xs:pattern value="[0-9a-zA-Z]{13}" />
> </xs:restriction>
> </xs:simpleType>
> </xs:element>
> <xs:element name="productCode">
> <xs:annotation>
> <xs:appinfo>
> <b:fieldInfo justification="left"
> sequence_number="2" />
> </xs:appinfo>
> </xs:annotation>
> <xs:simpleType>
> <xs:restriction base="xs:string">
> <xs:pattern value="[0-9a-zA-Z]{1,10}" />
> </xs:restriction>
> </xs:simpleType>
> </xs:element>
> <xs:element name="currentPrice">
> <xs:annotation>
> <xs:appinfo>
> <b:fieldInfo justification="left"
> sequence_number="3" />
> </xs:appinfo>
> </xs:annotation>
> <xs:simpleType>
> <xs:restriction base="xs:string">
> <xs:pattern value="[0-9]{1,6}.[0-9]{2}" />
> </xs:restriction>
> </xs:simpleType>
> </xs:element>
> <xs:element name="description">
> <xs:annotation>
> <xs:appinfo>
> <b:fieldInfo justification="left"
> sequence_number="4" wrap_char_type="char" wrap_char=""" />
> </xs:appinfo>
> </xs:annotation>
> <xs:simpleType>
> <xs:restriction base="xs:string">
> <xs:pattern value=".{1,40}" />
> </xs:restriction>
> </xs:simpleType>
> </xs:element>
> <xs:element name="group">
> <xs:annotation>
> <xs:appinfo>
> <b:fieldInfo sequence_number="5"
> justification="left" />
> </xs:appinfo>
> </xs:annotation>
> <xs:simpleType>
> <xs:restriction base="xs:string">
> <xs:pattern value="[a-zA-Z]{1,10}" />
> </xs:restriction>
> </xs:simpleType>
> </xs:element>
> </xs:sequence>
> </xs:complexType>
> </xs:element>
> </xs:sequence>
> </xs:complexType>
> </xs:element>
> </xs:sequence>
> </xs:complexType>
> </xs:element>
> </xs:sequence>
> </xs:complexType>
> </xs:element>
> </xs:schema>
>
> --------------------
> >>Thread-Topic: Flat file - Field delimiter in element string.
> >>thread-index: AcRZIrHDpjmpbDd0Qx25M/9JM27wUA==
> >>X-WBNR-Posting-Host: 212.134.116.130
> >>From: "=?Utf-8?B?R2lsZXNUSA==?=" <GilesTH@discussions.microsoft.com>
> >>Subject: Flat file - Field delimiter in element string.
> >>Date: Wed, 23 Jun 2021 06:05:01 -0700
> >>Lines: 243
> >>Message-ID: <DFB414D7-4337-4F03-A48F-ECCEDD3EE57E@microsoft.com>
> >>MIME-Version: 1.0
> >>Content-Type: text/plain;
> >> charset="Utf-8"
> >>Content-Transfer-Encoding: 7bit
> >>X-Newsreader: Microsoft CDO for Windows 2000
> >>Content-Class: urn:content-classes:message
> >>Importance: normal
> >>Priority: normal
> >>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> >>Newsgroups: microsoft.public.biztalk.general
> >>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 127.0.0.1
> >>Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
> >>Xref: cpmsftngxa10.phx.gbl microsoft.public.biztalk.general:16536
> >>X-Tomcat-NG: microsoft.public.biztalk.general
> >>
> >>Help needed!
> >>
> >>I am trying to create a schema for a 'CSV' file, but one of the fields
> can contain a comma as part of the string.
> >>
> >>If a comma is present, the whole string will be enclosed within quotation
> marks.
> >>
> >>Example data - The first row is a file header which can be followed by
> multiple product information rows.
> >>
> >>FileName,Dept,SuppNum,emailAddress,C,1
> >>EAN13 Barcode,ItemNumber,119.00,24pc conform,Group
> >>EAN13 Barcode,ItemNumber,4.95,"Yoghurt spoons, set of 2",Group
> >>EAN13 Barcode,ItemNumber,6.95,Cake server,Group
> >>
> >>XSD below.
> >>
> >> <?xml version="1.0" encoding="utf-16" ?>
> >>- <xs:schema xmlns:ns0="http://B2B.BizTalk.PropertySchema.PropertySchema"
> xmlns="BTSExample" xmlns:b="http://schemas.microsoft.com/BizTalk/2003"
> elementFormDefault="unqualified" targetNamespace="BTSExample"
> xmlns:xs="http://www.w3.org/2001/XMLSchema">
> >>- <xs:annotation>
> >>- <xs:appinfo>
> >> <b:schemaInfo allow_early_termination="true"
> count_positions_by_byte="false" standard="Flat File"
> root_reference="BTSExample" default_child_order="postfix" version="1.0" />
> >> <schemaEditorExtension:schemaInfo namespaceAlias="b"
> extensionClass="Microsoft.BizTalk.FlatFileExtension.FlatFileExtension"
> standardName="Flat File"
> xmlns:schemaEditorExtension="http://schemas.microsoft.com/BizTalk/2003/Schem
> aEditorExtensions" />
> >>- <b:imports>
> >> <b:namespace prefix="ns0"
> uri="http://B2B.BizTalk.PropertySchema.PropertySchema"
> location="..\..\propertyschema.xsd" />
> >> </b:imports>
> >> </xs:appinfo>
> >> </xs:annotation>
> >>- <xs:element name="BTSExample">
> >>- <xs:annotation>
> >>- <xs:appinfo>
> >> <b:recordInfo structure="delimited"
> preserve_delimiter_for_empty_data="false"
> suppress_trailing_delimiters="false" sequence_number="1"
> child_order="postfix" rootTypeName="BTSExample" child_delimiter_type="hex"
> child_delimiter="0x0D 0x0A" />
> >>- <b:properties>
> >> <b:property name="ns0:supplierNumber"
> xpath="/*[local-name()='BTSExample' and
> namespace-uri()='BTSExample']/*[local-name()='HeaderInfo' and
> namespace-uri()='']/*[local-name()='Header' and
> namespace-uri()='']/*[local-name()='supplierNumber' and
> namespace-uri()='']" />
> >> </b:properties>
> >> </xs:appinfo>
> >> </xs:annotation>
> >>- <xs:complexType>
> >>- <xs:sequence>
> >>- <xs:annotation>
> >>- <xs:appinfo>
> >> <b:groupInfo sequence_number="0"
> xmlns:b="http://schemas.microsoft.com/BizTalk/2003" />
> >> </xs:appinfo>
> >> </xs:annotation>
> >>- <xs:element name="HeaderInfo">
> >>- <xs:annotation>
> >>- <xs:appinfo>
> >> <b:recordInfo sequence_number="1" structure="delimited"
> preserve_delimiter_for_empty_data="true"
> suppress_trailing_delimiters="false" child_delimiter_type="hex"
> child_delimiter="0x0D 0x0A" child_order="postfix" />
> >> </xs:appinfo>
> >> </xs:annotation>
> >>- <xs:complexType>
> >>- <xs:sequence>
> >>- <xs:annotation>
> >>- <xs:appinfo>
> >> <b:groupInfo sequence_number="0"
> xmlns:b="http://schemas.microsoft.com/BizTalk/2003" />
> >> </xs:appinfo>
> >> </xs:annotation>
> >>- <xs:element name="Header">
> >>- <xs:annotation>
> >>- <xs:appinfo>
> >> <b:recordInfo sequence_number="1" structure="delimited"
> preserve_delimiter_for_empty_data="true"
> suppress_trailing_delimiters="false" child_delimiter_type="char"
> child_delimiter="," child_order="infix" />
> >> </xs:appinfo>
> >> </xs:annotation>
> >>- <xs:complexType>
> >>- <xs:sequence>
> >>- <xs:annotation>
> >>- <xs:appinfo>
> >> <b:groupInfo sequence_number="0"
> xmlns:b="http://schemas.microsoft.com/BizTalk/2003" />
> >> </xs:appinfo>
> >> </xs:annotation>
> >>- <xs:element name="supplierDescription">
> >>- <xs:annotation>
> >>- <xs:appinfo>
> >> <b:fieldInfo justification="left" sequence_number="1" />
> >> </xs:appinfo>
> >> </xs:annotation>
> >>- <xs:simpleType>
> >>- <xs:restriction base="xs:string">
> >> <xs:pattern value="[0-9a-zA-Z]{0,40}" />
> >> <xs:maxLength value="40" />
> >> <xs:minLength value="1" />
> >> </xs:restriction>
> >> </xs:simpleType>
> >> </xs:element>
> >>- <xs:element name="dept">
> >>- <xs:annotation>
> >>- <xs:appinfo>
> >> <b:fieldInfo justification="left" sequence_number="2" />
> >> </xs:appinfo>
> >> </xs:annotation>
> >>- <xs:simpleType>
> >>- <xs:restriction base="xs:string">
> >> <xs:maxLength value="4" />
> >> <xs:minLength value="4" />
> >> </xs:restriction>
> >> </xs:simpleType>
> >> </xs:element>
> >>- <xs:element name="supplierNumber">
> >>- <xs:annotation>
> >>- <xs:appinfo>
> >> <b:fieldInfo justification="left" sequence_number="3" />
> >> </xs:appinfo>
> >> </xs:annotation>
> >>- <xs:simpleType>
> >>- <xs:restriction base="xs:string">
> >> <xs:maxLength value="7" />
> >> <xs:minLength value="7" />
> >> </xs:restriction>
> >> </xs:simpleType>
> >> </xs:element>
> >>- <xs:element name="email" type="xs:string">
> >>- <xs:annotation>
> >>- <xs:appinfo>
> >> <b:fieldInfo justification="left" sequence_number="4" />
> >> </xs:appinfo>
> >> </xs:annotation>
> >> </xs:element>
> >>- <xs:element name="type">
> >>- <xs:annotation>
> >>- <xs:appinfo>
> >> <b:fieldInfo justification="left" sequence_number="5" />
> >> </xs:appinfo>
> >> </xs:annotation>
> >>- <xs:simpleType>
> >>- <xs:restriction base="xs:string">
> >> <xs:maxLength value="1" />
> >> <xs:minLength value="1" />
> >> </xs:restriction>
> >> </xs:simpleType>
> >> </xs:element>
> >>- <xs:element name="version">
> >>- <xs:annotation>
> >>- <xs:appinfo>
> >> <b:fieldInfo justification="left" sequence_number="6" />
> >> </xs:appinfo>
> >> </xs:annotation>
> >>- <xs:simpleType>
> >>- <xs:restriction base="xs:string">
> >> <xs:maxLength value="6" />
> >> <xs:minLength value="1" />
> >> </xs:restriction>
> >> </xs:simpleType>
> >> </xs:element>
> >> </xs:sequence>
> >> </xs:complexType>
> >> </xs:element>
> >>- <xs:element maxOccurs="unbounded" name="ProductInfo">
> >>- <xs:annotation>
> >>- <xs:appinfo>
> >> <b:recordInfo structure="delimited"
> preserve_delimiter_for_empty_data="true"
> suppress_trailing_delimiters="false" sequence_number="2"
> child_order="prefix" />
> >> </xs:appinfo>
> >> </xs:annotation>
> >>- <xs:complexType>
> >>- <xs:sequence>
> >>- <xs:annotation>
> >>- <xs:appinfo>
> >> <b:groupInfo sequence_number="0"
> xmlns:b="http://schemas.microsoft.com/BizTalk/2003" />
> >> </xs:appinfo>
> >> </xs:annotation>
> >>- <xs:element name="Products">
> >>- <xs:annotation>
> >>- <xs:appinfo>
> >> <b:recordInfo sequence_number="1" structure="delimited"
> preserve_delimiter_for_empty_data="true"
> suppress_trailing_delimiters="false" child_delimiter_type="char"
> child_delimiter="," child_order="infix" />
> >> </xs:appinfo>
> >> </xs:annotation>
> >>- <xs:complexType>
> >>- <xs:sequence>
> >>- <xs:annotation>
> >>- <xs:appinfo>
> >> <b:groupInfo sequence_number="0"
> xmlns:b="http://schemas.microsoft.com/BizTalk/2003" />
> >> </xs:appinfo>
> >> </xs:annotation>
> >>- <xs:element name="barCode">
> >>- <xs:annotation>
> >>- <xs:appinfo>
> >> <b:fieldInfo justification="left" sequence_number="1" />
> >> </xs:appinfo>
> >> </xs:annotation>
> >>- <xs:simpleType>
> >>- <xs:restriction base="xs:string">
> >> <xs:pattern value="[0-9a-zA-Z]{13}" />
> >> </xs:restriction>
> >> </xs:simpleType>
> >> </xs:element>
> >>- <xs:element name="productCode">
> >>- <xs:annotation>
> >>- <xs:appinfo>
> >> <b:fieldInfo justification="left" sequence_number="2" />
> >> </xs:appinfo>
> >> </xs:annotation>
> >>- <xs:simpleType>
> >>- <xs:restriction base="xs:string">
> >> <xs:pattern value="[0-9a-zA-Z]{1,10}" />
> >> </xs:restriction>
> >> </xs:simpleType>
> >> </xs:element>
> >>- <xs:element name="currentPrice">
> >>- <xs:annotation>
> >>- <xs:appinfo>
> >> <b:fieldInfo justification="left" sequence_number="3" />
> >> </xs:appinfo>
> >> </xs:annotation>
> >>- <xs:simpleType>
> >>- <xs:restriction base="xs:string">
> >> <xs:pattern value="[0-9]{1,6}.[0-9]{2}" />
> >> </xs:restriction>
> >> </xs:simpleType>
> >> </xs:element>
> >>- <xs:element name="description">
> >>- <xs:annotation>
> >>- <xs:appinfo>
> >> <b:fieldInfo justification="left" sequence_number="4" />
> >> </xs:appinfo>
> >> </xs:annotation>
> >>- <xs:simpleType>
> >>- <xs:restriction base="xs:string">
> >> <xs:pattern value=".{1,40}" />
> >> </xs:restriction>
> >> </xs:simpleType>
> >> </xs:element>
> >>- <xs:element name="group">
> >>- <xs:annotation>
> >>- <xs:appinfo>
> >> <b:fieldInfo sequence_number="5" justification="left" />
> >> </xs:appinfo>
> >> </xs:annotation>
> >>- <xs:simpleType>
> >>- <xs:restriction base="xs:string">
> >> <xs:pattern value="[a-zA-Z]{1,10}" />
> >> </xs:restriction>
> >> </xs:simpleType>
> >> </xs:element>
> >> </xs:sequence>
> >> </xs:complexType>
> >> </xs:element>
> >> </xs:sequence>
> >> </xs:complexType>
> >> </xs:element>
> >> </xs:sequence>
> >> </xs:complexType>
> >> </xs:element>
> >> </xs:sequence>
> >> </xs:complexType>
> >> </xs:element>
> >> </xs:schema>
> >>
>
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> EBusiness Server Team


Home > Archive > BizTalk > microsoft.public.biztalk.general > 2004-06   Shop / Science   Privacy   
This document was last modified: 06/24/04 02:15 CEST
We are proud to be hosted by
9 Net Avenue Deutschland