[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 402
  • Last Modified:

'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?
0
David Williamson
Asked:
David Williamson
  • 16
  • 15
  • 2
1 Solution
 
James RodgersWeb Applications DeveloperCommented:
are you sure your reading data?

from the error it seems as though you are trying to insert data in to the excel sheet
0
 
David WilliamsonIT DirectorAuthor Commented:
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
0
 
James RodgersWeb Applications DeveloperCommented:
>>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
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
James RodgersWeb Applications DeveloperCommented:
can you post teh whole page?
0
 
David WilliamsonIT DirectorAuthor Commented:
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.
0
 
James RodgersWeb Applications DeveloperCommented:
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
0
 
David WilliamsonIT DirectorAuthor Commented:
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" value="#sJobNo#"> AND
                        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" value="#sScope#">
                  </cfquery>
0
 
James RodgersWeb Applications DeveloperCommented:
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?
0
 
David WilliamsonIT DirectorAuthor Commented:
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)?
0
 
James RodgersWeb Applications DeveloperCommented:
>>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?
0
 
David WilliamsonIT DirectorAuthor Commented:
How can I find that out?
0
 
James RodgersWeb Applications DeveloperCommented:
>>Replacing * with say, 6 or 7 items works just fine

how many fields are in the sheet?
0
 
David WilliamsonIT DirectorAuthor Commented:
18
0
 
David WilliamsonIT DirectorAuthor Commented:
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||A||||LBW||TNH||TEM||TWD||101||M|| O||750
0
 
James RodgersWeb Applications DeveloperCommented:
have you tried the query with all 18 named fields instead of *, have you used * in all your queries prior to this?
0
 
David WilliamsonIT DirectorAuthor Commented:
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.
0
 
James RodgersWeb Applications DeveloperCommented:
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>
0
 
David WilliamsonIT DirectorAuthor Commented:
it bombs immediately on the first loop
0
 
David WilliamsonIT DirectorAuthor Commented:
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....
0
 
James RodgersWeb Applications DeveloperCommented:
there is no way to break up the read in to 6 column blocks?


other than that i'm all out of ideas...

sorry
0
 
James RodgersWeb Applications DeveloperCommented:
thats probalby the problem!!

you would need to delete the header/dropdown lists or start reading at line three

0
 
David WilliamsonIT DirectorAuthor Commented:
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.
0
 
David WilliamsonIT DirectorAuthor Commented:
how do I start reading at line 3?
0
 
James RodgersWeb Applications DeveloperCommented:
>>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
0
 
James RodgersWeb Applications DeveloperCommented:
i noticed a total row so you might want to add a where clause to your SQL


<cfquery datasource="sejobs" name="pulled">
SELECT *
FROM "23 jobs$"
WHERE jobno is not null
ORDER BY jobno
</cfquery>
0
 
David WilliamsonIT DirectorAuthor Commented:
'WHERE jobno is not null' did the trick!  Geez!  Any idea why?  I'm baffled...
0
 
James RodgersWeb Applications DeveloperCommented:
>>'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
0
 
anandkpCommented:
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 !

0
 
anandkpCommented:
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
0
 
David WilliamsonIT DirectorAuthor Commented:
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!
0
 
James RodgersWeb Applications DeveloperCommented:
>>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
0
 
David WilliamsonIT DirectorAuthor Commented:
Sorry for the delay in assigning points.  I've been busy at work.
0
 
James RodgersWeb Applications DeveloperCommented:
np... it happens

thanks for the points


glad i could help
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 16
  • 15
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now