Trimming column names & values

[Daemach]

@cfsimplicity I found my first CSV file today that looks like the one below. Can spreadsheet-cfml trim column names and values? I'll have to handle the non numerics, but auto-trimming would be lovely.

 costBasisPerShare  ,purchaseDate,sedol,cusip,isin,ticker,factsetID,shares
 109.60 ,1/1/2020,,,,ABBV,,51
 110.19 ,1/1/2020,,,,ABT,,37
 117.25 ,1/1/2020,,,,AJG,,50
 138.07 ,1/1/2020,,,,AMAT,,33
 504.55 ,1/1/2020,,,,AVGO,,9
 38.12 ,1/1/2020,,,,BAC,,153
 59.61 ,1/1/2020,,,,BK,,86
 695.75 ,1/1/2020,,,,BLK,,11
 63.32 ,1/1/2020,,,,BMY,,95
 63.52 ,1/1/2020,,,,C,,57
 202.96 ,1/1/2020,,,,CAT,,40
 163.56 ,1/1/2020,,,,CCI,,30
 41.77 ,1/1/2020,,,,CFG,,67
 54.94 ,1/1/2020,,,,CMCSA,,129
 50.11 ,1/1/2020,,,,COP,,97
 50.87 ,1/1/2020,,,,CSCO,,211
 30.88 ,1/1/2020,,,,CSX,,191
 -   ,1/1/2020,,,,CVX,,90
 232.96 ,1/1/2020,,,,DHR,,18
 60.71 ,1/1/2020,,,,DOW,,125

[Daemach]

It's hard to see, but there is a leading space on the first column if you select all of that.

[cfsimplicity]

Hi John. Could you elaborate a little on what you have in mind?

Which library method are you using in this case?

Are you asking for all values and column names added to a worksheet to be trimmed? I'm not sure that should happen by default and would be considered a breaking change for some, although I'm strugging to think of a real use case for leading/trailing spaces.

You could argue that it's the developer's job to ensure the data is in the right form, including trimming, before it's passed to the library.

Perhaps it could be an option, but I'm loathe to add yet another argument to what are already very crowded method signatures in many cases. Maybe a global init setting might work better, given it would affect so many methods.

Thoughts?

[Daemach]

It shouldn't be a default, but Lucee will not allow me to rename columns so another solution would be helpful if it's simpler than fully recreating the table.

I have to parse any input file uploaded to me from any source. I can parse the data I need as long as it has the required column names in any order. But in this case, whoever uploaded the file added spaces around the column name, so I can no longer identify the required fields. (columns.keyExists("costBasisPerShare") would not match the column name in the example above)

I can't control what someone is going to give me, and just need some intelligent cleaning tools. I would leave them on 100% for this application.

[cfsimplicity]

Lucee will not allow me to rename columns

There will be a native function in Lucee 6 but meantime you can call query.setColumnNames() on the underlying Java object. See https://blog.simplicityweb.co.uk/125/renaming-cfml-query-columns

I'm afraid I'm still not clear on your workflow.

1. You receive a CSV file?
2. You parse it in some way?
3. You pass the result to which function of the Spreadsheet library?

I think perhaps introducing column name auto-trimming only might be acceptable by default without worrying about it as a breaking change.

Trimming cell values would be a more significant change and would have to be optional.

Could you live with just the former?

[Daemach]

We take CSV or xls(x) and pass it to either .csvToQuery() or spreadsheet.read()

I am already trimming row values. I didn't know about query.setColumnNames(), but auto-trimming column names by default seems logical as they are key names. But do include a toggle so it can be shut off if necessary for that one weird edge case that I can't imagine.

[cfsimplicity]

@Daemach csvToQuery() apparently already trims column names and values:

