Solved

'too much data' error querying Excel file

Posted on 2003-12-11
33
369 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
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
 
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

One of the typical problems I have experienced is when you have to move a web server from one hosting site to another. You normally prepare all on the new host, transfer the site, change DNS and cross your fingers hoping all will be ok on new server…
Meet the world's only “Transparent Cloud™” from Superb Internet Corporation. Now, you can experience firsthand a cloud platform that consistently outperforms Amazon Web Services (AWS), IBM’s Softlayer, and Microsoft’s Azure when it comes to CPU and …
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

737 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