Copybook Filter Quote option

Get help with installation and running here.

Moderators: DataMystic Support, Moderators, DataMystic Support, Moderators, DataMystic Support, Moderators

Post Reply
KBliss
Posts: 21
Joined: Sat Feb 14, 2009 1:57 am

Copybook Filter Quote option

Post by KBliss »

The change to automatically place quotes around text fields when using the Copybook Filter is posing a maintenance issue for my company. We use the Copybook Filter or the Copybook Wizard to handle specific COBOL EBCDIC-based files, depending on what cleanup is needed. The Wizard does not automatically place the quotes around text fields. I would like to see the quotes be an option on the Copybook Filter rather than be automatic.

I have a work around by adding an Easy Pattern to drop the quotes, but this will also mean changing dozens of filters. The option would be the preferred route.

Thanks,
Ken
User avatar
DataMystic Support
Site Admin
Posts: 2227
Joined: Mon Jun 30, 2003 12:32 pm
Location: Melbourne, Australia
Contact:

Re: Copybook Filter Quote option

Post by DataMystic Support »

Yes Ken - we can do this. My question is - why wouldn't you want quotes? Doesn't the target software handle them?

Also, I assume that adding quotes for text fields when the string contains commas (or the selected delimiter) or a quote is fine?
KBliss
Posts: 21
Joined: Sat Feb 14, 2009 1:57 am

Re: Copybook Filter Quote option

Post by KBliss »

Yes, the SQL SSIS package can be set to look for quotes or not. The issue is consistancy. The Copybook Wizard does not have the option for Quotes, yet the Copybook Filter is forced with Quotes. We make changes to filters on a monthly basis. When possible, we use the Copybook Filter, rather than the Wizard, to do the conversions. If we switch between types, due to cleanup issues in the data itself, we'd need to change the SSIS package as well. Yes, this would be a minor change. It's just one more maintenance item to have in our process for making changes to the system.

If it's a relatively easy change to make in TextPipe that could be worked in this year, that would be great! It's not a show stopper for us by any means, just something we'd like to see to have some consistancy in how we load our data.
KBliss
Posts: 21
Joined: Sat Feb 14, 2009 1:57 am

Re: Copybook Filter Quote option

Post by KBliss »

Oops. Forgot to answer the second question... Yes. :D
User avatar
DataMystic Support
Site Admin
Posts: 2227
Joined: Mon Jun 30, 2003 12:32 pm
Location: Melbourne, Australia
Contact:

Re: Copybook Filter Quote option

Post by DataMystic Support »

Hi Ken - should be ready quite soon - in the next 2 weeks.
KBliss
Posts: 21
Joined: Sat Feb 14, 2009 1:57 am

Re: Copybook Filter Quote option

Post by KBliss »

Excellent! :D

Thanks for taking the time to make this change!

Ken
User avatar
DataMystic Support
Site Admin
Posts: 2227
Joined: Mon Jun 30, 2003 12:32 pm
Location: Melbourne, Australia
Contact:

Re: Copybook Filter Quote option

Post by DataMystic Support »

Hi Ken,

This change is now ready in the TextPipe Pro Beta version in the secure area. This is a pre-release of v8.6 which will be released shortly if there are no hiccups.
KBliss
Posts: 21
Joined: Sat Feb 14, 2009 1:57 am

Re: Copybook Filter Quote option

Post by KBliss »

Hi, Simon,

Thanks! I'll post feedback as soon I can. There are a few bureaucratic hoops to jump through on my end. :roll:

Thanks,
Ken
KBliss
Posts: 21
Joined: Sat Feb 14, 2009 1:57 am

Re: Copybook Filter Quote option

Post by KBliss »

Hi, Simon,

It works like a charm! Thanks!

Do you have an official release date for v8.6?

Thanks,
Ken
User avatar
DataMystic Support
Site Admin
Posts: 2227
Joined: Mon Jun 30, 2003 12:32 pm
Location: Melbourne, Australia
Contact:

Re: Copybook Filter Quote option

Post by DataMystic Support »

Great Ken,

It should be out by Wednesday this week - still waiting on feedback on the parallel file gathering changes.
KBliss
Posts: 21
Joined: Sat Feb 14, 2009 1:57 am

Re: Copybook Filter Quote option

Post by KBliss »

Hi, Simon,

A new glitch has come up with regards to the whole Quote around text fields.

The option is turned off.
There is a double-quote inside the text field.
TextPipe is putting double-quotes around the text field anyway, only for that record.

So, we have a mix of records with double-quoted fields and not. When you get time, would you please look into this?

Thanks,
Ken
User avatar
DataMystic Support
Site Admin
Posts: 2227
Joined: Mon Jun 30, 2003 12:32 pm
Location: Melbourne, Australia
Contact:

Re: Copybook Filter Quote option

Post by DataMystic Support »

Thanks Ken - will look into this next week.
KBliss
Posts: 21
Joined: Sat Feb 14, 2009 1:57 am

Re: Copybook Filter Quote option

Post by KBliss »

Sounds good! Thanks, Simon!

This whole quote/double-quote issue has shown a couple options I think you may want to consider in the future: a field to enter the text qualifier and an option to "double up" the text qualifier when the text qualifier is in the body of the text.

Here's why...

My company and department uses Microsoft Access and SQL Server heavily. Unfortunately, the two products do not handle text fields in the same way. (Go figure. :roll: )

Text Qualifiers: Access recognizes three different text qualifiers: nothing, single quote or double quote. SQL has a field to enter the text qualifer. To make TextPipe more flexible, allow for any character to be the text qualifer. Obviously, a double quote will work for both easily enough as you have it, so this is not a show stopper by any means. It's just something I noticed while working with both Access and SQL.

Doubling the text qualifer option: Up near the top of this chain, you asked if doubling up the double-quote worked for me or not. I said "yes", but it turns out I was wrong. SQL and Access work very differently in this regards. If you have a text field with a double-quote in it, SQL will just smile and load the double-quote into the field, regardless of if you have a text qualifer or not. Access requires the extra double-quote in order to load the double-quote properly into the field. If you don't, Access truncates the field at the second instance of a double-quote. For instance...

File has two records:
Single Story "A" frame
Gold Chain 18" necklace

As it stands, if we turn the option on for a double-quote text qualifier, TextPipe will give us:
"Single Story ""A"" frame"
"Gold Chain 18"" necklace"

SQL will load as
Single Story ""A"" frame
Gold Chain 18"" necklace

Access will load as
Single Story "A" frame
Gold Chain 18" necklace

So, in order for SQL to load the data correctly, the doubling-up of the text qualifier needs to be removed. So, if we do that we get:
"Single Story "A" frame"
"Gold Chain 18" necklace"

However,
SQL will load as
Single Story "A" frame
Gold Chain 18" necklace

Access will load as
Single Story
Gold Chain 18

Access will truncate and gives errors if there are additional fields after the text field because it cannot parse the rest of the data correctly. So, I would like to see the doubling-up to be an option in order to handle both Access and SQL loading quirks. We are able to work around it in SQL data load process, so it's not a show-stopper there, either.
Post Reply