Joined: Jul 05, 2005 Posts: 685 Location: North West England
Posted: Fri Oct 09, 2009 3:01 am Post subject: Base: Creating a new database for existing data
I have a flat form (AppleWorks) database for my books containing approximately 2500 records with about 20 fields in each record. It can be exported only as tabbed ASCII text.
I would like to transfer all the data to a NeoOffice database, and if possible (without too much work!!) to make use of the relational features of Base.
All my books are divided into eleven broad subject areas. One of those is a professional subject area, and within that there are multiple sub-classifications according to a bespoke numerical scheme (which exists in a separate spreadsheet). AIUI these classifications could be created ina separate table in Base. This would be fine for new entries, but I don't know how to manage existing entries. Other possibilities for separate tables would be 'Author', 'Publisher', and 'Format'. Some of these might be just as easily managed by using a list field in the data entry form.
It is now becoming an urgent matter because I have 30 or 40 professional books waiting to be catalogued, and I can't really shelve them until I have done that.
For the sake of getting things done sooner, I would be happy to forgo the relational side of things, and just to create a form which will do data entry (I can manage a report all right). For this I am specifically having difficulties in knowing how to handle the automatically generated ID key (does it have a box on the form or not?) and how to create a drop down list for data entry.
I would also like on the data entry form to access the bespoke numerical classification table, and to have certain data from that table 'pulled in' according to the classification number chosen.
Ideally I'd like to have some way of entering new data using Base's relational capabilities without losing old data, and being able (in time) to go back and go over older data and alter it to be relational too.
I have looked through quite a lot of the material that is available, and I know what I would do if I were starting from scratch, but I'm a bit stumped about how to do this task with such a lot of existing data. _________________ MacBook Pro
13-inch, Mid 2012
Processor 2.5 GHz Intel Core i5
Memory 4 GB 1600 MHz DDR3
Graphics Intel HD Graphics 4000 512 MB
OS X 10.9.3 (13D65)
Joined: Jun 20, 2006 Posts: 2051 Location: Midwest, USA
Posted: Fri Oct 09, 2009 7:18 am Post subject:
James:
I'm pretty sure it can be done, although it will take some massaging of the data to do it. It would help me think through the process if you could provide some a sample file or two, including the bespoke numerical classification table, as I have no clue what that is!
Since you're in a bit of a rush, it might be best to initially set up either a flat or two-table database (the second one being the bespoke numerical classification), to let you get the books entered you need to, and then work on developing a better long term solution.
Some of the data massaging that needs to be done to arrive at your ideal setup could be done in the "temporary database." In fact I have a better handle on how to do that kind of massaging in Base than in Calc.
In terms of the auto ID key, yes I think it does need to have a field on the form, even though you don't enter anything there.
The drop down lists vary, depending on whether you are using a list box or a combo box.
Have you taken a look at the To Base and Back Again tutorial series in the wiki? Two pages in particular might be of help to you:
Looks like I never got around to doing one for List Boxes.
I'm happy to help you sort this out; it's the kind of thing I enjoy. But I should warn you that I'm a "fly by the seat of my pants" kind of gal when it comes to this kind of thing. I don't have any formal training in databases, it's all learn-as-you go for me.
Joined: Jul 05, 2005 Posts: 685 Location: North West England
Posted: Fri Oct 09, 2009 8:19 am Post subject:
Lorinda wrote:
James:
I'm pretty sure it can be done, although it will take some massaging of the data to do it. It would help me think through the process if you could provide some a sample file or two, including the bespoke numerical classification table, as I have no clue what that is!
Thank you for your offer of help. I attach a file with the classification system which originated as a filing system, but was for a long time a useful way to organise books. In due course I intend to move away from it to Dewey or LC, but in the meantime it shapes my shelf order. I also attach a sample file (.ods) from the Catalogue itself.
Quote:
Since you're in a bit of a rush, it might be best to initially set up either a flat or two-table database (the second one being the bespoke numerical classification), to let you get the books entered you need to, and then work on developing a better long term solution.
I've already been able to make the main catalogue into a table, and I don't think I'll have a problem with the Classification system. I think the tricky bit (in my mind anyway) is how to connect them together.
Quote:
Some of the data massaging that needs to be done to arrive at your ideal setup could be done in the "temporary database." In fact I have a better handle on how to do that kind of massaging in Base than in Calc.
Sounds good to me!
Quote:
In terms of the auto ID key, yes I think it does need to have a field on the form, even though you don't enter anything there.
Thanks that is helpful. Do I need to do anything to prevent data from being attempted to be entered in that field?
Quote:
The drop down lists vary, depending on whether you are using a list box or a combo box.
TBH I'm hazy about the difference between the two.
Quote:
Have you taken a look at the To Base and Back Again tutorial series in the wiki? Two pages in particular might be of help to you:
I've looked at and used the first of these pages. Thank you I'll take a look at the second one now.
Quote:
I'm happy to help you sort this out; it's the kind of thing I enjoy. But I should warn you that I'm a "fly by the seat of my pants" kind of gal when it comes to this kind of thing. I don't have any formal training in databases, it's all learn-as-you go for me.
Thanks. I really appreciate the help. 'Seat of the pants' tends to be my approach too.
James _________________ MacBook Pro
13-inch, Mid 2012
Processor 2.5 GHz Intel Core i5
Memory 4 GB 1600 MHz DDR3
Graphics Intel HD Graphics 4000 512 MB
OS X 10.9.3 (13D65)
I think the most difficult part will be importing your flat database into a Base table. I just created a simple, tab-delimited text file and I could find no way to directly import data into a new, empty Base database.
So, before you try to separate out columns with recurring values into new tables, I would recommend trying to get your data imported as a single, flat table in a new database.
Here is how I was able to import a tab-delimited files into Base:
1. Save the tab-delimited AppleWorks export with a .csv extension and open it in Calc. Be sure to change the Separator to Tab in the dialog that appears when you open the file.
2. If the first row in the Calc document is data, insert a new row and add column names in each cell. This step is important as in the next step Base will use the first row to create the column names in a new table.
3. Open a new or existing Base file. Then, go back to the Calc document, select the Edit :: Select All menu to select the entire Calc document, and drag the selected area into the "Tables" section of the Base window.
4. A dialog should appear asking you to set the name of the table and if you want an autogenerated primary key in the table. Set the table name, make sure that the "Description and data" and "Create primary key" checkboxes are checked, and press the Create button.
Do these steps allow you to import your AppleWorks data into a Base table?
I found this thread about recreating AppleWorks form elements linked from our "Opening AppleWorks documents" page.
That page already has the first half of Patrick's instructions; I'm going to add the second half (about moving the data from Calc to Base) to the page and link it from the NeoBase page.
Smokey _________________ "[...] whether the duck drinks hot chocolate or coffee is irrelevant." -- ovvldc and sardisson in the NeoWiki
Joined: Jun 20, 2006 Posts: 2051 Location: Midwest, USA
Posted: Fri Oct 09, 2009 9:27 am Post subject:
James3359 wrote:
Thank you for your offer of help. I attach a file with the classification system which originated as a filing system, but was for a long time a useful way to organise books. In due course I intend to move away from it to Dewey or LC, but in the meantime it shapes my shelf order. I also attach a sample file (.ods) from the Catalogue itself.
Thanks, that helps me get a sense of things. And a lot of the books look famliar!
Quote:
I've already been able to make the main catalogue into a table, and I don't think I'll have a problem with the Classification system. I think the tricky bit (in my mind anyway) is how to connect them together.
How are they linked together in Appleworks? I don't see the bespoke #s in the catalog file.
Quote:
Quote:
In terms of the auto ID key, yes I think it does need to have a field on the form, even though you don't enter anything there.
Thanks that is helpful. Do I need to do anything to prevent data from being attempted to be entered in that field?
No, the form will prevent anyone from tampering with that field.
Quote:
Quote:
The drop down lists vary, depending on whether you are using a list box or a combo box.
TBH I'm hazy about the difference between the two.
Combo Boxes provide you with a (usually) optional list of values or text. You can type in a different value/text if you wish. And they save the value/text you enter or select into the underlying table. This is almost certainly what we will use for the data entry form in your initial "rush" database.
List Boxes limit you to the values/text provided. And, they allow you to see the values from one field but save the values from another. In your case, the bespoke # would be the ID field in the bespoke table, and that value should be entered in "bespoke" field of a given record in the Books table. But we humans would have an easier time if we could choose from a descriptive list. So you could set up a List Box that lists the Detail descriptions in the box to choose from, but would save the bespoke # in the Books table.
Joined: Jul 05, 2005 Posts: 685 Location: North West England
Posted: Fri Oct 09, 2009 12:25 pm Post subject:
Lorinda wrote:
James wrote:
I've already been able to make the main catalogue into a table, and I don't think I'll have a problem with the Classification system. I think the tricky bit (in my mind anyway) is how to connect them together.
How are they linked together in Appleworks? I don't see the bespoke #s in the catalog file.
The information from the classification system is in columns P and S for records 79 and onwards. Essentially it is only used to organise the very large (50%+) theology category. _________________ MacBook Pro
13-inch, Mid 2012
Processor 2.5 GHz Intel Core i5
Memory 4 GB 1600 MHz DDR3
Graphics Intel HD Graphics 4000 512 MB
OS X 10.9.3 (13D65)
Joined: Jun 20, 2006 Posts: 2051 Location: Midwest, USA
Posted: Sat Oct 10, 2009 6:08 am Post subject:
Ah, I see them now. Since there's not a column in the Book Catalogue whose content exactly matches the content of a column in the classification spreadsheet, it will take some massaging of the text in the catalogue list to make them interact. Some of that might be best done in a spreadsheet, actually.
First, though, you'll need to think about how you want the connection to be made, both in the short-term and in the long term. That will determine how the catalogue list needs to be "massaged"
Or...you could temporarily go with a completely flat file system, and use combo boxes to simplify entering Main heading, Sub heading, and Detail cells. This would mean you would only have to massage the data once, for the final system, instead of twice (for both the temporary and the final system).
Joined: Jul 05, 2005 Posts: 685 Location: North West England
Posted: Mon Oct 12, 2009 6:41 am Post subject:
Column P in the Book Catalogue ("Class#") is the same number as the ClassificationID number in the first column of the classification spreadsheet.
There was one duplicate in the classification numbers which I have now resolved, and I have imported that data into my database. However, when I try to edit the table to make the ClassificationID the Primary Key I get the following error message when attempting to save:
Quote:
Error while saving the table design
Violation of unique index SYS_IDX_52: duplicate value(s) for column(s) "CategoryID" in statement [ALTER TABLE "Saward Classification" ADD PRIMARY KEY ("CategoryID")]
I have checked my table and can't see any duplicate, so I am wondering what the problem is. Googling suggests that there might be a bug in the underlying OOo code but may be I have just missed something.
At the moment, the relational capabilities of Base are not a great issue for me, and in the end if I have to have the classification information open in a spreadsheet and copy and paste it into new entries for the Books Catalogue, that would be OK. Or a halfway house of some kind where the classification data is directly accessible via some kind of control in the data entry form would be better.
In the longer term the classification system needs to be changed anyway, so there is not much value in spending time to integrate it fully. The main thing as far as I am concerned is to get a workable way of inputting new data into the Books Catalogue table. I would like a drop down list for the Genre field if possible. _________________ MacBook Pro
13-inch, Mid 2012
Processor 2.5 GHz Intel Core i5
Memory 4 GB 1600 MHz DDR3
Graphics Intel HD Graphics 4000 512 MB
OS X 10.9.3 (13D65)
Joined: Jun 20, 2006 Posts: 2051 Location: Midwest, USA
Posted: Mon Oct 12, 2009 6:59 am Post subject:
James3359 wrote:
Column P in the Book Catalogue ("Class#") is the same number as the ClassificationID number in the first column of the classification spreadsheet.
Now how did I miss that?
That solves that problem, aside from the records where P is empty. That may or may not be a problem.
Quote:
There was one duplicate in the classification numbers which I have now resolved, and I have imported that data into my database. However, when I try to edit the table to make the ClassificationID the Primary Key I get the following error message when attempting to save:
Quote:
Error while saving the table design
Violation of unique index SYS_IDX_52: duplicate value(s) for column(s) "CategoryID" in statement [ALTER TABLE "Saward Classification" ADD PRIMARY KEY ("CategoryID")]
I have checked my table and can't see any duplicate, so I am wondering what the problem is. Googling suggests that there might be a bug in the underlying OOo code but may be I have just missed something.
I think the problem is that a primary key cannot be added after a table is created...although I'm not sure that applies to SQL commands as above. Do you have any blank entries in the Category ID field?
You might try re-importing the spreadsheet and specifying the primary key field in the wizard.
Quote:
In the longer term the classification system needs to be changed anyway, so there is not much value in spending time to integrate it fully. The main thing as far as I am concerned is to get a workable way of inputting new data into the Books Catalogue table. I would like a drop down list for the Genre field if possible.
The instructions in the Combo Box article should get you there. I've set the link to the instructions for using the Replace command, on the assumption that your form is already in existence.
Joined: Jul 05, 2005 Posts: 685 Location: North West England
Posted: Mon Oct 12, 2009 7:59 am Post subject:
Lorinda wrote:
I think the problem is that a primary key cannot be added after a table is created...although I'm not sure that applies to SQL commands as above. Do you have any blank entries in the Category ID field?
You might try re-importing the spreadsheet and specifying the primary key field in the wizard.
I've tried this a couple of ways now. The wizard doesn't (AFAICT) allow me to specify one of the fields I am importing as the Primary Key. The only option is for the wizard to create a a Primary Key. I have tried importing and then specificying Category ID as the Primary Key, and I have tried importing (with creation of a Primary Key) and then trying to change the Primary Key which also produces a (slightly different) error message.
Lorinda wrote:
The instructions in the Combo Box article should get you there. I've set the link to the instructions for using the Replace command, on the assumption that your form is already in existence.
OK I'll have a go at that. Thanks _________________ MacBook Pro
13-inch, Mid 2012
Processor 2.5 GHz Intel Core i5
Memory 4 GB 1600 MHz DDR3
Graphics Intel HD Graphics 4000 512 MB
OS X 10.9.3 (13D65)
Joined: Jun 20, 2006 Posts: 2051 Location: Midwest, USA
Posted: Mon Oct 12, 2009 8:22 am Post subject:
In terms of setting the primary key on import, see This section, specifically the following instructions when the Type Formatting window/tab of the wizard is discussed:
Quote:
Right-click or control-click on the "ID" field name in the left hand column. Select Primary Key from the contextual menu (it is the only option.) A yellow key appears to the left of the field name.
It is not self-evident, as is true for much of base.
Joined: Jul 05, 2005 Posts: 685 Location: North West England
Posted: Mon Oct 12, 2009 9:31 am Post subject:
Hah! That seems to have done it! It did generate the following error message:
Quote:
Warning!
Violation of unique constraing SYS_PK_72: duplicate value(s) for column(s) "Category ID" in statement [INSERT INTO "Saward Classification 2" ("CategoryID","Detail","Main Heading","Sub-heading") VALUES (?,?,?,?)]
Continue anyway?
but I clicked 'Yes' to continue anyway and it seems to have worked all right. _________________ MacBook Pro
13-inch, Mid 2012
Processor 2.5 GHz Intel Core i5
Memory 4 GB 1600 MHz DDR3
Graphics Intel HD Graphics 4000 512 MB
OS X 10.9.3 (13D65)
Joined: Jul 05, 2005 Posts: 685 Location: North West England
Posted: Mon Oct 12, 2009 10:01 am Post subject:
Now I've got the form. When I open it it shows the first entry in the catalogue. I have, I think, managed to get the List Box stuff sorted out (although it feels like a rather long winded way of doing it to me). How do I get it to show other records in the catalogue.
How do I create a new record for data entry. If I don't put anything in the ID (Primary Key) field it tells me I need to, But I don't know what number to enter in that field?
This is all making me feel remarkably stupid _________________ MacBook Pro
13-inch, Mid 2012
Processor 2.5 GHz Intel Core i5
Memory 4 GB 1600 MHz DDR3
Graphics Intel HD Graphics 4000 512 MB
OS X 10.9.3 (13D65)
Joined: Jun 20, 2006 Posts: 2051 Location: Midwest, USA
Posted: Mon Oct 12, 2009 10:16 am Post subject:
I'm afraid that Base can be that way. In other words, it's probably not you, but the very un-user-friendly nature of Base.
It might be easiest for me to figure out what's going on if I could see a copy of the odb file. If you don't want to post it here (or can't due to size issues), send me a pm and we can figure out a way to get it to me.
If it's asking you for an ID number, the table probably isn't set for auto increment.
There should be a navigation bar near the bottom of the window that lets you move between records and/or add a new record.
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 You cannot attach files in this forum You cannot download files in this forum
I'll take a look at the second one now.
