Solved

Posted on 2012-09-08
Medium Priority
1,987 Views
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
Question by:digitalwise
• 6
• 2

LVL 52

Expert Comment

ID: 38380112

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

LVL 52

Expert Comment

ID: 38380155
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 Comment ID: 38381227 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 LVL 52 Expert Comment ID: 38381251 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 LVL 52 Expert Comment ID: 38381263 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 LVL 52 Accepted Solution _agx_ earned 2000 total points ID: 38381355 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 Closing Comment

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

LVL 52

Expert Comment

ID: 38381604
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi, I will be creating today a basic tutorial on how we can create a Mail Custom Function and use it where ever we want. The main advantage about creating a custom function is that we can accommodate a range of arguments to pass to the Function and …
Hi, Even though I have created this Tutorial on My personal Blog, Some people might not able to find my website, So here i am posting it again Today, from the topic it is very clear that i will be showing you here the very basic usage of how we …
Integration Management Part 2
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
###### Suggested Courses
Course of the Month17 days, 3 hours left to enroll