Creating a DB flle from a OO spreadsheet

dBase, Calc, CSV, MS ACCESS, MySQL, PostgrSQL, OTHER
Post Reply
waynec444
Posts: 84
Joined: Wed Jan 21, 2015 6:09 am

Creating a DB flle from a OO spreadsheet

Post by waynec444 »

I have followed the procedure several times and get the same result.

Create a database from a spreadsheet.

It only imports the first 50 lines (approximately) and that is all (out of 40,000+ lines).

What am I doing wrong? Can't figure it out.
Open Office 4.1.10 on Windows Vista
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Creating a DB flle from a OO spreadsheet

Post by FJCC »

What leads you to believe that only the first 50 rows are imported? If you run the query

Code: Select all

SELECT COUNT(*) FROM "MyTable"
(where MyTable is replaced with the name of your spreadsheet table) what it the result?
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
User avatar
robleyd
Moderator
Posts: 5055
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Creating a DB flle from a OO spreadsheet

Post by robleyd »

Is there an asterisk * after the number of records? If so, it means there are more records to display; try clicking on the >| icon to go to the last record and see what happens.
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Creating a DB flle from a OO spreadsheet

Post by Villeroy »

Base does not load the entire record set because it may be large. 40,000 is not large. It may be many millions. It makes no sense to load huge chunks of data for scrolling and viewing. From a database you query the data and/or calculated results you are interested in right now.
Copy & paste from spreadsheet may give inconsistent results unless you create the table definitions before pasting. Creating the tables on the fly from clipboard content may result in text columns without any numbers or date/time values.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
waynec444
Posts: 84
Joined: Wed Jan 21, 2015 6:09 am

Re: Creating a DB flle from a OO spreadsheet

Post by waynec444 »

I have been entering data in calc intending to move it to base afterwards. I will NOT be using the calc file after moving the info to base. I will be making any changes in base, not in calc.

The importing into base stops after the few lines. The file is only 2kb so that indicates that ONLY those first 50 or so lines were imported into base.

Now, some of the entries in calc DO include an asterisk in them so that could be a problem based on your reply. However, in doing tests earlier with these same lines with asterisks in them it did NOT cause a problem in importing into base. ALL of the lines were imported, including those with asterisks and all lines after the first line with an asterisk.

The asterisks are there to denote that those entries are different from the others and ultimately when printed out there will be a note at the bottom of the page(s) explaining those entries with asterisks.

Finally, NOW the database I created (from the main menu when starting OO)cannot be opened as i was able to do before (seeing only 50 lines). When attempting to open it. All I get is the page asking me how to create a database.
Open Office 4.1.10 on Windows Vista
waynec444
Posts: 84
Joined: Wed Jan 21, 2015 6:09 am

Re: Creating a DB flle from a OO spreadsheet

Post by waynec444 »

BTW, i seem to remember that more than those 50 lines were imported in tests before with asterisks in some of the lines. ALL lines I had at that time were imported.
Open Office 4.1.10 on Windows Vista
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Creating a DB flle from a OO spreadsheet

Post by Villeroy »

We are talking about this navigation tool, not about anything in your data. The tool indicates that the row cursor is on row #1 of 67 rows which are currently loaded. The asterisk indicates that the record set has an unknown number of rows beyond 67. You may enter a known record number into the box where the 1 is in order to navigate to that record number. The buttons next to it navigate to first record, previous record, next record, last record and to the very last record where you find a single blank row to add a new one. If you navigate to the last record, you will see the record number of the last record and the asterisk disappears.
Attachments
Base record marker with asterisk
Base record marker with asterisk
Base_Asterisk.png (6.84 KiB) Viewed 26782 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
waynec444
Posts: 84
Joined: Wed Jan 21, 2015 6:09 am

Re: Creating a DB flle from a OO spreadsheet

Post by waynec444 »

