CFspreadsheet Issue
Hello, I have a question about CFspreadsheet I'm reading a spreadsheet and inserting the data into a SQL server DBeverything works fine unless 1 of my columns in the spreadsheet is blank. I'm getting the following error: Element PHONE is undefined in EXCELQUERYBYNAME. I tried the put a cfif isdefined to see if that would solve it but no lucksame error message. How can I handle this issue? My cfquery is below: INSERT into contactrecord ( contact_date, fname, lname, address, city, state, zip, county, phone, cell, fax, title, organization, email, general_comments, fname2, lname2, mailing, input_by, input_date, input_time, input_by_id, country, sh_rating ) Values( '#Trim(excelQueryByName.contact_date)#', '#Trim(excelQueryByName.fname)#', '#Trim(excelQueryByName.lname)#', '#Trim(excelQueryByName.address)#', 'Trim(excelQueryByName.city)#', '#Trim(excelQueryByName.state)#', '#Trim(excelQueryByName.zip)#', '#Trim(excelQueryByName.county)#', '#Trim(excelQueryByName.phone)#', '#Trim(excelQueryByName.cell)#', '#Trim(excelQueryByName.fax)#', '#Trim(excelQueryByName.title)#', '#Trim(excelQueryByName.organization)#', '#Trim(excelQueryByName.email)#', '#Trim(excelQueryByName.general_comments)#', '#Trim(excelQueryByName.fname2)#', '#Trim(excelQueryByName.lname2)#', '#Trim(excelQueryByName.mailing)#', '#Trim(excelQueryByName.input_by)#', '#Trim(excelQueryByName.input_date)#', '#Trim(excelQueryByName.input_time)#', '#Trim(excelQueryByName.input_by_id)#', '#Trim(excelQueryByName.country)#', '#Trim(excelQueryByName.sh_rating)#' ) Thanks in Advance. ~| 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:352118 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 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: 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 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: > > > src="#ExpandPath("Spreadsheets/#ClientFile#")#" /> > > > > > > > INSERT INTO BarCodes > (Code, BarCode, ResortID) > VALUES > ( > > > > ) > > > > The error is on 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
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 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
Yeah I picked up on that and still the same issue. On Aug 10, 2012, at 1:00 PM, "Sandra Clark" 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
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: INSERT INTO BarCodes (Code, BarCode, ResortID) VALUES ( ) The error is on 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
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: INSERT INTO BarCodes (Code, BarCode, ResortID) VALUES ( ) The error is on 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:352085 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 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: [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 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 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
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
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
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
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. > 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
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. > 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: 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. > 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: cfspreadsheet issue when reads dates from spreadsheet
> something like this (I think) after your cfsrpeeadsheet tag. > 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
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. 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 to upload Excel spreadsheet into database, but having an issue with reading dates. only reads last 2 digits of the year, e.g. the date in the Excel file is 2/2/1999, the reads it as 2/2/99. Below is my code: 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
cfspreadsheet issue when reads dates from spreadsheet
We use to upload Excel spreadsheet into database, but having an issue with reading dates. only reads last 2 digits of the year, e.g. the date in the Excel file is 2/2/1999, the reads it as 2/2/99. Below is my code: 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:349804 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm