issue when trying to remove " from load

[hopkinsc]

Hi all,

I am reading in a csv but one of the fields occasionally has a double quote within it (this is a free text field).

I am trying to remove the double quote but i am finding that the script stops reading at that point.

It does actually remove the double quote but no lines are read in after it.

Does anyone know hoe to get around this please?

Thanks

[MK_QSL]

instead of double quote us as below

"field name"

[field name]

[hopkinsc]

Hi,

what i mean is that i have a field called [Repair_Comments].

I then have values for this field like...

New heater required

light bulb changed

7'8" curtain pole replaced

its the double quotes in the values i need to remove.

I am using Purgechar(Repair_Comments,CHR(34))

This does remove the double quotes but it doesnt read any lines of data past the line where the double quotes were.

[anbu1984]

Can you post the entire script?

[#5]

hi

try this

Replace(Repair_Comments,CHR(34),'')

[#6]

This is a real problem with CSV files, I have tackled this in the past by reading the data in a whole line at a time into a temp table.  Once loaded I count the double quotes; rejecting any line with more than the right number.

Outline procedure is

load file a line at a time into temp. , along with a record number and a filename.

Join another column to the table showing the count of double quotes.

I can then read in the good data by reading the temp file with the right quote count, and parsing it with subfield commands.

I can also save the duff records to a temp file for later analysis of these "rejected" records.

I then visit the person who sent me duff data

Hope this helps, I would be very interested in other approaches, that don;t use external ETL processes.

Richard

[hopkinsc]

Hi, here is the script..

as you can see i have also tried replacing the " with a space but this does the same, replaces but doesnt read in any line afterwards..

LOAD RESORT,

     ROOM,

     SEQ,

     REPAIR_TYPE,

     REASON_CODE,

     makedate(20&right(BEGIN_DATE,2),applymap('MTHMAP',mid(BEGIN_DATE,4,3)),left(BEGIN_DATE,2)) as BEGIN_DATE,

     makedate(20&right(END_DATE,2),applymap('MTHMAP',mid(END_DATE,4,3)),left(END_DATE,2)) as END_DATE,

     ROOM_STATUS,

     makedate(20&right(COMPLETED_DATE,2),applymap('MTHMAP',mid(COMPLETED_DATE,4,3)),left(COMPLETED_DATE,2)) as COMPLETED_DATE,

     RETURN_STATUS,

     Purgechar(REPAIR_REMARKS,'"') as REPAIR_REMARKSTMP

//     replace(REPAIR_REMARKS,CHR(34),CHR(32)) as REPAIR_REMARKSTMP

FROM

$(vOORFilename)

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

[#8]

I would have expected for the next rows to be loaded, with the wrong data being loaded into the fields for the row with extra quotes in.  The replace Char functionality mentioned elsewhere will remove the quote, once its loaded, but not before.

Have you checked the file does not have anyother character in these as well?  Perhaps something that indicates end of file.

If your data is reasonably structureed apart for the occasional quote reading the file into a temp table a row at a time, and then rereading the data (use resident) and splitting on the "," using subfield may be your best idea (perhaps use the replace char function to remove all quotes from the line at same time)

Richard