• Status: Solved
• Priority: Medium
• Security: Public
• Views: 2070

We are reading a worksheet from an Excel file and then creating a new one with just the single worksheet.    Works great but the data has a lot of commas in it so the output has the double quotation marks to preserve those commas.     What can I do to remove these from the new excel file?    Also - we get a garbage header row that we don't want.    Can I suppress that - the deleting of the row only deletes the data but not the row?

<cfspreadsheet action="read" src="cells\tool.xlsx" sheetname="Export" rows="1-80" columns="1-55" query="Output">

query="Output"
sheetname="Export"
overwrite="true">

0
digitalwise
• 6
• 2
1 Solution

Commented:

Edit Which step is creating the quotes or are they already present in the xlsx file? If the quotes are already part of the file - OR they're being added by action="read", I think you just have to remove them manually. Loop through the query and use regex to remove them. Then create the new sheet.
0

Commented:
As for the quotes, I'm guessing they're already present in the file. So you'll need a loop to remove them. Something along these lines, where "columnName" is the query column containing the leading/trailing double quotes.

<cfloop query="Output">
<cfset Output.columnName[currentRow] = reReplace(columnName, "^[""]+|[""]+$", "", "all")> </cfloop> Edit - Sorry, I just remembered excludeHeaderRow only applies to reading, not writing. If you mean the header row is duplicated like this Row 1: HeaderName Row 2: HeaderName Row 3: data xxxxx That's a bug. Try using spreadSheetAddRows. It adds a header row, but only once. <cfset newSheet = SpreadSheetNew("Export")> <cfset spreadSheetAddRows(newSheet, output)> <cfset spreadSheetWrite(newSheet, "c:\path\to\newFile.xlsx", true)> If you don't want ANY header row, try using shift rows to overwrite the headers. SpreadsheetShiftRows(spreadsheetObj, start, end, rows) 0 Author Commented: The columns don't have headers so this won't work. I tried just entering 1 but it didn't strip so that clearly isn't helping. However, I think that your replacement solution and your writing solution aren't taking each other into consideration. Not quite sure what needs to be done but they aren't working in conjunction. also - I have 55 columns I need to do this in. 0 Commented: In my tests the replacement worked perfectly. So either there's something about the quotes I'm misunderstanding or you're using different code on your end. Let's back up a minute to answer some of the earlier questions and get on the same page. 1) Where are these quotes coming from? Are they part of the original file itself OR are they being added by either the "read" or "write" action? 2) As far as the headers, you're saying the sheet you're reading from only contains data. So when you "write" the new file you end up with headers like "COL_1,COL_2, ..." - correct? 0 Commented: Also did you see the updates to the previous responses? And can you post a small example demonstrating your actual vs desired output ? That'll help cut to the chase quicker. ie Actual Row 1: COL_1 | COL_2 | COL_3 <=== meaningless header row Row 2: data | "xxx,xxx" | "yyy xxx" <=== double quotes in the cells Row 3: ..... Desired (no quotes and no header row) Row 1: data | xxx,xxx | yyy xxx Row 2: ..... 0 Commented: I've got to head out. But I've run several tests and it works correctly, so it seems like we're doing something different. Here's the results of simple test I did with a small sample file: ORIGINAL EXCEL FILE: Row1: John, Smith "a,b,c" <=== has embedded quotes Row2: Rob Moore 123,456,789 Row3: "Jenkins, Alan" 456 street BEFORE - query COL_1 COL_2 1 "John, Smith" ""a,b,c"" 2 Rob Moore "123,456,789" 3 ""Jenkins, Alan"" 456 street AFTER - query COL_1 COL_2 1 John, Smith a,b,c <=== quotes are gone 2 Rob Moore 123,456,789 3 Jenkins, Alan 456 street NEW EXCEL FILE: Row1: John, Smith a,b,c <=== no quotes or header Row2: Rob Moore 123,456,789 Row3: Jenkins, Alan 456 street CODE: <!--- read sample file with 3 rows ---> <cfspreadsheet action="read" src="c:/originalFile.xlsx" sheetname="Export" rows="1-3" query="Output"> <!--- debug show original values ---> <cfdump var="#Output#" label="BEFORE"> <!--- must do the replace on EVERY column that contains the double quotes, if it's all columns then use query.columnList ---> <cfloop query="Output"> <cfloop list="#Output.columnList#" index="colName"> <!--- this replaces leading/traling double quotes ONLY ---> <cfset Output[colName][currentRow] = reReplace(Output[colName][currentRow], "^[""]+|[""]+$", "", "all")>
</cfloop>
</cfloop>

<!--- debug, show fixed data values ---->
<cfdump var="#Output#" label="AFTER">

<!--- write fixed data to workbook on row 1 ---->

0

Author Commented:
I hope you are having a beer!   Wish I could buy you one!!    This is perfect.
0

Commented:
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.