Edit schema for delimited file in BizTalk Editor

Edit schema for delimited file in BizTalk Editor

Post by Amy » Sat, 04 Aug 2001 06:35:51



I get a .txt file from customer, 'customerinfo.txt'.

It's a comma-delimited file. e.g.

"customername","phone","address"
"a","123","22 rain"
"b,""456","33 rain"

How do I start create the schama for this file in Biztalk Editor? I can not
import file like I did for well-formated XML file? So I have to manully edit
one?

What's the root element name? I only see the fields name in the file.

Please help.

Thanks very much in advance.

-amy

 
 
 

Edit schema for delimited file in BizTalk Editor

Post by Everett Ya » Sun, 05 Aug 2001 05:14:49


You have to manually create one using the Editor.

If your flat file is not that complicated, it's a rather straight forward
process.

From your example, you will juse use CR for record delimiter and comma for
field delimiter.

The root record's name is not a major issue.  The only contrain on the
names would be how the document is to be recognized and identified later on.

HTH

Everett Yang

 
 
 

Edit schema for delimited file in BizTalk Editor

Post by Amy » Sun, 05 Aug 2001 05:41:16


Hi Yang,

Thanks for the reply.

I created the schema. It look easy.

One question I had. If the field has comma in the data, it breaks the
validation.

e.g. "Great Northern Paper, Inc.","

I get the error:

Additional data in the document instance was not parsed. Make sure that the
document instance you want to validate contains only one document.
 The parser failed to convert the document due to processing errors. See the
following messages for details.

 How do you solve that?

Thanks again.

-amyj

Quote:> You have to manually create one using the Editor.

> If your flat file is not that complicated, it's a rather straight forward
> process.

> From your example, you will juse use CR for record delimiter and comma for
> field delimiter.

> The root record's name is not a major issue.  The only contrain on the
> names would be how the document is to be recognized and identified later
on.

> HTH

> Everett Yang

 
 
 

Edit schema for delimited file in BizTalk Editor

Post by Everett Ya » Sun, 05 Aug 2001 06:04:16


If you must use comma in the input txt file, you can use another field
delimiter.  That'd be a easy
workaround.  Or, you can define an escape character.

HTH

Everett Yang

 
 
 

Edit schema for delimited file in BizTalk Editor

Post by Amy » Sun, 05 Aug 2001 06:40:04


Hi Yang,

The BizTalk Editor help file said the wrap character can solve this just
like you use escape character.

I wonder why it's not working in my case. I attached my file in last
message.

Here is what they say in the help file:

      Wrap Character Type a character to enclose field data. This property
is useful if you have a character in your field data that is also used as
the delimiter value for the field's parent node. For example, if your field
data is
      Browne,Peter,1231,yes

      and you have chosen a comma as the delimiter value of the node that
contains the field, BizTalk Editor interprets the comma after "Browne" to be
a delimiter, even if you intend for it to be part of the field data. A
solution for this is to define a value for the wrap character property and
then enclose the field data in the wrap character. For example, you can set
the wrap character property to double quotation marks for the first field
and then type your field data as in the following example:

      "Browne,Peter",1231,yes

      The comma between the double quotation marks is interpreted by BizTalk
Editor to be field data rather than a delimiter value

-amyj


> Hi Yang,

> Thanks.

> I tried pue escape character '/', it works.

> But the flatfile is what we get from our customer. Their generate the
file.

> I attached the file to you, without set the escape character. Did you
notice
> they use double quote to the field text. I set the double quote as wrap
> character. I don't know If I can do something with this double quote to
make
> BizTalk treat the whole field text as a string.

> Thanks again.

> -amyj



> > If you must use comma in the input txt file, you can use another field
> > delimiter.  That'd be a easy
> > workaround.  Or, you can define an escape character.

> > HTH

> > Everett Yang

 
 
 

Edit schema for delimited file in BizTalk Editor

Post by Everett Ya » Sun, 05 Aug 2001 06:53:20


Hi,

I don't have a quick solution for that one.  

I think it may not be possible to have the parser ignore a particular
character while being wrapped by double quotes.

In that case, you may have to work on a pre-processor.

Everett Yang

 
 
 

Edit schema for delimited file in BizTalk Editor

Post by Amy » Sun, 05 Aug 2001 07:30:39


That's what the wrap character and escape character for.

I don't know why the wrap character is not working.

I applied Biztalk sp1. Still not working.

