- This topic has 9 replies, 1 voice, and was last updated 8 years, 4 months ago by
.
-
Posts
-
-
Hello
[b:af137b1d11]I have a intresting problem with a positional flat file.[/b:af137b1d11]
I have a normal file and I created schema using 2006 bts flat file schema wizard.
If I have 1 row in text file it reads it nicely and saves to DB. If I have more than 1 row in text file it appends rest of the data in file to last field.
Example :
John Doe Arizona
Jill Doe Arizonain message file like this would go :
firstname = \”John\” Lastname=\”Doe\” City = \”Arizona#xD; Jill;Doe;Arizona\”Seems like parser does not recognize Line break
Values in schema :
Default Child delimeter 0x0D 0x0A
Default Child Delimeter type Hexadecimal
Default Child Order PostfixIs there something that must be done in schema creation wizard differently or are there some other values to set?
Any help would be appreciated!
-
Hi.
Here’s sample output from wizard.
[code:1:235624bd60]
<?xml version=\"1.0\" encoding=\"utf-16\" ?>
– <xs:schema xmlns:b=\"http://schemas.microsoft.com/BizTalk/2003\" xmlns=\"http://Application1\" targetNamespace=\"http://Application1\" xmlns:xs=\"http://www.w3.org/2001/XMLSchema\">
– <xs:annotation>
– <xs:appinfo>
<schemaEditorExtension:schemaInfo namespaceAlias=\"b\" extensionClass=\"Microsoft.BizTalk.FlatFileExtension.FlatFileExtension\" standardName=\"Flat File\" xmlns:schemaEditorExtension=\"http://schemas.microsoft.com/BizTalk/2003/SchemaEditorExtensions\" />
<b:schemaInfo standard=\"Flat File\" codepage=\"65001\" default_pad_char=\"\" count_positions_by_byte=\"false\" parser_optimization=\"speed\" lookahead_depth=\"3\" suppress_empty_nodes=\"false\" generate_empty_nodes=\"true\" allow_early_termination=\"true\" early_terminate_optional_fields=\"false\" allow_message_breakup_of_infix_root=\"false\" compile_parse_tables=\"false\" root_reference=\"LoadingList\" child_delimiter_type=\"hex\" default_child_delimiter=\"0x0D 0x0A\" culture=\"fi-FI\" case=\"upper\" />
</xs:appinfo>
</xs:annotation>
– <xs:element name=\"List\">
– <xs:annotation>
– <xs:appinfo>
<b:recordInfo structure=\"positional\" sequence_number=\"1\" preserve_delimiter_for_empty_data=\"true\" suppress_trailing_delimiters=\"false\" />
</xs:appinfo>
</xs:annotation>
– <xs:complexType>
– <xs:sequence minOccurs=\"1\">
– <xs:annotation>
– <xs:appinfo>
<groupInfo sequence_number=\"0\" xmlns=\"http://schemas.microsoft.com/BizTalk/2003\" />
</xs:appinfo>
</xs:annotation>
– <xs:element default=\"0\" name=\"Segment_Id\" type=\"xs:string\">
– <xs:annotation>
– <xs:appinfo>
<b:fieldInfo justification=\"left\" pos_offset=\"0\" pos_length=\"4\" sequence_number=\"1\" />
</xs:appinfo>
</xs:annotation>
</xs:element>
– <xs:element default=\"0\" name=\"Segment_Version\" type=\"xs:string\">
– <xs:annotation>
– <xs:appinfo>
<b:fieldInfo justification=\"left\" pos_offset=\"0\" pos_length=\"2\" sequence_number=\"2\" />
</xs:appinfo>
</xs:annotation>
</xs:element>
– <xs:element default=\"0\" name=\"data1\" type=\"xs:string\">
– <xs:annotation>
– <xs:appinfo>
<b:fieldInfo justification=\"left\" pos_offset=\"0\" pos_length=\"17\" sequence_number=\"3\" />
</xs:appinfo>
</xs:annotation>
</xs:element>
– <xs:element default=\"0\" name=\"data2\" type=\"xs:string\">
– <xs:annotation>
– <xs:appinfo>
<b:fieldInfo justification=\"left\" pos_offset=\"0\" pos_length=\"18\" sequence_number=\"4\" />
</xs:appinfo>
</xs:annotation>
</xs:element>
– <xs:element default=\"0\" name=\"data3\" type=\"xs:int\">
– <xs:annotation>
– <xs:appinfo>
<b:fieldInfo justification=\"left\" pos_offset=\"0\" pos_length=\"5\" sequence_number=\"5\" />
</xs:appinfo>
</xs:annotation>
</xs:element>
– <xs:element default=\"0\" name=\"data4\" type=\"xs:string\">
– <xs:annotation>
– <xs:appinfo>
<b:fieldInfo justification=\"left\" pos_offset=\"0\" pos_length=\"6\" sequence_number=\"6\" />
</xs:appinfo>
</xs:annotation>
</xs:element>
– <xs:element default=\"0\" name=\"data5\" type=\"xs:string\">
– <xs:annotation>
– <xs:appinfo>
<b:fieldInfo justification=\"left\" pos_offset=\"0\" pos_length=\"7\" sequence_number=\"7\" />
</xs:appinfo>
</xs:annotation>
</xs:element>
– <xs:element default=\"0\" name=\"data6\" type=\"xs:string\">
– <xs:annotation>
– <xs:appinfo>
<b:fieldInfo justification=\"left\" pos_offset=\"0\" pos_length=\"18\" sequence_number=\"8\" />
</xs:appinfo>
</xs:annotation>
</xs:element>
– <xs:element default=\"20060101\" name=\"date1\" type=\"xs:string\">
– <xs:annotation>
– <xs:appinfo>
<b:fieldInfo justification=\"left\" pos_offset=\"0\" pos_length=\"8\" sequence_number=\"9\" />
</xs:appinfo>
</xs:annotation>
</xs:element>
– <xs:element default=\"00:00:00\" name=\"date2\" type=\"xs:string\">
– <xs:annotation>
– <xs:appinfo>
<b:fieldInfo justification=\"left\" pos_offset=\"0\" pos_length=\"8\" sequence_number=\"10\" />
</xs:appinfo>
</xs:annotation>
</xs:element>
– <xs:element default=\"0\" name=\"data8\" type=\"xs:string\">
– <xs:annotation>
– <xs:appinfo>
<b:fieldInfo justification=\"left\" pos_offset=\"0\" pos_length=\"30\" sequence_number=\"11\" />
</xs:appinfo>
</xs:annotation>
</xs:element>
– <xs:element default=\"0\" name=\"data9\" type=\"xs:string\">
– <xs:annotation>
– <xs:appinfo>
<b:fieldInfo justification=\"left\" pos_offset=\"0\" pos_length=\"2\" sequence_number=\"12\" />
</xs:appinfo>
</xs:annotation>
</xs:element>
– <xs:element default=\"0\" name=\"data10\" type=\"xs:string\">
– <xs:annotation>
– <xs:appinfo>
<b:fieldInfo justification=\"left\" pos_offset=\"0\" pos_length=\"3\" sequence_number=\"13\" />
</xs:appinfo>
</xs:annotation>
</xs:element>
– <xs:element default=\"0\" name=\"data11\" type=\"xs:string\">
– <xs:annotation>
– <xs:appinfo>
<b:fieldInfo justification=\"left\" pos_offset=\"0\" pos_length=\"30\" sequence_number=\"14\" />
</xs:appinfo>
</xs:annotation>
</xs:element>
– <xs:element default=\"0\" name=\"data12\" type=\"xs:string\">
– <xs:annotation>
– <xs:appinfo>
<b:fieldInfo justification=\"left\" pos_offset=\"0\" pos_length=\"2\" sequence_number=\"15\" />
</xs:appinfo>
</xs:annotation>
</xs:element>
– <xs:element default=\"0\" name=\"data13\" type=\"xs:string\">
– <xs:annotation>
– <xs:appinfo>
<b:fieldInfo justification=\"left\" pos_offset=\"0\" pos_length=\"3\" sequence_number=\"16\" />
</xs:appinfo>
</xs:annotation>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>[/code:1:235624bd60][/quote]
-
Hello. This problem is now solved, Thank you very much for assistance.
[b:56e43e2e04]Now the trailing row where are some data that I do not need causes error.[/b:56e43e2e04] without trailer file goes ok when footerschema declaration is removed from pieline. When foorter in file and footer schema declared in pipeline orchestration crashes into it. I have defined footer schema for this and assigned it in pipeline. There is also a header to which I defined header schema and assigned it also into pipeline and it works fine. This trailing footer cases now a problem. Does anyone have idea what should be done.
row is 48 marks long and I don’t need this data
Here’s trailer row :
DATA01SSAS-I AAA DATASS2102200614:02:10 12
and schema :
[code:1:56e43e2e04]
<?xml version=\"1.0\" encoding=\"utf-16\" ?>
– <xs:schema xmlns:b=\"http://schemas.microsoft.com/BizTalk/2003\" xmlns=\"http://Jn.Carport.Mazda.sch_LoadingList_Footer\" targetNamespace=\"http://sch_List_Footer\" xmlns:xs=\"http://www.w3.org/2001/XMLSchema\">
– <xs:annotation>
– <xs:appinfo>
<schemaEditorExtension:schemaInfo namespaceAlias=\"b\" extensionClass=\"Microsoft.BizTalk.FlatFileExtension.FlatFileExtension\" standardName=\"Flat File\" xmlns:schemaEditorExtension=\"http://schemas.microsoft.com/BizTalk/2003/SchemaEditorExtensions\" />
<b:schemaInfo standard=\"Flat File\" codepage=\"1252\" default_pad_char=\"\" count_positions_by_byte=\"false\" parser_optimization=\"speed\" lookahead_depth=\"3\" suppress_empty_nodes=\"false\" generate_empty_nodes=\"true\" allow_early_termination=\"false\" early_terminate_optional_fields=\"false\" allow_message_breakup_of_infix_root=\"false\" compile_parse_tables=\"false\" root_reference=\"Record\" />
</xs:appinfo>
</xs:annotation>
– <xs:element name=\"Record\">
– <xs:annotation>
– <xs:appinfo>
<recordInfo structure=\"delimited\" preserve_delimiter_for_empty_data=\"true\" suppress_trailing_delimiters=\"false\" sequence_number=\"1\" child_delimiter_type=\"hex\" child_delimiter=\"0x0D 0x0A\" child_order=\"default\" xmlns=\"http://schemas.microsoft.com/BizTalk/2003\" />
</xs:appinfo>
</xs:annotation>
– <xs:complexType>
– <xs:sequence>
– <xs:annotation>
– <xs:appinfo>
<groupInfo sequence_number=\"0\" xmlns=\"http://schemas.microsoft.com/BizTalk/2003\" />
</xs:appinfo>
</xs:annotation>
– <xs:element name=\"Root\">
– <xs:annotation>
– <xs:appinfo>
<b:recordInfo structure=\"positional\" preserve_delimiter_for_empty_data=\"true\" suppress_trailing_delimiters=\"false\" sequence_number=\"1\" />
</xs:appinfo>
</xs:annotation>
– <xs:complexType>
– <xs:sequence>
– <xs:annotation>
– <xs:appinfo>
<groupInfo sequence_number=\"0\" xmlns=\"http://schemas.microsoft.com/BizTalk/2003\" />
</xs:appinfo>
</xs:annotation>
– <xs:element name=\"Root_Child1\" type=\"xs:string\">
– <xs:annotation>
– <xs:appinfo>
<b:fieldInfo justification=\"left\" pos_offset=\"0\" pos_length=\"4\" sequence_number=\"1\" />
</xs:appinfo>
</xs:annotation>
</xs:element>
– <xs:element name=\"Root_Child2\" type=\"xs:string\">
– <xs:annotation>
– <xs:appinfo>
<b:fieldInfo justification=\"left\" pos_offset=\"0\" pos_length=\"44\" sequence_number=\"2\" />
</xs:appinfo>
</xs:annotation>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>
[/code:1:56e43e2e04]-
Hello
How can I define in map to exclude this last line when reading file?
\\R
[quote:488cffc3c8=\”greg.forsythe\”]The parser starts at the top of the file, so it finds the header and then your List records.
The problem is it cannot differentiate the trailer record from the List records.
One solution is to use the Tag Identifier property on the List record. Is there some identifying data in each List record that you do not need that is different from the header and trailer.
e.g.
Hfield1field2field3
Lfield1field2field3field4field5field6
Lfield1field2field3field4field5field6
Lfield1field2field3field4field5field6
TLfield1In a file like this you can declare the Tag Identifier on the header to be H , the list records to be L and the trailer to be T.
With delimited data the Tag Identifier must always be at the start of the record, but with positional data you can also specify an offset.
If you have no data that can be used as a Tag Identifier then do not specify a trailer and you can use a map to exclude the last line of your List.[/quote:488cffc3c8]
-
-
I tend not to use the Flat file wizard, probably too old to learn new tricks.
The output of the wizard for you example file should look like this
[code:1:30e05585db]<Schema>
Root (delimited, postfix, hex, 0x0D 0x0A)
Record ( maxOccurs=unbounded, positional)
FirstName (offset 0, length 5)
LastName (offset 0, length 4)
City(offset 0, length 8)[/code:1:30e05585db]
If there is a discrepancy you can edit the schema without running the wizard again.What does the wizard actually produce?
-
It looks like you have only one record called List.
You need to add a new root node.
right click on <schema> -> Insert Schema Node -> Child Record
Set to delimited, postfix, hex, 0x0D 0x0ADrag the List node and place as child node of this new root
Change List to maxOccurs = unbounded
-
The parser starts at the top of the file, so it finds the header and then your List records.
The problem is it cannot differentiate the trailer record from the List records.
One solution is to use the Tag Identifier property on the List record. Is there some identifying data in each List record that you do not need that is different from the header and trailer.
e.g.
Hfield1field2field3
Lfield1field2field3field4field5field6
Lfield1field2field3field4field5field6
Lfield1field2field3field4field5field6
TLfield1In a file like this you can declare the Tag Identifier on the header to be H , the list records to be L and the trailer to be T.
With delimited data the Tag Identifier must always be at the start of the record, but with positional data you can also specify an offset.
If you have no data that can be used as a Tag Identifier then do not specify a trailer and you can use a map to exclude the last line of your List.
-
I tried with functoids but could get the desired result.
This is a custom Xslt that shows how to filter the last record[code:1:4407151d2d]<xsl:stylesheet xmlns:xsl=\"http://www.w3.org/1999/XSL/Transform\"
xmlns:msxsl=\"urn:schemas-microsoft-com:xslt\"
xmlns:var=\"http://schemas.microsoft.com/BizTalk/2003/var\"
exclude-result-prefixes=\"msxsl var\"
version=\"1.0\"
xmlns:s0=\"urn:input:namespace\"
xmlns:ns0=\"urn:output:namespace\">
<xsl:output omit-xml-declaration=\"yes\" version=\"1.0\" method=\"xml\" />
<xsl:template match=\"/s0:Root\">
<xsl:element name=\"ns0:outputdocument\">
<xsl:apply-templates select =\"Record[position() != last()]\"/>
</xsl:element>
</xsl:template>
<xsl:template match=\"Record\">
<xsl:element name=\"Record\">
<xsl:element name=\"Field1\">
<xsl:value-of select=\"Field1\"/>
</xsl:element>
<xsl:element name=\"Field2\">
<xsl:value-of select=\"Field2\"/>
</xsl:element>
<xsl:element name=\"Field3\">
<xsl:value-of select=\"Field3\"/>
</xsl:element>
</xsl:element>
</xsl:template>
</xsl:stylesheet>[/code:1:4407151d2d]-
You can do this with two scripting functoids:
For a schema like this as both input and output:
[code:1:2c46f67690]<Schema>
Root
Record
Field1
Field2
Field3[/code:1:2c46f67690]Add a scripting functoid, select [b:2c46f67690]Inline XSLT Call Template[/b:2c46f67690]
Add this code:
[code:1:2c46f67690]<xsl:template match=\"Record\">
<xsl:element name=\"Record\">
<xsl:element name=\"Field1\">
<xsl:value-of select=\"Field1\"/>
</xsl:element>
<xsl:element name=\"Field2\">
<xsl:value-of select=\"Field2\"/>
</xsl:element>
<xsl:element name=\"Field3\">
<xsl:value-of select=\"Field3\"/>
</xsl:element>
</xsl:element>
</xsl:template>[/code:1:2c46f67690]Add another scripting functoid, select [b:2c46f67690]Inline XSLT[/b:2c46f67690]
Add this code:
[code:1:2c46f67690]<xsl:apply-templates select =\"Record[position() != last()]\"/>[/code:1:2c46f67690]Connect this functoid to the Record element of the output schema.
You will get this warning [i:2c46f67690]The \”Scripting\” functoid has no output parameters[/i:2c46f67690] about the first functoid but you can ignore this
-
-
-
-
-
-
-
-
-
- The forum ‘BizTalk 2004 – BizTalk 2010’ is closed to new topics and replies.