DataMystic

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

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?

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.

Oops. Forgot to answer the second question... Yes.

Hi Ken - should be ready quite soon - in the next 2 weeks.

Excellent!

Thanks for taking the time to make this change!

Ken

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.

Hi, Simon,

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

Thanks,
Ken

Hi, Simon,

It works like a charm! Thanks!

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

Thanks,
Ken

Great Ken,

It should be out by Wednesday this week - still waiting on feedback on the parallel file gathering changes.

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

Thanks Ken - will look into this next week.

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. )

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.