Geek Answers Handbook

CSV (with extra quotation marks in the field value) for an array in ColdFusion

I am converting a CSV file to an array using this post. Everything is working fine. But I have a file that contains extra quotation marks in the field values, for example:

"bash: "shortcuts" are" and

"bash: \"shortcuts\" are".

So, I tried replacing these quotes as follows:

<cffile action="read" file="#filePath#" variable="csvContent">
<cfset csvContent = reReplace(csvContent, '(?:[^,\r\n])"(?:[^,\r\n])', '&quot;', 'ALL')>

<--- Then do the conversion --->
<cfset array = csvToArray(csv = csvContent)>

But the non-capture group is not working. What am I doing wrong?

Is there any other way to do this?

Change 1:

I also tried using cfhttpand got the following error:

<cfhttp name="csvToQuery" method="get" url="#url#" />

More details . Check the number of columns indicated in the columns. attribute and to the target file

Message . Invalid number of columns per row.

Stacktrace: coldfusion.tagext.httpTag $InvalidColumnsException: . coldfusion.tagext.net.HttpTag.connHelper(HttpTag.java:1149) coldfusion.tagext.net.HttpTag.doEndTag(HttpTag.java:1219) cfmfhttp2ecfm308364137.runPage(C:\inetpub\wwwroot\mfhttp.cfm: 1) at coldfusion.runtime.CfJspPage.invoke(CfJspPage.java:244) coldfusion.filter.CfincludeFilter.invoke(CfincludeFilter.java:65) coldfusion.filter.IpFilter.invoke(IpFilter.java:64) coldfusion.filter.ApplicationFilter.invoke(ApplicationFilter.java:430) coldfusion.filter.RequestMonitorFilter.invoke(RequestMonitorFilter.java:48) at coldfusion.filter.MonitoringFilter.invoke(MonitoringFilter.java:40) coldfusion.filter.PathFilter.invoke(PathFilter.java:112) coldfusion.filter.LicenseFilter.invoke(LicenseFilter.java:30) coldfusion.filter.ExceptionFilter.invoke(ExceptionFilter.java:94) coldfusion.filter.ClientScopePersistenceFilter.invoke(ClientScopePersistenceFilter.java:28) at coldfusion.filter.BrowserFilter.invoke(BrowserFilter.java:38) coldfusion.filter.NoCacheFilter.invoke(NoCacheFilter.java:58) coldfusion.filter.GlobalsFilter.invoke(GlobalsFilter.java:38) coldfusion.filter.DatasourceFilter.invoke(DatasourceFilter.java:22) coldfusion.filter.CachingFilter.invoke(CachingFilter.java:62) coldfusion.CfmServlet.service(CfmServlet.java:219) coldfusion.bootstrap.BootstrapServlet.service(BootstrapServlet.java:89) org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303) org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) coldfusion.monitor.event.MonitoringServletFilter.doFilter(MonitoringServletFilter.java:42) coldfusion.bootstrap.BootstrapFilter.doFilter(BootstrapFilter.java:46) org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241) org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:220) org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:122) org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:501) org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171) org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102) org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:950) org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116) org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:422) org.apache.coyote.ajp.AjpProcessor.process(AjpProcessor.java:199) org.apache.coyote.AbstractProtocol $AbstractConnectionHandler.process(AbstractProtocol.java:607) org.apache.tomcat.util.net.JIoEndpoint $SocketProcessor.run(JIoEndpoint.java:314) java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) java.util.concurrent.ThreadPoolExecutor $Worker.run(ThreadPoolExecutor.java:615) org.apache.tomcat.util.threads.TaskThread $WrappingRunnable.run(TaskThread.java:61) java.lang.Thread.run(Thread.java:722)

+4
1

, . .

script Java, ? , uniVocity-parsers, CSV- :

CSV, . :

import com.univocity.parsers.csv;

import java.io.*;
import java.util.*;

public class Test {

    public static void main(String ... args){
        CsvParserSettings settings = new CsvParserSettings();
        settings.getFormat().setLineSeparator("\r\n");
        settings.setParseUnescapedQuotes(true); // THIS IS IMPORTANT FOR YOU
        CsvParser parser = new CsvParser(settings);

        String line1 = "something,\"a quoted value \"with unescaped quotes\" can be parsed\", something\r\n";
        System.out.println("Input line: " + line1);

        String line2 = "\"after the newline \r\n you will find \" more stuff\r\n";
        System.out.println("Input line: " + line2);

        List<String[]> allInputLines = parser.parseAll(new StringReader(line1 + line2));

        System.out.println("===============\nParsed input values\n===============");
        int count = 0;
        for(String[] line : allInputLines){
            System.out.println("From line " + ++count + ":");
            for(String element : line){
                System.out.println("\t" + element);

            }
            System.out.println();
        }

        //Let write your output CSV
        StringWriter output = new StringWriter();
        CsvWriterSettings writerSettings = new CsvWriterSettings();
        writerSettings.getFormat().setLineSeparator("\r\n");
        writerSettings.getFormat().setQuoteEscape('\\'); //it seems you are using backslash as quote escape
        writerSettings.getFormat().setCharToEscapeQuoteEscaping('\\'); //when your quote escape character is not the same as the quote character, you might need to escape the escape character as well
        writerSettings.setQuoteAllFields(true); //let force quotes on all fields so whatever is parsing your input file has more  chance of doing it properly
        CsvWriter writer = new CsvWriter(output, writerSettings);

        for(String[] row : allInputLines){
            writer.writeRow(row);
        }
        writer.close();

        System.out.println("===============\nNicely formatted output\n===============");
        System.out.println(output.toString());

    }

}

(, , ):

Input line: something,"a quoted value "with unescaped quotes" can be parsed", something

Input line: "after the newline 
you will find " more stuff

===============
Parsed input values
===============
From line 1:
    something
    a quoted value "with unescaped quotes" can be parsed
    something

From line 2:
    after the newline 
you will find " more stuff


===============
Nicely formatted output
===============
"something","a quoted value \"with unescaped quotes\" can be parsed","something"

"after the newline 
 you will find \" more stuff"

: . ( Apache V2.0).

ColdFusion 10+:

  • Application.cfc

    this.javaSettings = { loadPaths: ["C:\path\to\univocity-parsers-1.5.6.jar" ]};

  • createObject:

    filePath = "c:\path\to\yourFile.csv";
settings = createObject("java", "com.univocity.parsers.csv.CsvParserSettings").init();
settings.getFormat().setLineSeparator(chr(13)& chr(10));
settings.getFormat().setQuoteEscape("\");
settings.setParseUnescapedQuotes(true); // THIS IS IMPORTANT FOR YOU
parser = createObject("java", "com.univocity.parsers.csv.CsvParser").init(settings);
reader = createObject("java", "java.io.StringReader").init(fileRead(filePath));
arrayOfLines = parser.parseAll(reader);

// display results
counter = 1;
for (line in arrayOfLines) {
    writeOutput("<br>From line "& (counter++) & ":");
    for (element in line) {
       writeOutput("<br>"& element);
    }
}
+2

More articles:


All Articles