Import CSV files using GEL results enclosed in double quotes

[Lowell Wetzel]

Hi All,

I have an issue where I have a CSV Import that I get all results with Double Quotes. My problem is I can't seem to strip off those double quotes to actually use the data.

Is there a flag to set to not include the Double Quotes?

                        <file:processFileLines delimiter="${fileDelimiter}" embedded="${fileIsEmbedded}"
                          fileName="${filePath.concat(fileSeparator).concat(fileName)}" indexVar="fileRowNum" var="fileRow">

All my results have Double quotes around the values.

Example: "Closed Complete"

[Avadh Maheshwari]

Try this in your query: ( Test it with one column and then add it to all. This is Text Delimiter it will be there. 

select (case when col like '"%"'
             then substring(col, 2, len(col) - 1)
             else col
        end)

[Lowell Wetzel]

Hi Avadh,

For this syntax, is it possible to integrate into the load of the table? I tried adding it in and it failed.

It would seem that the "read" of the CSV file shouldn't encapsulate items with quotes.

I don't seem to recall this happening previously.

                        INSERT INTO odf_ca_u_hr_staging_role_pr
                            (id, created_date, created_by, last_updated_date, last_updated_by, partition_code, page_layout, 
                             code, name, field_1, field_2, field_3, field_4, field_5, field_6, field_7, field_8, field_9, field_10)
                        VALUES 
                            (?, SYSDATE, 1, SYSDATE, 1, 'NIKU.ROOT','odf.u_hr_staging_role_prFrame', 
                             ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                        ]]><sql:param value="${nextDBId}"/>
                                <sql:param value="${nextDBId}"/>
                                <sql:param value="${nextDBId}"/>
                                <sql:param value="${dataRow[0]}"/>
                                <sql:param value="${dataRow[1]}"/>
                                <sql:param value="${dataRow[2]}"/>
                                <sql:param value="${dataRow[3]}"/>
                                <sql:param value="${dataRow[4]}"/>
                                <sql:param value="${dataRow[5]}"/>
                                <sql:param value="${dataRow[6]}"/>
                                <sql:param value="${dataRow[7]}"/>
                                <sql:param value="${dataRow[8]}"/>
                                <sql:param value="${dataRow[9]}"/>
                              </sql:update>

[Lowell Wetzel]

I was able to get it working using a query instead. I couldn't find anything in GEL that would work with removing Double-Quotes "

        update odf_ca_u_hr_staging_role_pr PR
        set
        PR.FIELD_9 = REPLACE(PR.FIELD_9,chr(34),'')
        where
        instr(PR.FIELD_9, chr(34)) > 0