Coldfusion到Excel CSVtoArray外部引号

Question

我一直在成功地使用ColdFusion函数CSVtoArray以编程方式将CSV文件转换为CSVtoArray文件，然后可以通过CFSpreadsheet导入数据库。最近，我遇到了一个数据问题，这个函数阻塞了它，而且我对RegEx (我怀疑我需要的东西)还不够强，无法修复它。我希望这里有人能给我一个合适的解决办法。

CSV数据通常以逗号分隔，并用如下引号分隔和分隔(省略号表示行之前和之后的附加数据)，这些数据不带错误地进行转换：

……，“明尼阿波利斯”，"MN"，"55555"，“病人推荐-约翰·史密斯”，.

但是，我遇到的问题是，当在(逗号分隔行)中有多余的引号时。例如，该行将失败并停止进一步导入：

……，“明尼阿波利斯”，"MN"，"55555"，“病人推荐-罗伯特”鲍勃“史密斯”，

我认为我需要做的是在引号字符上运行某种RegEx 替换--但是只有在有而不是之前或后面有逗号的情况下？但我不知道怎么做那样的事。有什么建议吗？

我将在下面包含我使用的完整的CSVtoArray函数代码。如果有任何建议或解决办法，我将不胜感激。谢谢!

    <cffunction
name="csvToArray"
access="public"
returntype="array"
output="false"
hint="I take a CSV file or CSV data value and convert it to an array of arrays based on the given field delimiter. Line delimiter is assumed to be new line / carriage return related.">
 
<!--- Define arguments. --->
<cfargument
name="file"
type="string"
required="false"
default=""
hint="I am the optional file containing the CSV data."
/>
 
<cfargument
name="csv"
type="string"
required="false"
default=""
hint="I am the CSV text data (if the file argument was not used)."
/>
 
<cfargument
name="delimiter"
type="string"
required="false"
default=","
hint="I am the field delimiter (line delimiter is assumed to be new line / carriage return)."
/>
 
<cfargument
name="trim"
type="boolean"
required="false"
default="true"
hint="I flags whether or not to trim the END of the file for line breaks and carriage returns."
/>
 
<!--- Define the local scope. --->
<cfset var local = {} />
 
<!---
Check to see if we are using a CSV File. If so, then all we
want to do is move the file data into the CSV variable. That
way, the rest of the algorithm can be uniform.
--->
<cfif len( arguments.file )>

<cfset FilePathName="\\ply-fileserver\EPDE\DOWNLOADS\#arguments.file#">

<!--- Read the file into Data. --->
<cfset arguments.csv = fileRead( FilePathName ) />
 
</cfif>
 
<!---
ASSERT: At this point, no matter how the data was passed in,
we now have it in the CSV variable.
--->
 
<!---
Check to see if we need to trim the data. Be default, we are
going to pull off any new line and carraige returns that are
at the end of the file (we do NOT want to strip spaces or
tabs as those are field delimiters).
--->
<cfif arguments.trim>
 
<!--- Remove trailing line breaks and carriage returns. --->
<cfset arguments.csv = reReplace(
arguments.csv,
"[\r\n]+$",
"",
"all"
) />
 
</cfif>
 
<!--- Make sure the delimiter is just one character. --->
<cfif (len( arguments.delimiter ) neq 1)>
 
<!--- Set the default delimiter value. --->
<cfset arguments.delimiter = "," />
 
</cfif>
 
 
<!---
Now, let's define the pattern for parsing the CSV data. We
are going to use verbose regular expression since this is a
rather complicated pattern.
 
NOTE: We are using the verbose flag such that we can use
white space in our regex for readability.
--->
<cfsavecontent variable="local.regEx">(?x)
<cfoutput>
 
<!--- Make sure we pick up where we left off. --->
\G
 
<!---
We are going to start off with a field value since
the first thing in our file should be a field (or a
completely empty file).
--->
(?:
 
<!--- Quoted value - GROUP 1 --->
"([^"]*+ (?>""[^"]*+)* )"
 
|
 
<!--- Standard field value - GROUP 2 --->
([^"\#arguments.delimiter#\r\n]*+)
 
)
 
<!--- Delimiter - GROUP 3 --->
(
\#arguments.delimiter# |
\r\n? |
\n |
$
)
 
</cfoutput>
</cfsavecontent>
 
<!---
Create a compiled Java regular expression pattern object
for the experssion that will be parsing the CSV.
--->
<cfset local.pattern = createObject(
"java",
"java.util.regex.Pattern"
).compile(
javaCast( "string", local.regEx )
)
/>
 
<!---
Now, get the pattern matcher for our target text (the CSV
data). This will allows us to iterate over all the tokens
in the CSV data for individual evaluation.
--->
<cfset local.matcher = local.pattern.matcher(
javaCast( "string", arguments.csv )
) />
 
 
<!---
Create an array to hold the CSV data. We are going to create
an array of arrays in which each nested array represents a
row in the CSV data file. We are going to start off the CSV
data with a single row.
 
NOTE: It is impossible to differentiate an empty dataset from
a dataset that has one empty row. As such, we will always
have at least one row in our result.
--->
<cfset local.csvData = [ [] ] />
 
<!---
Here's where the magic is taking place; we are going to use
the Java pattern matcher to iterate over each of the CSV data
fields using the regular expression we defined above.
 
Each match will have at least the field value and possibly an
optional trailing delimiter.
--->
<cfloop condition="local.matcher.find()">
 
<!---
Next, try to get the qualified field value. If the field
was not qualified, this value will be null.
--->
<cfset local.fieldValue = local.matcher.group(
javaCast( "int", 1 )
) />
 
<!---
Check to see if the value exists in the local scope. If
it doesn't exist, then we want the non-qualified field.
If it does exist, then we want to replace any escaped,
embedded quotes.
--->
<cfif structKeyExists( local, "fieldValue" )>
 
<!---
The qualified field was found. Replace escpaed
quotes (two double quotes in a row) with an unescaped
double quote.
--->
<cfset local.fieldValue = replace(
local.fieldValue,
"""""",
"""",
"all"
) />
 
<cfelse>
 
<!---
No qualified field value was found; as such, let's
use the non-qualified field value.
--->
<cfset local.fieldValue = local.matcher.group(
javaCast( "int", 2 )
) />
 
</cfif>
 
<!---
Now that we have our parsed field value, let's add it to
the most recently created CSV row collection.
--->
<cfset arrayAppend(
local.csvData[ arrayLen( local.csvData ) ],
local.fieldValue
) />
 
<!---
Get the delimiter. We know that the delimiter will always
be matched, but in the case that it matched the end of
the CSV string, it will not have a length.
--->
<cfset local.delimiter = local.matcher.group(
javaCast( "int", 3 )
) />
 
<!---
Check to see if we found a delimiter that is not the
field delimiter (end-of-file delimiter will not have
a length). If this is the case, then our delimiter is the
line delimiter. Add a new data array to the CSV
data collection.
--->
<cfif (
len( local.delimiter ) &&
(local.delimiter neq arguments.delimiter)
)>
 
<!--- Start new row data array. --->
<cfset arrayAppend(
local.csvData,
arrayNew( 1 )
) />
 
<!--- Check to see if there is no delimiter length. --->
<cfelseif !len( local.delimiter )>
 
<!---
If our delimiter has no length, it means that we
reached the end of the CSV data. Let's explicitly
break out of the loop otherwise we'll get an extra
empty space.
--->
<cfbreak />
 
</cfif>
 
</cfloop>
 
 
<!---
At this point, our array should contain the parsed contents
of the CSV value as an array of arrays. Return the array.
--->
<cfreturn local.csvData />
</cffunction>

Answer 1

尖端编号1

I've been using the ColdFusion function CSVtoArray successfully to programmatically convert CSV files into Excel files that I can then import into my database via CFSpreadsheet.，对我来说，这听起来像是过度设计。您是否考虑过用<cffile>读取文件，然后遍历生成的变量以插入记录？

尖端编号2

你不需要regex来处理引号。您可以使用以下更简单的字符串函数：

// delete leading double quote
row = replace(row, '"', '', 'one');
// delete trailing double quote
row = left(row, len(row) -1);
// change csv delimiters to ascii 50, record separator
row = replace(row, '",", chr(50), 'all');

在insert查询中使用查询参数将处理任何其他双引号或单引号。