Hope someone can see this message and give some idea.  I'll also post
another message for this.

Thank you.

-amyj


Quote:> Hi,

> I don't have a quick solution for that one.

> I think it may not be possible to have the parser ignore a particular
> character while being wrapped by double quotes.

> In that case, you may have to work on a pre-processor.

> Everett Yang

 
 
 

Edit schema for delimited file in BizTalk Editor

Post by Everett Ya » Tue, 07 Aug 2001 23:19:45


Hi,

Try change the delimiter order to infix, set to Ignore Linefeed, and remove
any additional CR's.  That should work.

Everett Yang

 
 
 

Edit schema for delimited file in BizTalk Editor

Post by Amy » Wed, 08 Aug 2001 01:47:24


Hi Yang:

The delimiter order is infix. The 'Skip Line Feed' preperty is Yes.

What do you mean remove any additonal CR's?

I have to set 'Skip Carriage Return' to No. If I set to yes, I get err msg:
While parsing record "CustomerFile", the parser cannot find the required
delimiter: "
" (0x000d).

But if I have an extra line betten the record, it works. Like this:

"cust_name","cust_phone"

"amy","232-1111"

"kevin , Lee","334-9008"

Notice, Even I have the comma in the cust name field, it still work. But I
have to have a extra line between record. Why ? I think my setting still
wrong somewhere.

Can you get my attachment file? If not I'll send you another one. Maybe you
can fix this in a sec. Thanks.

-amy


Quote:> Hi,

> Try change the delimiter order to infix, set to Ignore Linefeed, and
remove
> any additional CR's.  That should work.

> Everett Yang

 
 
 

Edit schema for delimited file in BizTalk Editor

Post by Amy » Wed, 08 Aug 2001 02:20:10


Hi Yang:

I found I have a extra CR at end of the file. After I delete that, it works.

But If I have record like this:

"kevin , Lee","322","454-8797"

The xml instance I get for this record is:

<cust_name>"Kevin</cust_name>
<cust_id>Lee"</cust_id>

which is not right. The comma in the cust_name field is wrapped by
boutle-quote. It's not a delimiter. Why the parsor still think it's a
delimiter. Any idea?

Thanks.

-amyj


Quote:> Hi,

> Try change the delimiter order to infix, set to Ignore Linefeed, and
remove
> any additional CR's.  That should work.

> Everett Yang

 
 
 

Edit schema for delimited file in BizTalk Editor

Post by Everett Ya » Wed, 08 Aug 2001 02:30:35


Have you looked at the last example I sent you?  I think it works as you
specified.  

Everett Yang

 
 
 

Edit schema for delimited file in BizTalk Editor

Post by Amy » Wed, 08 Aug 2001 03:40:42


Hi Yang,

Thanks very much for you help on the way.

I finaly get it works.

-amyj


Quote:> Hi,

> Here you go.  It tested fine on my machine now.  Remember to keep track of
> line feeds and carriage returns for each record.  That is what happened.
> You didn't skip line feed at some places.

> Everett Yang

 
 
 

Edit schema for delimited file in BizTalk Editor

Post by Everett Ya » Wed, 08 Aug 2001 03:43:42


you are missing one field per record.  you don't have the last ID value.  
When I tested the input file, I added a value in there so that the parser
won't complain.

Everett Yang

 
 
 

1. delimited file in biztalk editor

I 'm editing a schema in biztalk editor for a comma-delimited flat file.

The file is like this

"custname","phone","address"
"a","123","33 rain "
"b","234","34 rain "

The file name is testfile.txt.

My quesiton is what's the root element. Is that he filename"testfile", or
what? I think the "custname"... are fields. Then what's the root element?

for example, if the root element is <rootelement>, then I set the default
record delimiter to 'CR', default field delimiter to ','. is that right.

Please help. Thanks.

-amy

2. MGS 2 on XBox?

3. Biztalk Editor / Schemas / Open-Closed parameter

4. adding VPN to already config'd PIX 515 6.2(2)

5. Once again: How do I edit qualified element names in BizTalk Editor in BTS 2000?

6. xntp behind firewall?

7. Cannot open schemas in the Biztalk Editor

8. NTVDM.EXE Priority

9. Help - Delimited text files and BizTalk?

10. Custom Delimited files - Biztalk doesnt seem to work with 3 omissions

11. import an office file in Biztalk Editor

12. XML file could not be properly opened by BizTalk editor

13. converting xml file to flat file delimited by comma