Problem importing tab delimited file containing double quote

Posted by Admin on 26-Mar-2007 09:48


I am trying to import a tab delimited file where the description field sometimes contains double quotes. For instance, to indicate inches, a description might read __3/8" hose__. I am using an import delimiter "~011" statement to funnel each record (distinguished by carriage return) into a temp table.

However, when the program hits one of these double quotes, it assumes that is a field begin, and goes until it hits another double quote. It ignores tabs contained within this block of text. I then get the error "IMPORT data field wider than 65534 characters. (4138) ** Invalid character in numeric input E. (76)".

I have tried importing the text without delimiters into a straight character field and using a repeat-substring(data) type statement to replace the " with a ~", but this hasn't worked. I've also tried using the REPLACE statement below with no luck.


Help! I'm out of ideas.

Thanks for your time,

Muriel Taft

All Replies

Posted by Tim Kuehn on 26-Mar-2007 10:00

How about using ENTRY() to pull the fields out of the string?

Alternatively, "~"s can be hard to use properly because of how ABL interprets them.

Instead, I'd use

REPLACE(v-data, CHR(34), CHR(34) + CHR(34))

Posted by Admin on 26-Mar-2007 10:47

Hi Tim,

Thanks for the reply. I tried using the replace statement you suggested, and it didn't appear to make any difference in the results. What am I missing?

Line from input file looks like this:

TDA R458551 HYDRAULIC HOSE " 1 14.52 15.25 18.2 19.41

Here's my code:

input from /work/mtaft/ no-echo.


import delimiter "~r" v-data.

message "1" v-data.

REPLACE(v-data, CHR(34), CHR(34) + CHR(34)).

message "2" v-data.


Resulting display:

1 TDA R458551 HYDRAULIC HOSE " 1 14.52 15.25 18.2 19.41

2 TDA R458551 HYDRAULIC HOSE " 1 14.52 15.25 18.2 19.41

Posted by Admin on 26-Mar-2007 11:43

I see what this is doing, it's replacing one double-quote with two double-quotes. This doesn't solve my problem though because if the double-quote is at the end of the description, an export statement adds a tab between the description and the double quotes. I'm think I'm going to have to find a UNIX scripting solution to strip out the double quotes.

Posted by Tim Kuehn on 26-Mar-2007 12:14

In order for REPLACE to work, you need to assign it's return value - like so:

v-data = REPLACE(v-data, CHR(34), CHR(34) + CHR(34)).

although once that was done, there's still the question of what your extract code would do with the resulting string.

Posted by Admin on 26-Mar-2007 13:15

Thanks Tim. This did answer my original question about how to use REPLACE effectively.

This thread is closed