As I said, it is NOT importing more than about 50 lines. That is ALL that is there. Proof of t his is that the file size is only 2k. There is no way that 40,000+ lines are contained in 2k.

But now, I cannot even open the 50+ lines file.
Open Office 4.1.10 on Windows Vista
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Creating a DB flle from a OO spreadsheet

Post by Villeroy »

waynec444 wrote:I have followed the procedure several times and get the same result.
Which procedure?
Create a database from a spreadsheet.
How do you do that?
It only imports the first 50 lines (approximately) and that is all (out of 40,000+ lines).

What am I doing wrong? Can't figure it out.
We have no idea what you are talking about because you do not tell us what you are doing nor do you tell us what happens (error message?) nor do you upload any relevant data.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
waynec444
Posts: 84
Joined: Wed Jan 21, 2015 6:09 am

Re: Creating a DB flle from a OO spreadsheet

Post by waynec444 »

I have a file of over 40,000 lines in Calc.

I am trying to import this data into Base several times. At first i only got 50 lines imported and was able to open the db and see them. That is how i know that is what is importing.

Several times since i have done:

In Base, Connect to an existing database (the Calc spreadsheet) where only 50 lines are imported).

After successfully following this, I now try to open the newly created database from the menu shown when first opening OO and all I get is a screen with two options. It does not open the file. It seems as if I have done as it asks how I want to create a database.

Don't know what other info you need. Please specify. Thanks.
Open Office 4.1.10 on Windows Vista
waynec444
Posts: 84
Joined: Wed Jan 21, 2015 6:09 am

Re: Creating a DB flle from a OO spreadsheet

Post by waynec444 »

correction - left a word out.\

It seems as if I have done NOTHING as it asks how I want to create a database (instead of opening the database I created).
Open Office 4.1.10 on Windows Vista
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Creating a DB flle from a OO spreadsheet

Post by Villeroy »

waynec444 wrote:
In Base, Connect to an existing database (the Calc spreadsheet) where only 50 lines are imported).
This imports nothing. All your data are in the spreadsheet and Base treats the named database ranges and the used areas of the sheets as if they were database tables.
Base is not a database. It is a tool to work with connected databases. Lots of things may happen if the connected database is a spreadsheet because a spreadsheet is too far away from being a database. Without having your data it is impossible to tell why you do not see all the rows. 100% of all spreadsheet databases I have seen are inconsistent. Just open the spreadsheet and look what is different between the last "imported" row and the following.
I have been entering data in calc intending to move it to base afterwards.
Are you saying that you and your co-workers typed 40,000 records into a spreadsheet?
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
waynec444
Posts: 84
Joined: Wed Jan 21, 2015 6:09 am

Re: Creating a DB flle from a OO spreadsheet

Post by waynec444 »

Yes. Entered 40,000+ lines in the spreadsheet. Once i transfer the data to a database I want no more to do with the spreadsheet. I will then work exclusively with the database (modifying, etc.)

I have also tried creating a new database from scratch and then importing the info from a csv file. When I try this it is giving me an error message saying there is no Java connected to it. The database cannot be established until this is fixed.

So as instructed I went to Tools, Option, OpenOffice, Java. There it displays the Java on my computer and it is highlighted. Continuing on I get another error message saying to do it again. I must not be doing something.

I had no idea this would be so complicated. I am a novice. I thought that once i put the data into a database I could just work with it alone.

Thanks to all those who are trying to help.

ALL I want to do now is just get the data transferred to a database so I can work with it there. I thought this would be very simple.
Open Office 4.1.10 on Windows Vista
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Creating a DB flle from a OO spreadsheet

Post by Villeroy »

AOO 4.1.0 on Windows VISTA? Really? AOO for Windows is a 32-bit application.

1. Upgrade to 4.1.9 which is the 9th revision of 4.1.0 and the "best" version you can get for Vista.
2. Go to https://adoptopenjdk.net/ and download an old 32-bit version of Java. Version 8 should be adequate, version 11 should work as well. I don't use AOO.
Normal Windows users should switch to a 64-bit version of LibreOffice together with a recent 64-bit JRE.

