Hello,
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.
replace(v-data,""","~~"").
Help! I'm out of ideas.
Thanks for your time,
Muriel Taft
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))
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/flc.in no-echo.
repeat:
import delimiter "~r" v-data.
message "1" v-data.
REPLACE(v-data, CHR(34), CHR(34) + CHR(34)).
message "2" v-data.
end.
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
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.
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.
Thanks Tim. This did answer my original question about how to use REPLACE effectively.