comma in array element breaks spreadsheet

Question

When adding data to an array, one of the elements has commas in its value. An example of the value is "Trim marks at 103, 96, and 90".

Using the following code to add the array elements to the spreadsheet object, the partdescription element, as described above, has its data span multiple columns in the spreadsheet. It is handled as separate elements and not one.

<!---Create file name variable--->
    <cfset filenametouse = 'PartLevel_Report' />
    <!---Set directory and full file path--->
    <cfset theDir = GetDirectoryFromPath(GetCurrentTemplatePath()) /> 
    <!---Attach file extension to full file path and file name--->
    <cfset theFile = theDir & filenametouse & ".xls" /> 

    <cflock name="fileActionSentItems" type="exclusive" timeout="30" throwontimeout="true">
        <cfset SpreadsheetObj = spreadsheetNew()>
        <cfset fcol = {}>
        <cfset fcol.dataformat = "@">

        <!---Create the column headings--->
        <cfset SpreadsheetAddRow(SpreadsheetObj, "Part ##, Reorder ##, Description, Bin ##, Current Inv., Staged, Allocations, Available Inv., Shelf Count, Total Shipped, Total ## of Stores, Total Ordered, Avg. Per Store, Lead Time (in days), Low Water Mark, Total ## of Stores Remaining")>

            <cfoutput query="getParts" group="partnum">

    <cfset aColumns = [ partnum , shortchar08 , partdescription , binlist , inventory.currinv , staged.stagedqty , alloc.allocqty , available , shelfCount , shipdtl.shipqty , getNumberofStores.numStores , tordered , APS, paddedLeadTime, LWM , storesRemain] />

    <!---add the column data to the spreadsheet--->
    <cfset SpreadsheetAddRow(SpreadsheetObj, ArrayToList(aColumns)) />
</cfoutput>

        <!---Generate the spreadsheet--->
        <cfspreadsheet action="write" filename="#theFile#" name="SpreadsheetObj" sheetname="Sheet1" overwrite="true" />

How may I alleviate this issue?

Solved: I set a variable to the partdescription with all commas replaced with semicolons. Now the data appears all in the same column:

 <cfset cleanDesc = rereplace(partdescription, ",", ";", "all")>
    <cfset aColumns = [ partnum , shortchar08 , cleanDesc , binlist , inventory.currinv , staged.stagedqty , alloc.allocqty , available , shelfCount , shipdtl.shipqty , getNumberofStores.numStores , tordered , APS, paddedLeadTime, LWM , storesRemain] />

See this: http://cfsimplicity.com/30/workaround-for-spreadsheetaddrow-limitation-when-column-values-contain-commas — Dan A., Mar 01 '12 at 19:50
Thank you for the guidance, Dan; however, I do not understand how to translate the example to work for my situation. I am not using cfscript and I am building the spreadsheet within my query. I have edited my original question with more code for elaboration. — aparker81, Mar 01 '12 at 20:19
@aparker81 cfscript to cfml is very easy. Instead of x=y(z); it is ` so you should be able to follow what @Dan A. posted. — Henry, Mar 02 '12 at 00:46
@aparker81: Rather than adding the solution to the question, please write it up as a new answer and "accept" it. That way it will act as a signpost for future readers. — ale, Mar 02 '12 at 19:45

score 2 · Answer 1 · answered Mar 01 '12 at 20:48

2

Have you tried SpreadsheetAddRows() instead? Pass in the whole query? Maybe that will be a workaround assuming that SpreadsheetAddRows() doesn't just call SpreadsheetAddRow() underneath.

Since SpreadsheetAddRow() does not accept an optional delimiter that most CF list feature supports, I guess alternatively you can escape the comma into something else, then replace it as a comma using spreadsheetsetcellvalue()?

You may want to file a feature request to Adobe for an optional delimiter for the SpreadsheetAddRow() function.

answered Mar 01 '12 at 20:48

Henry

32,689
19
120
221

Thanks for the input, Henry. I went off from your "optional delimiter" comment and tried that route. I ended up setting a variable to the partdescription and replaced the commas with semicolons and this appears to have solved the issue of the data being spread across multiple columns. – aparker81 Mar 01 '12 at 21:14
thx for taking my suggestion but upon reading what Dan A. posted in the comment, you should really just single quoted the string with comma. – Henry Mar 02 '12 at 00:56

score 1 · Answer 2 · answered Mar 01 '12 at 21:03

1

try using "listQualify()" to first insure that your list elements - the ones that contain commas - do not mess with the list length.

answered Mar 01 '12 at 21:03

Mark A Kruger

7,183
20
21

score 1 · Answer 3 · answered Mar 02 '12 at 01:01

According to the Adobe Engineer off Dan A.'s link

Kunal from Adobe has pointed out a much more straightforward workaround, which is to wrap the variable in single quotes and then in double quotes

So... right after <cfset aColumns = [ ... ]>

<!--- wrap element with single quote if it contains a comma --->
<cfloop from="1" to="#arrayLen(aColumns)#" index="i">
    <cfif aColumns[i] CONTAINS ','>
        <cfset aColumns[i] = "'#aColumns[i]#'">
    </cfif>
</cfloop>

<cfset SpreadsheetAddRow(SpreadsheetObj, ArrayToList(aColumns)) />

score 0 · Answer 4 · answered Feb 23 '18 at 22:33

As Henry said, wrapping the variable in single quotes worked for me, except when the last character is a comma.

<cfset SpreadsheetAddRow(SpreadsheetObj, "'col1,','col2'" />

So I added a space to the value of each cell, like this:

<cfset SpreadsheetAddRow(SpreadsheetObj, "'col1, ','col2 '" />

comma in array element breaks spreadsheet

4 Answers4

Linked