No, you must NOT export to csv. csv makes everything even more difficult. You've got to get your sheet data clean and consistent before you will be able to copy clean and consistent raw data from Calc into Base connected to HSQL or any other relational database. This may take more than a weekend of labour.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
waynec444
Posts: 84
Joined: Wed Jan 21, 2015 6:09 am

Re: Creating a DB flle from a OO spreadsheet

Post by waynec444 »

1. Upgrade to 4.1.9 which is the 9th revision of 4.1.0 and the "best" version you can get for Vista.
Already have.
Java. Version 8 should be adequate
OO says I have 8.x (don't remember the last part). it gave me that info when trying to create a stand-alone database not connected to the spreadsheet. The error message says that I don't have Java but the program says I do and (8.x too). Only one Java program is shown.
No, you must NOT export to csv. csv makes everything even more difficult. You've got to get your sheet data clean and consistent before you will be able to copy clean and consistent raw data from Calc into Base connected to HSQL or any other relational database. This may take more than a weekend of labour.
I have both the main Calc database and have also exported to CSV to try it that way too.

Calc is still intact. I do not know what you mean by "clean" but it just contains data that I entered. Did not make any changes or modifications.
Open Office 4.1.10 on Windows Vista
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Creating a DB flle from a OO spreadsheet

Post by Villeroy »

You have a 64-bit Windows, right?
Your 64-bit WIndows has a directory "C:\Program Files" and another one "C:\Program Files (x86)", right?
In the x86 folder you find OpenOffice together with other 32-bit programs but no Java folder, right?
The Java folder is in "C:\Program Files" because you installed the suggested Java from java.com, right?
You can find other JRE downloads on java.com, among them a 32-bit version and you can install the "alternative" JRE I have suggested. The most important thing is, that it needs to be the 32-bit version which is installed in "C:\Program Files (x86)", not the one in "C:\Program Files". IF (and only if) your system happens to be a 64-bit WIndows, "C:\Program Files" would be the right folder for all programs since there is no distinction between 64-bit and 32-bit. These old system variants are limited to 32-bit anyway.

WHY IN HELL DO WE HAVE TO EXPLAIN THIS TWICE A FUCKING WEEK?
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Creating a DB flle from a OO spreadsheet

Post by Villeroy »

Manually edited spreadsheets are a swamp of unstructured data because every single cell can take any type of value. Rectangles of cells may represent a normalized table or some other 2-dimensional area.

A database is organized in normalized tables (look up "database normalization").
Normalized tables have a fixed set of columns. Each column accepts one particular type of data (integers, decimals, text, dates, times, timestamps, pictures, ...). You never add any columns while adding new data.
Normalized tables have a variable amount of rows. Each row is stored directly to disk after editing. New data always are appended to new rows. You never have to store the whole thing. You never see a save-as dialog while working with the data of a database.
Any row with invalid or missing data in any of the columns will not be stored. If your rectangle of single sheet cells has a text 2,23.897 (a mis-typed decimal number, thus a text) in a column, it will be imported as a column of text values or not at all.
You can (and should) define database columns as mandatory (not optional, not empty), so a row will not be stored if a mandatory column has no value (e.g. do not store any person without forename AND surname AND birth date). Manually typed spreadsheets (those that are not derived from database imports) have missing values where there should be no missing values. Instead of a birth date there may be a text "not availlable yet" instead of a valid date value.
A database will not accept these inconsistencies. The import will fail, no matter how hard you try.
Did you find out what is different in row 51 of your sheet?
Have you ever visited the sub-forum for Base tutorials?
Did you download some example databases from this forum?
Do you read any resources we link here?
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
waynec444
Posts: 84
Joined: Wed Jan 21, 2015 6:09 am

Re: Creating a DB flle from a OO spreadsheet

Post by waynec444 »

"You have a 64-bit Windows, right?"

No, 32-bit.

"has a directory "C:\Program Files" and another one "C:\Program Files (x86)", right?"

Has the first one only.

"In the x86 folder you find OpenOffice together with other 32-bit programs but no Java folder, right?"

There iS a Java directory.

"The Java folder is in "C:\Program Files" because you installed the suggested Java from java.com, right?"

Two versions are in the Java directory. One of which OO Base recognizes and is listed in Base.
JRE1.8.0_201 (dated 2015)
JRE1.8.0_01 (dated 2021) (selected when establishing database.

"You can find other JRE downloads on java.com, among them a 32-bit version and you can install the "alternative" JRE I have suggested. The most important thing is, that it needs to be the 32-bit version which is installed in "C:\Program Files (x86)", not the one in "C:\Program Files". IF (and only if) your system happens to be a 64-bit WIndows, "C:\Program Files" would be the right folder for all programs since there is no distinction between 64-bit and 32-bit. These old system variants are limited to 32-bit anyway."

Update:

I followed an online tutorial to import into the database from Calc.

With a little bit of effort I was able to import ALL of the data.

But, there is a problem seeing ALL of the lines.

When attempting to scroll i can only use the down arrow to SLOWLY scroll down. Doing it this way will take hours. Trying to use the bar on the right does not work. Clicking and holding down just above the down arrow moves it but again it will take hours.

How can I scroll all the way through the lines quickly?

I also want to sort, search, rename text, display lines with certain text, etc. but see no way to do it. It seems that once the data is there not much can be done with it. Calc has the rename function but base does not seem to have it.
Open Office 4.1.10 on Windows Vista
waynec444
Posts: 84
Joined: Wed Jan 21, 2015 6:09 am

Re: Creating a DB flle from a OO spreadsheet

Post by waynec444 »

Yes I do read about what to do before posting. Not always finding what I need or what works when I try something.

I only post when I have a situation I cannot find an answer to. It may be there but not always found when searching.

Example: I spent more time trying to find how to import the data and finally found something that worked.

See my other post about not finding anything about various things I can do with the data (such as sorting, renaming, etc.).
Open Office 4.1.10 on Windows Vista
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Creating a DB flle from a OO spreadsheet

Post by Villeroy »

waynec444 wrote:Example: I spent more time trying to find how to import the data and finally found something that worked.
In fact it does not work. You created a link where you wanted to import all the data. Base opens the spreadsheet and tries its best to interpretes the sheet as a database table. The Base file is still tiny because all your data are still in the spreadsheet. A database link to a spreadsheet is useful for printing serial letters to a list of receipients that are stored in a spreadsheet. It avoids importing data into a new database for this trivial task. However, you will not have any advantage from this pseudo-database.
And now I tell you that there is no way to import unstructured cell values into a structure unless you build the structure that is able to take all the data and fix your spreadsheet so it fulfills the conditions of the database structure.
I'm 100% sure, that it is impossible to import 40,000 hand edited sheet rows offhand, no matter which sophisticated database program you try. This would require an artificial intelligence able to make sense of data. However, this is software from the 90ies.
WIthout seeing a single line of your data, it is impossible to give any constructive advice.
 Edit: P.S. you do not need any JRE at all for a spreadsheet connection. You definitively need one for HyperSQL which is entirely written in Java. This forum's tutorial section has a tutorial on Java issues. viewtopic.php?f=74&t=69896
All the Java issues go away with LibreOffice. 
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
waynec444
Posts: 84
Joined: Wed Jan 21, 2015 6:09 am

Re: Creating a DB flle from a OO spreadsheet

Post by waynec444 »

Here is a sample of my spreadsheet data: It is shown here in three lines but they are all on the same line in three columns.

entertainment
WV-West Virginia Daily News (Lewisburg)
https://wvdn.com/category/entertainment/

Now that I have been able to get the spreadsheet data into the database by accessing the spreadsheet to get it (it will not be updated), can I use the data in the database WITHOUT any access to the spreadsheet? I used the spreadsheet just to enter the data.

I want to be able to sort, search, print to file, replace, etc.

I do have a CSV file that has all of the spreadsheet info so I can import the data from there instead.
Open Office 4.1.10 on Windows Vista
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Creating a DB flle from a OO spreadsheet

Post by Villeroy »

So we have 3 columns (A, B, C), all text, no numbers, no dates and the 3rd one is meant to be a column of clickable hyperlinks.

Is there any header row above the data like Category|Name|URL?

Code: Select all

Category	Name	URL
entertainment	WV-West Virginia Daily News (Lewisburg)	https://wvdn.com/category/entertainment/
What is the range address of these 3 columns excluding any header row?
Click the first cell and hit Ctrl+Shift+End which expands the selection to the last used cell of a sheet. The range address is visible in the name box left of the formula bar.

The first column contains one or two dozends of repetetive category names ("entertainment", "science", "news", ...), right?
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
waynec444
Posts: 84
Joined: Wed Jan 21, 2015 6:09 am

Re: Creating a DB flle from a OO spreadsheet

Post by waynec444 »

Column 3 will have numbers and/or special characters in many of the entries.

Remember, I now have the data in Base.

I will not be using only Base to modify data. Also, although the third column contains URLs no clicking on these entries will be done in Base or Calc. I will be creating links for web pages in a way I have done for years in another DB program. The info in Column 3 will NOT change. Additional columns will be created in Base to complete usable links in the web pages when using (I hope) a function called "print to file." I hope that Base can do that.

In Base there is a header row with names. But NOT in Calc.
Open Office 4.1.10 on Windows Vista
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Creating a DB flle from a OO spreadsheet

Post by Villeroy »

waynec444 wrote:Remember, I now have the data in Base.
Can you edit, add or remove any data to that database?
What happens when you remove or rename the spreadsheet?
Image
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
waynec444
Posts: 84
Joined: Wed Jan 21, 2015 6:09 am

Re: Creating a DB flle from a OO spreadsheet

Post by waynec444 »

I edited the Base file and saved it. No change to spreadsheet data.

I changed the name of the spreadsheet and then opened the Base file. It opened without any problems.

I assume this is the info you wanted. Seems that the Base file is NOT connected to the spreadsheet.
Open Office 4.1.10 on Windows Vista
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Creating a DB flle from a OO spreadsheet

Post by Villeroy »

This kind of animal?
Image
Take care that you do regular backups and that do not shut down nor hibernate your computer while working with this. Otherwise you will lose all your data sooner or later.

You can convert an embedded HSQLDB to an external HSQLDB with a few clicks.
viewtopic.php?f=21&t=86071
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
waynec444
Posts: 84
Joined: Wed Jan 21, 2015 6:09 am

Re: Creating a DB flle from a OO spreadsheet

Post by waynec444 »

I do regular backups of all important files. Besides manual backups I have a program that backs up the important files (if changes have been made) every five minutes (when they are not open). The program also keeps the last 25 backups of each file. Also regular online backups. This has saved me many times.
Open Office 4.1.10 on Windows Vista
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Creating a DB flle from a OO spreadsheet

Post by Villeroy »

Base has no hyperlink fields. If you want to open the hyperlinks in column #3, you need an input form with an extra push button and this: viewtopic.php?f=100&t=92331&p=438435#p438435
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
waynec444
Posts: 84
Joined: Wed Jan 21, 2015 6:09 am

Re: Creating a DB flle from a OO spreadsheet

Post by waynec444 »

I do NOT want to open hyperlinks from Base. I want to be able to "print" the data to a file and add data in the file to make them clickable links. The Base info just gives me the basic URL to which I can add the rest for a link.
Open Office 4.1.10 on Windows Vista
Post Reply