Solved

'too much data' error querying Excel file

Posted on 2003-12-11
33
354 Views
Last Modified: 2013-12-24
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
Comment
Question by:theamzngq
  • 16
  • 15
  • 2
33 Comments
 
LVL 25

Expert Comment

by:James Rodgers
ID: 9923766
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
 
LVL 2

Author Comment

by:theamzngq
ID: 9924076
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
 
LVL 25

Expert Comment

by:James Rodgers
ID: 9924154
>>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
 
LVL 25

Expert Comment

by:James Rodgers
ID: 9924161
can you post teh whole page?
0
 
LVL 2

Author Comment

by:theamzngq
ID: 9924186
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
 
LVL 25

Expert Comment

by:James Rodgers
ID: 9924232
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
 
LVL 2

Author Comment

by:theamzngq
ID: 9924261
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
 
LVL 25

Expert Comment

by:James Rodgers
ID: 9924343
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
 
LVL 2

Author Comment

by:theamzngq
ID: 9924389
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
 
LVL 25

Expert Comment

by:James Rodgers
ID: 9924429
>>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
 
LVL 2

Author Comment

by:theamzngq
ID: 9924442
How can I find that out?
0
 
LVL 25

Expert Comment

by:James Rodgers
ID: 9924465
>>Replacing * with say, 6 or 7 items works just fine

how many fields are in the sheet?
0
 
LVL 2

Author Comment

by:theamzngq
ID: 9924491
18
0
 
LVL 2

Author Comment

by:theamzngq
ID: 9924501
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
 
LVL 25

Expert Comment

by:James Rodgers
ID: 9924511
have you tried the query with all 18 named fields instead of *, have you used * in all your queries prior to this?
0
 
LVL 2

Author Comment

by:theamzngq
ID: 9924523
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 25

Expert Comment

by:James Rodgers
ID: 9924567
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
 
LVL 2

Author Comment

by:theamzngq
ID: 9924586
it bombs immediately on the first loop
0
 
LVL 2

Author Comment

by:theamzngq
ID: 9924611
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
 
LVL 25

Expert Comment

by:James Rodgers
ID: 9924621
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
 
LVL 25

Expert Comment

by:James Rodgers
ID: 9924625
thats probalby the problem!!

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

0
 
LVL 2

Author Comment

by:theamzngq
ID: 9924654
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
 
LVL 2

Author Comment

by:theamzngq
ID: 9924659
how do I start reading at line 3?
0
 
LVL 25

Expert Comment

by:James Rodgers
ID: 9924719
>>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
 
LVL 25

Accepted Solution

by:
James Rodgers earned 250 total points
ID: 9924820
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
 
LVL 2

Author Comment

by:theamzngq
ID: 9924904
'WHERE jobno is not null' did the trick!  Geez!  Any idea why?  I'm baffled...
0
 
LVL 25

Expert Comment

by:James Rodgers
ID: 9924924
>>'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
 
LVL 17

Expert Comment

by:anandkp
ID: 9925964
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
 
LVL 17

Expert Comment

by:anandkp
ID: 9925976
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
 
LVL 2

Author Comment

by:theamzngq
ID: 9926579
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
 
LVL 25

Expert Comment

by:James Rodgers
ID: 9927920
>>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
 
LVL 2

Author Comment

by:theamzngq
ID: 10387824
Sorry for the delay in assigning points.  I've been busy at work.
0
 
LVL 25

Expert Comment

by:James Rodgers
ID: 10391839
np... it happens

thanks for the points


glad i could help
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

This is a guide to setting up a new WHM/cPanel Server to be used for web hosting accounts. It is intended for web hosting company administrators and dedicated server owners. For under $99 per month (considering normal rate of Big Data Cetnters like …
In our day to day coding, how many times have we come across a necessity to check whether a URL is a broken link or not? For those of you that answered countless and are using ColdFusion like myself, then this article is for you.  It will show yo…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now