OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Data Content can not be loaded error [SOLVED]

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
newbee391
General User
General User


Joined: 04 Jan 2012
Posts: 17
Location: New York, USA

PostPosted: Fri Jan 06, 2012 6:53 am    Post subject: Data Content can not be loaded error [SOLVED] Reply with quote

Greetings:

I have encounter an error while pulling data from a "connected Excel spreadsheet (Excel 2003) file" The error is as follows:
"The Query can not be executed. Its too complex. Only "COUNT(*) is supported.

My objective is to pull data (column named" AMOUNT) from a connected excel file and Total the amounts by vendor with the following code.
SELECT "VENDOR", SUM( "AMOUNT" ) AS "Total" FROM "DBase Report" GROUP BY "VENDOR"

I originally formatted (in Excel) the "Amount" column as currency with two Decimals. I tried changing the data type to number and general and still get the same error.

I thank you in advance for your great support.


Last edited by newbee391 on Fri Jan 06, 2012 10:35 am; edited 1 time in total
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Fri Jan 06, 2012 7:46 am    Post subject: Reply with quote

Problem: You are NOT using a 'real database' . . . but instead, are only, to use your words, "connected Excel spreadsheet ( Excel 2003) file" . . . the ability to use SQL functions is very limited. See post below:

http://www.openoffice.org/dba/specifications/file_based_functions.html

Solution: Connect to a real database . . . where you will have available the SQL functions you want.

  1. Open your current Base file ( spreadsheet file )
  2. Click on the Tables icon on the left, so, you have your list of tables
  3. Create a NEW OpenOffice Base file, and, assign it a name. If it were me, when it asks for you to 'register' it, I would be sure to give it a name . . . so . . . when you open your Calc sheet, or, Writer . . . you will be able to see your NEW tables and queries, and, drag the data to other OpenOffice components.
  4. In the NEW OpenOffice Base file, click on the Tables icon on the left
  5. Drag from the OpenOffice Base Spreadsheet file, the table(s) to the NEW OpenOffice Base database file. Answer the questions as needed, with regard to field names and types
  6. Save the New database

Now, to see documentation on the NEW HSQL database:

http://www.hsqldb.org/doc/guide/ch09.html

For a description of the built-In functions: http://www.hsqldb.org/doc/guide/ch09.html#N1251E

Now, you will have the a 'real database' including the aggregate functions you want, such as:
  1. COUNT
  2. SUM
  3. MIN
  4. MAX
  5. AVG
  6. VAR_POP
  7. VAR_SAMP
  8. STDDEV_POP
  9. STDDEV_SAMP
I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your first post Title ( edit button ) if your issue has been fixed / resolved.
Back to top
View user's profile Send private message
newbee391
General User
General User


Joined: 04 Jan 2012
Posts: 17
Location: New York, USA

PostPosted: Fri Jan 06, 2012 10:36 am    Post subject: Data Content can not be loaded error Reply with quote

Thank you so much for the detail step by step, did everything you suggested and it work like a charm.

Thank you very much!!!!

Very Happy
Back to top
View user's profile Send private message
sy662
Newbie
Newbie


Joined: 27 Jan 2012
Posts: 3

PostPosted: Mon Jan 30, 2012 11:45 am    Post subject: Reply with quote

Sliderule wrote:
Problem: You are NOT using a 'real database' . . . but instead, are only, to use your words, "connected Excel spreadsheet ( Excel 2003) file" . . . the ability to use SQL functions is very limited. See post below:

http://www.openoffice.org/dba/specifications/file_based_functions.html

Solution: Connect to a real database . . . where you will have available the SQL functions you want.

  1. Open your current Base file ( spreadsheet file )
  2. Click on the Tables icon on the left, so, you have your list of tables
  3. Create a NEW OpenOffice Base file, and, assign it a name. If it were me, when it asks for you to 'register' it, I would be sure to give it a name . . . so . . . when you open your Calc sheet, or, Writer . . . you will be able to see your NEW tables and queries, and, drag the data to other OpenOffice components.
  4. In the NEW OpenOffice Base file, click on the Tables icon on the left
  5. Drag from the OpenOffice Base Spreadsheet file, the table(s) to the NEW OpenOffice Base database file. Answer the questions as needed, with regard to field names and types
  6. Save the New database



I tried these steps and kept getting an error when copying. My original file is a CSV not a spreadsheet and it has over 3m rows. I really need to be able to run SQL queries on the data though.

Thanks for your help in advance.

Sy
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Mon Jan 30, 2012 12:33 pm    Post subject: Reply with quote

Sy:

Just so yhou know, in your previous forum post, I suspect ( you did NOT say ) that you were connected to a dBase file ( *.dbf ), to a a database having all the database power of HSQL.

This is because, the ODBC connection, as documented below, has 'limited' functions available. This includes the ONLY aggregate function being COUNT(*), but, it does NOT include SUM aggregate function. Additionally, the ODBC connection to a flat file ( text or dBase file ) does NOT include the ability to link tables.

Now, in your case, because of the size ( number of records ) of your table(s) . . . this is what I would do.

Short English Description: Create a HSQL database, with the data stored in the HSQL location of your choice. You will create the OpenOffice connection with a NEW OpenOffice Base file, as described below. Then, open your current OpenOffice dBase file ( *.odb ) and drag the table from that file to the NEW OpenOffice Base file.

  1. Create a NEW ( additional ) OpenOffice Base file, where the HSQL files will be 'stored'. These files will be ( mydb ), with different file extensions :

    1. mydb.data
    2. mydb.script
    3. mydb.properties
    4. mydb.backup
    Let me assume the location of the file above will be in a directory name of: C:/Program Files/HSQL-18/data/ ( you can change this to whatever you want Smile )

  2. To accomplish the description in number 1. above . . .

    1. Start your OpenOffice application
    2. From the Menu: File -> New -> Database
    3. Click on: Connect to an existing database
    4. From the drop-down, select: JDBC
    5. Click the Next button
    6. In the Datasource URL . . . copy and paste:

      Code:
      hsqldb:file:C:/Program Files/HSQL-18/data/mydb;default_schema=true;shutdown=true


    7. From the above, change C:/Program Fiels/HSQL-18/data/mydb to the directory location and file name ( mydb ) I described in 1. above to your choice
    8. In the JDBC driver class, enter: org.hsqldb.jdbcDriver
    9. Click on the Test class button to confirm it is successful
    10. Click on the Next button
    11. For User name, enter: SA
    12. Press the Test Connect button, to confirm it is successful
    13. Click on the Next button
    14. I strongly suggest you do not change the: Yes, register the database for me
    15. Click the Finish button
    16. Assign a new name for this NEW OpenOffice Base file, the name is your choice
    17. Click the Save button


  3. Now, open ( start ) your original Open Office Base file that is 'connected' to your dBase ( dbf ) file ( you will have TWO OpenOffice Base files open Smile )

  4. Drag the table ( 3 million record table ) to the white table area of your NEW HSQL database

  5. Follow the instruction, regarding fields to 'import' and names etc.

  6. After the import, be sure to save the NEW OpenOffice Base file

  7. Now, you should have a database table and you will have the ability to run an aggregate Query using the SUM function For documentation of HSQL version 1.8, see:

    http://www.hsqldb.org/doc/guide/ch09.html

    For information specifically on Aggregate functions:

    http://www.hsqldb.org/doc/guide/ch09.html#select-section

I hope this helps, please be sure to let me / us know.

Sliderule
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Mon Jan 30, 2012 12:36 pm    Post subject: Reply with quote

Sy:

IMPORTANT NOTE:

If you are 'connected' to a CSV file, and, NOT a dBase ( dbf ) file, send me a PRIVATE MESSAGE ( PM ) . . . maybe I can help in another way.

Sliderule
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group