<cfscript>
spreadsheet = New spreadsheet.Spreadsheet()
// create a CSV string with columns and values that have leading+trailing spaces
csv = ' Name , Phone #NewLine()# Frumpo , 12345 '
queryFromCsv = spreadsheet.csvToQuery( csv: csv, firstRowIsHeader: true )
columns = QueryColumnList( queryFromCsv )
</cfscript>
<cfoutput>
<h2>Raw CSV</h2>
<pre>#csv#</pre>
<h2>Query Columns</h2>
<pre>#columns#</pre>
<h2>Query values</h2>
<cfloop list="#columns#" item="column">
<pre>#queryFromCsv[ column ]#</pre>
</cfloop>
</cfoutput>

And read() trims the column names (but not string values):

<cfscript>
spreadsheet = New spreadsheet.Spreadsheet()
//write a spreadsheet file with columns and values that have leading+trailing spaces
path = ExpandPath( "test.xlsx" )
spreadsheet.newChainable( "xlsx" )
	.addRow( [ ' Name ', ' Phone ' ] )
	.addRow( [ ' Frumpo ', ' 1234 ' ] )
	.write( path, true )
queryFromXlsx = spreadsheet.read( src: path, format: "query", headerRow: 1 )
columns = QueryColumnList( queryFromXlsx )
</cfscript>
<cfoutput>
<h2>Query Columns</h2>
<pre>#columns#</pre>
<h2>Query values</h2>
<cfloop list="#columns#" item="column">
<pre>#queryFromXlsx[ column ]#</pre>
</cfloop>
</cfoutput>

So I'm not sure how to replicate the issue you're seeing?

[Daemach]

That is good foresight and apparently that wasn’t the problem after all. I’ll keep looking, and great work as always.

…

On Fri, Aug 4, 2023 at 8:39 AM Julian Halliwell ***@***.***> wrote: @Daemach <https://github.com/Daemach> csvToQuery() apparently already trims column names and values: <cfscript> spreadsheet = New spreadsheet.Spreadsheet() // create a CSV string with columns and values that have leading+trailing spaces csv = ' Name , Phone #NewLine()# Frumpo , 12345 ' queryFromCsv = spreadsheet.csvToQuery( csv: csv, firstRowIsHeader: true ) columns = QueryColumnList( queryFromCsv ) </cfscript> <cfoutput> <h2>Raw CSV</h2> <pre>#csv#</pre> <h2>Query Columns</h2> <pre>#columns#</pre> <h2>Query values</h2> <cfloop list="#columns#" item="column"> <pre>#queryFromCsv[ column ]#</pre> </cfloop> </cfoutput> [image: Screenshot 2023-08-04 at 16-21-09 https __test] <https://user-images.githubusercontent.com/456718/258462169-d6311ae7-3ffc-4142-9205-4ea218608dc5.png> And read() trims the column names (but not string values): <cfscript> spreadsheet = New spreadsheet.Spreadsheet() //write a spreadsheet file with columns and values that have leading+trailing spaces path = ExpandPath( "test.xlsx" ) spreadsheet.newChainable( "xlsx" ) .addRow( [ ' Name ', ' Phone ' ] ) .addRow( [ ' Frumpo ', ' 1234 ' ] ) .write( path, true ) queryFromXlsx = spreadsheet.read( src: path, format: "query", headerRow: 1 ) columns = QueryColumnList( queryFromXlsx ) </cfscript> <cfoutput> <h2>Query Columns</h2> <pre>#columns#</pre> <h2>Query values</h2> <cfloop list="#columns#" item="column"> <pre>#queryFromXlsx[ column ]#</pre> </cfloop> </cfoutput> [image: xlsx] <https://user-images.githubusercontent.com/456718/258464985-8d9cec97-e8c3-4cca-948b-929242704f90.png> [image: Screenshot 2023-08-04 at 16-31-56 https __test] <https://user-images.githubusercontent.com/456718/258465025-393b3448-85c7-4831-a0d1-d55daaf964d8.png> So I'm not sure how to replicate the issue you're seeing? — Reply to this email directly, view it on GitHub <#329 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AC7Y5YPIJHD7IPZPMX6TGULXTUJUJANCNFSM6AAAAAA3B2ZVYM> . You are receiving this because you were mentioned.Message ID: <cfsimplicity/spreadsheet-cfml/repo-discussions/329/comments/6640050@ github.com>