RE: CFspreadsheet issue
I'm seeing a missing # sign after Form.Request -Original Message- From: Bruce Sorge [mailto:sor...@gmail.com] Sent: Friday, August 10, 2012 2:28 PM To: cf-talk Subject: CFspreadsheet issue Hello list, Long time (years) no post. I am trying to read from a spreadsheet that is loaded up onto the server and then insert the information into the database. I have three column headers called Code, Barcode and ResortID. Code and Barcode have values and ResortID is empty. The reason that I did this is because I want to insert the ResortID from a formfield called ResortID. The problem is that I am getting the error message: Columc count doesn't match value count at row 1. Here is my code: !--- Read the spreadsheet data into a query object --- cfspreadsheet action=read query=DealsDSN headerrow=1 src=#ExpandPath(Spreadsheets/#ClientFile#)# / !--- Loop thorugh the query starting with the first row containing data (row 2) --- cfloop query=MahDSN startrow=2 !--- Insert the code and bar code into the database for future use --- cfquery datasource=DealsDSN result=foobar INSERT INTO BarCodes (Code, BarCode, ResortID) VALUES ( cfqueryparam cfsqltype=cf_sql_varchar value=#Code# cfqueryparam cfsqltype=cf_sql_varchar value=#BarCode# cfqueryparam cfsqltype=cf_sql_int value=#Form.ResortID ) /cfquery /cfloop The error is on cfqueryparam cfsqltype=cf_sql_int value=#Form.ResortID Any assistance is greatly appreciated. Bruce ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:352087 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: CFspreadsheet issue
Yeah I picked up on that and still the same issue. On Aug 10, 2012, at 1:00 PM, Sandra Clark sclarkli...@gmail.com wrote: I'm seeing a missing # sign after Form.Request ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:352088 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: CFspreadsheet issue
Did you pick up the missing commas between the cfparams? They need commas, just like the field list. That would give a mismatched column count error. -Cameron On Fri, Aug 10, 2012 at 3:05 PM, Bruce Sorge sor...@gmail.com wrote: Yeah I picked up on that and still the same issue. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:352090 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: CFspreadsheet issue
You do know your query names don't match up.. Your reading the spreadsheet into a query object called DealsDSN and looping over an object called MahDSN On Fri, Aug 10, 2012 at 2:27 PM, Bruce Sorge sor...@gmail.com wrote: Hello list, Long time (years) no post. I am trying to read from a spreadsheet that is loaded up onto the server and then insert the information into the database. I have three column headers called Code, Barcode and ResortID. Code and Barcode have values and ResortID is empty. The reason that I did this is because I want to insert the ResortID from a formfield called ResortID. The problem is that I am getting the error message: Columc count doesn't match value count at row 1. Here is my code: !--- Read the spreadsheet data into a query object --- cfspreadsheet action=read query=DealsDSN headerrow=1 src=#ExpandPath(Spreadsheets/#ClientFile#)# / !--- Loop thorugh the query starting with the first row containing data (row 2) --- cfloop query=MahDSN startrow=2 !--- Insert the code and bar code into the database for future use --- cfquery datasource=DealsDSN result=foobar INSERT INTO BarCodes (Code, BarCode, ResortID) VALUES ( cfqueryparam cfsqltype=cf_sql_varchar value=#Code# cfqueryparam cfsqltype=cf_sql_varchar value=#BarCode# cfqueryparam cfsqltype=cf_sql_int value=#Form.ResortID ) /cfquery /cfloop The error is on cfqueryparam cfsqltype=cf_sql_int value=#Form.ResortID Any assistance is greatly appreciated. Bruce ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:352092 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: CFspreadsheet issue
Thanks Cameron. I have been out of the game so long I am making noob mistakes, LOL. On Aug 10, 2012, at 1:33 PM, Cameron Childress camer...@gmail.com wrote: Did you pick up the missing commas between the cfparams? They need commas, just like the field list. That would give a mismatched column count error. -Cameron ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:352093 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: [5] RE: cfspreadsheet issue when reads dates from spreadsheet
The only thing I might add is that if you are on CF9, you might consider using the new in-memory virtual file system (VFS) to write your new spreadsheet and read it back in (might improve the performance if the spreadsheet is large). -Carl On 2/8/2012 8:34 AM, Hong Chen wrote: I used SpreadSheetRead to read the spreadsheet into memory and used SpreadsheetFormatColumns to format the dob column, then used cfspreadsheet to write a new spreadsheet, last step, I read the new spreadsheet into a query to insert to DB. It was successful, but hope there is a simple way to do it. Thanks. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:349852 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: [5] RE: cfspreadsheet issue when reads dates from spreadsheet
I used SpreadSheetRead to read the spreadsheet into memory and used SpreadsheetFormatColumns to format the dob column, then used cfspreadsheet to write a new spreadsheet, last step, I read the new spreadsheet into a query to insert to DB. It was successful, but hope there is a simple way to do it. Thanks. -Original Message- From: Leigh [mailto:cfsearch...@yahoo.com] Sent: Tuesday, February 07, 2012 9:11 PM To: cf-talk Subject: Re: [5] RE: cfspreadsheet issue when reads dates from spreadsheet If I manually format the column, it still reads yy. I am betting Excel is still registering the cell format as m/d/yy. Try using m/d/;@ How can I programmatically format it before it reads the spreadsheet? I do not think you can convert a spreadsheet object into a query directly. If not, use SpreadSheetRead to load the spreadsheet into memory. Format the column. Then write it back to disk. -Lei ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:349826 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: [5] RE: cfspreadsheet issue when reads dates from spreadsheet
It was successful, but hope there is a simple way to do it. Unfortunately, I think that is as simple as it gets. AFAIK, CF does not let you specify the formatting of query values. It always uses the cell format information. -Lei ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:349829 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: [5] RE: cfspreadsheet issue when reads dates from spreadsheet
I was thinking there were extra read and write action... Anyway my problem resolved now. Thanks for your help. -Original Message- From: Leigh [mailto:cfsearch...@yahoo.com] Sent: Wednesday, February 08, 2012 11:56 AM To: cf-talk Subject: Re: [5] RE: cfspreadsheet issue when reads dates from spreadsheet It was successful, but hope there is a simple way to do it. Unfortunately, I think that is as simple as it gets. AFAIK, CF does not let you specify the formatting of query values. It always uses the cell format information. -Lei ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:349831 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: cfspreadsheet issue when reads dates from spreadsheet
If you are on CF9, you can try using SpreadsheetFormatColumns() to format it
like you want.
something like this (I think) after your cfsrpeeadsheet tag.
cfset spreadSheetFormatColumn(qryResult, {dataformat=mm/dd/yyy}, 1) /
Im assuming DOB is the first column. If that isnt the case, change the last
attribute to the right column number.
.:.:.:.:.:.:.:.:.:.:.:.
Bobby Hartsfield
http://acoderslife.com
http://cf4em.com
-Original Message-
From: Hong Chen [mailto:ho...@cbmiweb.com]
Sent: Tuesday, February 07, 2012 2:42 PM
To: cf-talk
Subject: cfspreadsheet issue when reads dates from spreadsheet
We use cfspreadsheet to upload Excel spreadsheet into database, but
having an issue with reading dates. cfspreadsheet only reads last 2 digits
of the year, e.g. the date in the Excel file is 2/2/1999, the
cfspreadsheet reads it as 2/2/99. Below is my code:
cfset strDir=GetDirectoryFromPath(ExpandPath(*.*)) /uploadFile
cffile action=Upload
filefield=InputFile
destination=#strDir#
nameconflict=Overwrite
mode=757
cfset destFileName = file.ServerDirectory \Book1.xls
cfspreadsheet action=read
src =#destFileName#
columnnames = name,dob
query=qryResult
cfdump var=#qryResult#
The query dumped out like this:
query
DOB NAME
1 2/2/99 John
Anyone can help?
Thanks!
~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:349807
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfspreadsheet issue when reads dates from spreadsheet
something like this (I think) after your cfsrpeeadsheet tag.
cfset spreadSheetFormatColumn(qryResult, {dataformat=mm/dd/yyy}, 1) /
Yep, essentially. But it needs to be applied before reading the sheet into a
query.
-Leig
~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:349809
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: cfspreadsheet issue when reads dates from spreadsheet
Not sure why I was thinking after.
.:.:.:.:.:.:.:.:.:.:.:.
Bobby Hartsfield
http://acoderslife.com
http://cf4em.com
-Original Message-
From: Leigh [mailto:cfsearch...@yahoo.com]
Sent: Tuesday, February 07, 2012 5:42 PM
To: cf-talk
Subject: Re: cfspreadsheet issue when reads dates from spreadsheet
something like this (I think) after your cfsrpeeadsheet tag.
cfset spreadSheetFormatColumn(qryResult, {dataformat=mm/dd/yyy}, 1) /
Yep, essentially. But it needs to be applied before reading the sheet into a
query.
-Leig
~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:349811
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: [5] RE: cfspreadsheet issue when reads dates from spreadsheet
I also have a download function, in there I have formatted the column with
spreadSheetFormatColumn. If I use the downloaded file to upload, it reads 4
digits year. But if I create a new spreadsheet to upload. It only reads the
last 2 digits of year.
-Original Message-
From: Bobby Hartsfield [mailto:bo...@acoderslife.com]
Sent: Tuesday, February 07, 2012 5:59 PM
To: cf-talk
Subject: [5] RE: cfspreadsheet issue when reads dates from spreadsheet
Not sure why I was thinking after.
.:.:.:.:.:.:.:.:.:.:.:.
Bobby Hartsfield
http://acoderslife.com
http://cf4em.com
-Original Message-
From: Leigh [mailto:cfsearch...@yahoo.com]
Sent: Tuesday, February 07, 2012 5:42 PM
To: cf-talk
Subject: Re: cfspreadsheet issue when reads dates from spreadsheet
something like this (I think) after your cfsrpeeadsheet tag.
cfset spreadSheetFormatColumn(qryResult, {dataformat=mm/dd/yyy}, 1) /
Yep, essentially. But it needs to be applied before reading the sheet into a
query.
-Leig
~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:349812
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: [5] RE: cfspreadsheet issue when reads dates from spreadsheet
Sounds like the same solution in both cases unless I'm missing something.
.:.:.:.:.:.:.:.:.:.:.:.
Bobby Hartsfield
http://acoderslife.com
http://cf4em.com
-Original Message-
From: Hong Chen [mailto:ho...@cbmiweb.com]
Sent: Tuesday, February 07, 2012 6:47 PM
To: cf-talk
Subject: RE: [5] RE: cfspreadsheet issue when reads dates from spreadsheet
I also have a download function, in there I have formatted the column with
spreadSheetFormatColumn. If I use the downloaded file to upload, it reads 4
digits year. But if I create a new spreadsheet to upload. It only reads the
last 2 digits of year.
-Original Message-
From: Bobby Hartsfield [mailto:bo...@acoderslife.com]
Sent: Tuesday, February 07, 2012 5:59 PM
To: cf-talk
Subject: [5] RE: cfspreadsheet issue when reads dates from spreadsheet
Not sure why I was thinking after.
.:.:.:.:.:.:.:.:.:.:.:.
Bobby Hartsfield
http://acoderslife.com
http://cf4em.com
-Original Message-
From: Leigh [mailto:cfsearch...@yahoo.com]
Sent: Tuesday, February 07, 2012 5:42 PM
To: cf-talk
Subject: Re: cfspreadsheet issue when reads dates from spreadsheet
something like this (I think) after your cfsrpeeadsheet tag.
cfset spreadSheetFormatColumn(qryResult, {dataformat=mm/dd/yyy}, 1) /
Yep, essentially. But it needs to be applied before reading the sheet into a
query.
-Leig
~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:349813
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: [5] RE: cfspreadsheet issue when reads dates from spreadsheet
Yeah, the solution is the same. You have to apply a format. I also have a download function, in there I have formatted the column with spreadSheetFormatColumn. If I use the downloaded file to upload, it reads 4 digits year. But if I create a new spreadsheet to upload. It only reads the last 2 digits of year. From what I have read on the POI lists, if you do not *explicitly* set a format for the cells (either manually or programatically with CF), Excel assigns its default. For dates it is m/d/yy. That pattern, is what CF uses to format the query values. So with your downloaded file, it uses whatever format you applied. Whereas with your brand new worksheet, there is no format, so it is falls back on the default m/d/yy. -Leig ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:349815 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: [5] RE: cfspreadsheet issue when reads dates from spreadsheet
If I manually format the column, it still reads yy. How can I programmatically format it before it reads the spreadsheet? Thanks, Hong -Original Message- From: Leigh [mailto:cfsearch...@yahoo.com] Sent: Tuesday, February 07, 2012 8:20 PM To: cf-talk Subject: Re: [5] RE: cfspreadsheet issue when reads dates from spreadsheet Yeah, the solution is the same. You have to apply a format. I also have a download function, in there I have formatted the column with spreadSheetFormatColumn. If I use the downloaded file to upload, it reads 4 digits year. But if I create a new spreadsheet to upload. It only reads the last 2 digits of year. From what I have read on the POI lists, if you do not *explicitly* set a format for the cells (either manually or programatically with CF), Excel assigns its default. For dates it is m/d/yy. That pattern, is what CF uses to format the query values. So with your downloaded file, it uses whatever format you applied. Whereas with your brand new worksheet, there is no format, so it is falls back on the default m/d/yy. -Leig ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:349816 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: [5] RE: cfspreadsheet issue when reads dates from spreadsheet
If I manually format the column, it still reads yy. I am betting Excel is still registering the cell format as m/d/yy. Try using m/d/;@ How can I programmatically format it before it reads the spreadsheet? I do not think you can convert a spreadsheet object into a query directly. If not, use SpreadSheetRead to load the spreadsheet into memory. Format the column. Then write it back to disk. -Lei ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:349818 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: [5] RE: cfspreadsheet issue when reads dates from spreadsheet
It worked after I applied date format for the column manually, will try to use SpreadSheetRead tomorrow. Thanks. -Original Message- From: Leigh [mailto:cfsearch...@yahoo.com] Sent: Tuesday, February 07, 2012 9:11 PM To: cf-talk Subject: Re: [5] RE: cfspreadsheet issue when reads dates from spreadsheet If I manually format the column, it still reads yy. I am betting Excel is still registering the cell format as m/d/yy. Try using m/d/;@ How can I programmatically format it before it reads the spreadsheet? I do not think you can convert a spreadsheet object into a query directly. If not, use SpreadSheetRead to load the spreadsheet into memory. Format the column. Then write it back to disk. -Lei ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:349819 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
