'too much data' error querying Excel file
Doing a query on an Excel file, I get the following Cold Fusion error:
[MERANT][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Excel Driver] The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data.
Is there a limit to the number of records that can be returned from a query? Or is it a Microsoft limit via ODBC (System DSN) that won't allow you to retrieve so many records?
[MERANT][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Excel Driver] The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data.
Is there a limit to the number of records that can be returned from a query? Or is it a Microsoft limit via ODBC (System DSN) that won't allow you to retrieve so many records?
ASKER
Here is the query:
<cfquery datasource="sejobs" name="pulled">
SELECT * FROM "23 jobs$"
ORDER BY jobno
</cfquery>
a screenshot of the error:
www.davesrentals.com/error.jpg
<cfquery datasource="sejobs" name="pulled">
SELECT * FROM "23 jobs$"
ORDER BY jobno
</cfquery>
a screenshot of the error:
www.davesrentals.com/error.jpg
>>Is there a limit to the number of records that can be returned from a query?
not sure what it is but it is well over 100,000
i have tables with over 1M records and i have never had a problem, ecxcept for timeouts
there is a limit on an individual record, it's 2k for access not sure for other DB i think its a BLOB for most
not sure what it is but it is well over 100,000
i have tables with over 1M records and i have never had a problem, ecxcept for timeouts
there is a limit on an individual record, it's 2k for access not sure for other DB i think its a BLOB for most
can you post teh whole page?
ASKER
the whole page of code? The query I posted above is in the first couple lines of the page; it doesn't even get beyond that before throwing an error.
looking at teh image is says the error is on line 7
line 7 is a comment so i wanted to look at the page to see iif there is a another reason for the error
if you can post lines 1-50 of the page
line 7 is a comment so i wanted to look at the page to see iif there is a another reason for the error
if you can post lines 1-50 of the page
ASKER
Got it. See it below. Just a note on what I'm doing: I am developing an intranet site for the company for all of their data. They currently are storing it all in Excel sheets. As parts of the new site are completed, we stop using the sheet that was doing the same job before. In the meantime, i have to write code to 'sync' the spreadsheet with the database until such time as testing is complete and we can dump the spreadsheet all together and use the new site module. Hopefully, that makes sense. So, the code below (what you see of it) goes through each row and separates it out into the various tables in my database.
<!---get everything from jobs sheet--->
<cfquery datasource="sejobs" name="pulled">
SELECT * FROM "23 jobs$"
ORDER BY jobno
</cfquery>
<!---loop through all row in sheet--->
<cfloop query="pulled">
<!---parse jobno, jobname, dept from sJobno--->
<cfset sDept = #left(pulled.jobno,2)#>
<cfset sJobNo = #mid(pulled.jobno,3,5)#>
<cfset sScope = #right(pulled.jobno,2)#>
<!---set status variable--->
<cfswitch expression="pulled.status"
<cfcase value="A">
<cfset sStatus = 'Active'>
</cfcase>
<cfcase value="I">
<cfset sStatus = 'Inactive'>
</cfcase>
<cfcase value="P">
<cfset sStatus = 'Processing'>
</cfcase>
<cfcase value="D">
<cfset sStatus = 'Done and Invoiced'>
</cfcase>
<cfcase value="C">
<cfset sStatus = 'Cancelled'>
</cfcase>
<cfcase value="L">
<cfset sStatus = 'Lot Fee'>
</cfcase>
</cfswitch>
<!---make sure that the right data has been parsed; each item should be a specific length--->
<cfif len(sDept) EQ 2 AND len(sJobNo) EQ 5 AND len(sScope) EQ 2>
<!---check for existing job--->
<cfquery datasource="wrightdb" name="existsJ">
SELECT JobsID
WHERE
JobNo = <cfqueryparam cfsqltype="cf_sql_integer"
Dept = <cfqueryparam cfsqltype="cf_sql_char" value="#sDept#">
</cfquery>
<!---if there is an existing job in the table that matches the sheet...--->
<cfif existsJ.RecordCount>
<!---check for existing JobItem--->
<cfquery datasource="wrightdb" name="existsJJI">
SELECT JobItemsID FROM JobItems
WHERE
Scope = <cfqueryparam cfsqltype="cf_sql_integer"
</cfquery>
<!---get everything from jobs sheet--->
<cfquery datasource="sejobs" name="pulled">
SELECT * FROM "23 jobs$"
ORDER BY jobno
</cfquery>
<!---loop through all row in sheet--->
<cfloop query="pulled">
<!---parse jobno, jobname, dept from sJobno--->
<cfset sDept = #left(pulled.jobno,2)#>
<cfset sJobNo = #mid(pulled.jobno,3,5)#>
<cfset sScope = #right(pulled.jobno,2)#>
<!---set status variable--->
<cfswitch expression="pulled.status"
<cfcase value="A">
<cfset sStatus = 'Active'>
</cfcase>
<cfcase value="I">
<cfset sStatus = 'Inactive'>
</cfcase>
<cfcase value="P">
<cfset sStatus = 'Processing'>
</cfcase>
<cfcase value="D">
<cfset sStatus = 'Done and Invoiced'>
</cfcase>
<cfcase value="C">
<cfset sStatus = 'Cancelled'>
</cfcase>
<cfcase value="L">
<cfset sStatus = 'Lot Fee'>
</cfcase>
</cfswitch>
<!---make sure that the right data has been parsed; each item should be a specific length--->
<cfif len(sDept) EQ 2 AND len(sJobNo) EQ 5 AND len(sScope) EQ 2>
<!---check for existing job--->
<cfquery datasource="wrightdb" name="existsJ">
SELECT JobsID
WHERE
JobNo = <cfqueryparam cfsqltype="cf_sql_integer"
Dept = <cfqueryparam cfsqltype="cf_sql_char" value="#sDept#">
</cfquery>
<!---if there is an existing job in the table that matches the sheet...--->
<cfif existsJ.RecordCount>
<!---check for existing JobItem--->
<cfquery datasource="wrightdb" name="existsJJI">
SELECT JobItemsID FROM JobItems
WHERE
Scope = <cfqueryparam cfsqltype="cf_sql_integer"
</cfquery>
have you tried the query in a page on its own to see if the query bomb out?
how many records are in the sheet?
what size is each row?
how many records are in the sheet?
what size is each row?
ASKER
query on its own page still bombs. When I reduce the number of items queried, however, it succeeds. Replacing * with say, 6 or 7 items works just fine. But I need everything!
Sheet has 2380 rows, the largest cell probably has no more than 175 characters, although 80% of them are much smaller, ranging from 1-30ish
I did a 'sync' with another sheet that has over 3000 rows, and never had this error. I wonder if installing the CFMX 6.1 update would help (I am reaching, I know)?
Sheet has 2380 rows, the largest cell probably has no more than 175 characters, although 80% of them are much smaller, ranging from 1-30ish
I did a 'sync' with another sheet that has over 3000 rows, and never had this error. I wonder if installing the CFMX 6.1 update would help (I am reaching, I know)?
>>the largest cell probably has no more than 175 characters
but what about the total record? does it exceed 2k on even one of them?
but what about the total record? does it exceed 2k on even one of them?
ASKER
How can I find that out?
>>Replacing * with say, 6 or 7 items works just fine
how many fields are in the sheet?
how many fields are in the sheet?
ASKER
18
ASKER
Here is a sample, columns separated by '||'
SN2364401||Sunflower Retaining Walls||2'-8" and 4'-0" Retainer with 2:1 Sloping Toe Soil and 6'-0" Fencewall Atop and 6'-0" Blockwall with Normal and Eccentric Footings, All on Critically Expansive Soil||Hirschi Masonry||12/1/03||12/8/03|
SN2364401||Sunflower Retaining Walls||2'-8" and 4'-0" Retainer with 2:1 Sloping Toe Soil and 6'-0" Fencewall Atop and 6'-0" Blockwall with Normal and Eccentric Footings, All on Critically Expansive Soil||Hirschi Masonry||12/1/03||12/8/03|
have you tried the query with all 18 named fields instead of *, have you used * in all your queries prior to this?
ASKER
I have tried the query with several, but not all, fields. I added them one at a time. Each time the query worked, I added another field. Before I gotten very far, the query started bombing again.
the only thing i can think of is that one or more ov the fields is larger that you think and on their own the are ok but combined they are over the 2k mark
this is going to be tedious but give this code a shot
see if it bombs immediately or on a specific record
<cfoutput>
<cfloop index="idx" from="1" to="2380">
#idx#
<!---get everything from jobs sheet--->
<cfquery datasource="sejobs" name="pulled" maxrows="#idx#">
SELECT * FROM "23 jobs$"
ORDER BY jobno
</cfquery>
</cfloop>
</cfoutput>
this is going to be tedious but give this code a shot
see if it bombs immediately or on a specific record
<cfoutput>
<cfloop index="idx" from="1" to="2380">
#idx#
<!---get everything from jobs sheet--->
<cfquery datasource="sejobs" name="pulled" maxrows="#idx#">
SELECT * FROM "23 jobs$"
ORDER BY jobno
</cfquery>
</cfloop>
</cfoutput>
ASKER
it bombs immediately on the first loop
ASKER
the first 3 lines of the spreadsheet don't contain any data; the first 2 are just header cells, the 3rd has cells that have sort drop-down lists. Perhaps that is a clue....
there is no way to break up the read in to 6 column blocks?
other than that i'm all out of ideas...
sorry
other than that i'm all out of ideas...
sorry
thats probalby the problem!!
you would need to delete the header/dropdown lists or start reading at line three
you would need to delete the header/dropdown lists or start reading at line three
ASKER
I could do that and I even tried it, but when i tried to query just the description field, it started bombing again.
The only other thing I could mention is that the sheet has macros as well as having multiple sheets, although I'm only dealing with one at the moment; I don't know if that makes a difference when the sheet is read as a datasource.
If you would like to download it and look at it, I'll post a truncated version of the file here:
www.davesrentals.com/excel.xls
This has only one sheet in it, and only 10 records.
The only other thing I could mention is that the sheet has macros as well as having multiple sheets, although I'm only dealing with one at the moment; I don't know if that makes a difference when the sheet is read as a datasource.
If you would like to download it and look at it, I'll post a truncated version of the file here:
www.davesrentals.com/excel.xls
This has only one sheet in it, and only 10 records.
ASKER
how do I start reading at line 3?
>>how do I start reading at line 3?
my fault i was thinking about output, when you do output you can have a start row, startRow="3" you will probably have to delete the headers and dropdowns
also the page has linked data, that might be an issue also
try using a copy of the file without the headers an dropdowns an see if that works delete lines 2, 3 and 4. Leave line 1 you will need some kind of reference for your ORDER BY clause
my fault i was thinking about output, when you do output you can have a start row, startRow="3" you will probably have to delete the headers and dropdowns
also the page has linked data, that might be an issue also
try using a copy of the file without the headers an dropdowns an see if that works delete lines 2, 3 and 4. Leave line 1 you will need some kind of reference for your ORDER BY clause
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
'WHERE jobno is not null' did the trick! Geez! Any idea why? I'm baffled...
>>'WHERE jobno is not null' did the trick! Geez! Any idea why? I'm baffled...
makes two of us
i guess that when trying to read the data it was pulling in all 65000+ rows and was bombing out, but with the where clause it was filtering it down to the 2000+ records and that was something it could handle
makes two of us
i guess that when trying to read the data it was pulling in all 65000+ rows and was bombing out, but with the where clause it was filtering it down to the 2000+ records and that was something it could handle
Use MAXROWS to filter out the required number of records from a query - as theres no point in getting all of them. set the value of maxrows to the size of ur page & if u want u cld also use BLOCKFACTOR & increase the performance.
<CFQUERY NAME="Qry_Name" DATASOURCE="My_Dsn" MAXROWS="2000">
Select .....
</CFQUERY>
just a thought !
<CFQUERY NAME="Qry_Name" DATASOURCE="My_Dsn" MAXROWS="2000">
Select .....
</CFQUERY>
just a thought !
Jester & theamzngq : i wonder how wld jobno be ever null !
that seems to me like PK for the table ... just wondering ... if something is weird here
that seems to me like PK for the table ... just wondering ... if something is weird here
ASKER
the 65000 rows thing sounds pretty good to me...I'll do a couple of more tests tomorrow once I get to work before I close this thing out...thanks for hanging in there, guys!
>>Jester & theamzngq : i wonder how wld jobno be ever null !
there was at least one line that had no jobno, so i just suggested to use a where to filter it out
there was at least one line that had no jobno, so i just suggested to use a where to filter it out
ASKER
Sorry for the delay in assigning points. I've been busy at work.
np... it happens
thanks for the points
glad i could help
thanks for the points
glad i could help
from the error it seems as though you are trying to insert data in to the excel sheet