I am converting a CSV file to array using this post. Everything was working fine. But I got a file which contains extra quotes in field values like:
"bash: "shortcuts" are"
and
"bash: \"shortcuts\" are"
.
So I tried replacing these quotes like this:
<cffile action="read" file="#filePath#" variable="csvContent">
<cfset csvContent = reReplace(csvContent, '(?:[^,\r\n])"(?:[^,\r\n])', '"', 'ALL')>
<--- Then do the conversion --->
<cfset array = csvToArray(csv = csvContent)>
But the non capturing group is not working. What am I doing wrong?
Is there any other way to do this?
Edit 1:
I also tried using cfhttp
and got following error:
<cfhttp name="csvToQuery" method="get" url="#url#" />
Detail : Verify the number of columns specified in the columns attribute and in the target file
Message : Incorrect number of columns in row.
StackTrace : coldfusion.tagext.net.HttpTag$InvalidColumnsException: Incorrect number of columns in row. at coldfusion.tagext.net.HttpTag.connHelper(HttpTag.java:1149) at coldfusion.tagext.net.HttpTag.doEndTag(HttpTag.java:1219) at cfmfhttp2ecfm308364137.runPage(C:\inetpub\wwwroot\mfhttp.cfm:1) at coldfusion.runtime.CfJspPage.invoke(CfJspPage.java:244) at coldfusion.tagext.lang.IncludeTag.doStartTag(IncludeTag.java:446) at coldfusion.filter.CfincludeFilter.invoke(CfincludeFilter.java:65) at coldfusion.filter.IpFilter.invoke(IpFilter.java:64) at coldfusion.filter.ApplicationFilter.invoke(ApplicationFilter.java:430) at coldfusion.filter.RequestMonitorFilter.invoke(RequestMonitorFilter.java:48) at coldfusion.filter.MonitoringFilter.invoke(MonitoringFilter.java:40) at coldfusion.filter.PathFilter.invoke(PathFilter.java:112) at coldfusion.filter.LicenseFilter.invoke(LicenseFilter.java:30) at coldfusion.filter.ExceptionFilter.invoke(ExceptionFilter.java:94) at coldfusion.filter.ClientScopePersistenceFilter.invoke(ClientScopePersistenceFilter.java:28) at coldfusion.filter.BrowserFilter.invoke(BrowserFilter.java:38) at coldfusion.filter.NoCacheFilter.invoke(NoCacheFilter.java:58) at coldfusion.filter.GlobalsFilter.invoke(GlobalsFilter.java:38) at coldfusion.filter.DatasourceFilter.invoke(DatasourceFilter.java:22) at coldfusion.filter.CachingFilter.invoke(CachingFilter.java:62) at coldfusion.CfmServlet.service(CfmServlet.java:219) at coldfusion.bootstrap.BootstrapServlet.service(BootstrapServlet.java:89) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) at coldfusion.monitor.event.MonitoringServletFilter.doFilter(MonitoringServletFilter.java:42) at coldfusion.bootstrap.BootstrapFilter.doFilter(BootstrapFilter.java:46) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:220) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:122) at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:501) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102) at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:950) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:422) at org.apache.coyote.ajp.AjpProcessor.process(AjpProcessor.java:199) at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:607) at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:314) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) at java.lang.Thread.run(Thread.java:722)
Oh you won't be able to fix this sort of input by yourself so easily. Regular expressions will break your data even further.
Can you create a little script in Java to handle that? If you do, then use uniVocity-parsers to read your CSV input and write it back with the correct quote escapes:
This is the only CSV parser that can handle broken quote escapes. Try this example:
This code will produce the following output (which can probably be read by your data import tool):
Disclosure: I am the author of this library. It's open-source and free (Apache V2.0 license).
ColdFusion 10+ Example:
Load the jar in your Application.cfc
Create instances of the parser classes with createObject: