tracker issue : CF-4152543

select a category, or use search below
(searches all categories and all time range)
Title:

[ANeff] Bug for: cfhttp throws "Incorrect number of columns in row" if quoted CSV value contains newline

| View in Tracker

Status/Resolution/Reason: Closed/Fixed/

Reporter/Name(from Bugbase): Aaron Neff / Aaron Neff (Aaron Neff)

Created: 05/13/2016

Components: Net Protocols, HTTP

Versions: 10.0

Failure Type:

Found In Build/Fixed In Build: CF2016_Final /

Priority/Frequency: Major / Some users will encounter

Locale/System: English / Win XP All

Vote Count: 1

Listed in the version 2016.0.03.300466 Issues Fixed doc 
cfhttp throws "Incorrect number of columns in row" if quoted CSV value contains newline

Repro:

<cfscript>
  myCSV = 'col1,col2' & chr(13) & chr(10) & '"val,' & chr(10) & 'ue",value2';
  cfspreadsheet(action="write", filename=expandPath("./myXLS.xls"), name="myCSV", format="csv", overwrite=true);//works (good)

  myCSV = 'col1,col2' & chr(13) & chr(10) & '"val,' & chr(10) & 'ue",value2';
  fileWrite(expandPath("./myCSV.csv"), myCSV);
  cfhttp(url='http://' & CGI.HTTP_HOST & getDirectoryFromPath(CGI.SCRIPT_NAME) & 'myCSV.csv', name="resultQuery");//throws "Incorrect number of columns in row."
</cfscript>

Actual result: "Incorrect number of columns in row."

Expected result: query created successfully

----------------------------- Additional Watson Details -----------------------------

Watson Bug ID:	4152543

External Customer Info:
External Company:  
External Customer Name: Aaron Neff
External Customer Email:

Attachments:

Comments:

Can't parse CSV using cfspreadsheet, due to CF-4152515. Can't parse CSV using cfhttp, due to CF-4152543. :/
Comment by External U.
2824 | May 13, 2016 02:09:29 AM GMT
Aaron, Can you try using the textQualifier attributes with the cfhttp tag and share the result. Here's how I used it: cfhttp(url='http://' & CGI.HTTP_HOST & getDirectoryFromPath(CGI.SCRIPT_NAME) & 'myCSV.csv', name="resultQuery", textQualifier="'");
Comment by Piyush K.
2825 | May 20, 2016 07:55:25 AM GMT
Hi Piyush, Thank you very much for looking into this! Well, the textQualifier needs to be double-quote ("), not single-quote ('), b/c Row 2 Column 1 contains a comma. That is why I surrounded Row 2 Column 1 w/ double-quotes. Row 2 Column 1, of the .xls, should be "val,ue" (with a line-feed after the comma). If you view myCSV.csv w/ Notepad++, you'll see this* (good): col1,col2CRLF "val,LF ue",value2 * - In Notepad++, ensure "Show All Characters" is enabled via "View > Show Symbol > Show All Characters". Thanks!, -Aaron
Comment by External U.
2826 | May 24, 2016 03:56:28 PM GMT