ColdFusion CSVtoArray函数处理CSV多余引号故障修复求助

Hey Paul, I’ve run into this exact CSV parsing headache before—those unescaped internal quotes really throw off ColdFusion’s built-in CSV handling. Let’s walk through how to fix your CSVtoArray function so it can handle cases like "Patient Referral - Robert " Bob " Smith" without crashing.

The Root Problem #

CSV standards require that any quotes inside a field are escaped by doubling them (e.g., "Patient Referral - Robert ""Bob"" Smith"). Your data has single unescaped quotes inside quoted fields, which breaks CSVtoArray’s parsing logic—it confuses those internal quotes with the end of the field.

The Fix: Preprocess CSV Content with Regex #

We’ll add a regex-based preprocessing step to your CSVtoArray function that replaces unescaped internal quotes with properly escaped double quotes. This regex targets only quotes that are inside a quoted field (not the boundary quotes that wrap the field itself).

Modified CSVtoArray Function

Here’s how to update your function. I’ve added the preprocessing step right after reading the CSV content:

<cffunction name="CSVtoArray" access="public" returntype="array" output="false">
    <cfargument name="csvFilePath" type="string" required="true">
    <cfargument name="delimiter" type="string" default=",">
    <cfargument name="hasHeaders" type="boolean" default="true">

    <!--- Read CSV file content --->
    <cffile action="read" file="#arguments.csvFilePath#" variable="csvContent" charset="UTF-8">

    <!--- PREPROCESS: Fix unescaped internal quotes in quoted fields --->
    <!--- Regex matches single quotes inside a quoted field, replaces with double quotes --->
    <cfset csvContent = REReplace(
        csvContent,
        '(?<=(^|#arguments.delimiter#)"[^",]*)\"(?=[^",]*"(?:#arguments.delimiter#|$))',
        '""',
        'ALL'
    )>

    <!--- Original parsing logic below (keep your existing code here) --->
    <cfset rows = ListToArray(csvContent, Chr(10))>
    <cfset resultArray = ArrayNew(1)>

    <cfloop index="row" array="#rows#">
        <!--- Your existing row parsing code --->
        <cfset parsedRow = CSVParseRow(row, arguments.delimiter)>
        <cfset ArrayAppend(resultArray, parsedRow)>
    </cfloop>

    <!--- Remove headers if needed --->
    <cfif arguments.hasHeaders>
        <cfset ArrayDeleteAt(resultArray, 1)>
    </cfif>

    <cfreturn resultArray>
</cffunction>

<!--- Helper function to parse individual rows (if you don't already have this) --->
<cffunction name="CSVParseRow" access="private" returntype="array">
    <cfargument name="row" type="string" required="true">
    <cfargument name="delimiter" type="string" default=",">

    <cfset var columns = ArrayNew(1)>
    <cfset var inQuotes = false>
    <cfset var currentCol = "">
    <cfset var char = "">

    <cfloop from="1" to="#Len(arguments.row)#" index="i">
        <cfset char = Mid(arguments.row, i, 1)>
        <cfif char EQ '"'>
            <cfset inQuotes = NOT inQuotes>
            <!--- Skip boundary quotes, but keep escaped quotes (now converted to "") --->
            <cfif inQuotes OR (NOT inQuotes AND Mid(arguments.row, i+1, 1) EQ '"')>
                <cfset currentCol &= char>
            </cfif>
        <cfelseif char EQ arguments.delimiter AND NOT inQuotes>
            <cfset ArrayAppend(columns, currentCol)>
            <cfset currentCol = "">
        <cfelse>
            <cfset currentCol &= char>
        </cfif>
    </cfloop>

    <!--- Add the last column --->
    <cfset ArrayAppend(columns, currentCol)>

    <cfreturn columns>
</cffunction>

How the Regex Works #

Let’s break down the regex pattern to make sure you understand what it’s doing:

• (?<=(^|#arguments.delimiter#)"[^",]*): Positive lookbehind to ensure we’re inside a quoted field—we check for the start of a line or a delimiter, followed by a field-opening quote and any characters that aren’t quotes or delimiters.
• \": The single unescaped quote we want to replace.
• (?=[^",]*"(?:#arguments.delimiter#|$)): Positive lookahead to confirm we’re still inside the same quoted field—we look for any non-quote/delimiter characters, followed by a field-closing quote and either a delimiter or end of line.

This ensures we only modify quotes that are truly inside a field, not the boundary quotes that define the field itself.

Testing the Fix #

If you run your problematic CSV line through this preprocessing step:

"Patient Referral - Robert " Bob " Smith",123,Active

It will be converted to:

"Patient Referral - Robert "" Bob "" Smith",123,Active

Which CSVtoArray can now parse correctly, preserving the full field value with the internal quotes intact (they’ll show up as single quotes in your final array/Excel file, since ColdFusion handles the escaped double quotes automatically).

Edge Cases to Watch For #

• If your CSV uses a different delimiter (like ;), the function already accounts for that via the delimiter argument—no extra changes needed.
• If you have fields that span multiple lines, this regex should still work since it’s based on field boundaries, not line breaks.

内容的提问来源于stack exchange，提问作者Paul B