[Solved] dbf or ods

dBase, Calc, CSV, MS ACCESS, MySQL, PostgrSQL, OTHER

[Solved] dbf or ods

Postby capemayal1 » Tue Jul 05, 2011 12:47 am

I've successfully used a CALC spreadsheet, saved as a DBF file, to import into Base for query and reporting purposes.

The CALC sheet has calculations on it, particularly date addition, which does work.

After connecting to the DBF file in Base, and doing what I need to do, I then return to the DBF file in CALC, with the intention of adding more records.

However, even though I've copied the date calculations from the 1st record down to the last row of records I expect (the rows actually haven't been filled in, except for the calculations)

When I return to the spreadsheet the results of the calculations are still there, but the formula's are no longer there. In order to enter new records, or update the current records, the formula must be entered again.

I hope I made this clear.

The same thing happens if I connect as a spreadsheet.

Is there a solution?

Thanks,
Al
Last edited by capemayal1 on Mon Nov 21, 2011 2:59 pm, edited 1 time in total.
LO 4.0
JDBC DATABASE
HSQLDB 2.2.8
Ubuntu 12.10 64-bit
HP DV6 64-bit Laptop
capemayal1
 
Posts: 78
Joined: Fri Dec 31, 2010 5:16 pm

Re: dbf or ods

Postby Villeroy » Tue Jul 05, 2011 11:41 am

Any database engine can do the date calculation properly, but not the built-in HSQLDB 1.8 nor dBase. In Base you can get today's date, time or time-stamp and you can get the difference between two times as integer numbers of days, month, years, hours, minutes. But you can not get another date/time from one date/time and some offset.
 Edit: I forgot a rather complex work-around for the built-in HSQLDB 1.8: http://www.oooforum.org/forum/viewtopic ... 693#434199 



You may collect the raw data in a database, and do the time calculation in Calc.
Drag a table or a filtered/sorted query from the data source window into Calc.
Apply any formatting, charts and formulas you want.
Call Data>Define... select your import range and set the additional options "Insert/Remove Cells" and "Keep Formatting". You may also decide to not store the imported data in the spreadsheet which enforces a refresh on open.
No the size of your formula ranges will adjust to the resizing import ranges if the formula ranges are adjacent to the import range.
[Tutorial] Using registered datasources in Calc
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28536
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to External Data Sources

Who is online

Users browsing this forum: No registered users and 1 guest