AUTHOR'S MAIN SITE  »  Open Office Tutorials Main Index
Delicious.Com Bookmark this on Delicious   StumbleUpon.ComRecommend to StumbleUpon

Open Office Database Tutorials

Adding records to a table

... via batches in CSV text files.


This essay will explain a way to add batches of records to an existing table.

The batch of records to be added will be held in a single CSV file.

Suppose, for the discussion, that you have a table called NameStateNumber with three fields...

names
where a person lives (just the state they live in), and
their telephone number.

That's a little artificial, but it will do to illustrate the relevant issues.

For the sake of this discussion, the only constraints on data in the database are...

1) The name must be no more than 10 characters. (Longer in real life, of course... but 10 will work better for this discussion.)

2) The value in the "state person lives in" field, hereafter called LivesInState, must be in the standard set of USPS abbreviations.... NY, CA, PA, MA, etc. (Everyone lives in the USA, for the sake of this discussion.)

So.... let's say I've typed up the following as a batch of records to be appended to the database....

860-787-5555, MA, Fred
608-878-6666, CA, Alice
111-222-3333, XX, Henry
113-555-1212, NY, Euphiagenia

... and saved it in a file called "BatchOfData3Oct16.txt".

Overview

All you need to do is to copy/paste that to a spreadsheet... I've tested this with, obviously, OpenOffice's "Calc".

You then do a copy/paste from the spreadsheet. (Sorry... you can't "go direct".)

Details

So. We'll assume you have your database already set up.

Enter some data into a text file, separating the fields in each record with commas.

Whether you put the field names in the first "record" of the batch file is up to you... there are pros and cons. The description that follows is based on DOING that. But, if you adapt the instructions slightly, you don't have to. The names you use do not have to exactly match the field names, as used in the database.

Your life will be EASIER if you arrange the fields in the batch records in the same order as the fields are listed if you open the table FOR EDIT.... but it isn't "necessary".

Open the batch of records in the text file in a text editor. Select them all, press Ctrl-C (or use the trick of your choice) to copy what you have selected.

Open a fresh spreadsheet page. You aren't going to keep what goes into the spreadsheet, which should be good news. (You can if you wish.. but you don't need to.)

Right-click on a cell, and choose "Paste".

In Calc, this opens the "Text Import" dialog.

Most of the settings can be left in their default states.

In "separator options", select "comma" for the field separator, and deselect the others on offer.

IMPORTANT, and "a trick":

At the bottom of the dialog, there is a section headed "Fields", and, in it, a grid showing what you propose to paste.

And a combo-box called "Column Type". You want to set all the columns to "Text" (I think... I try to keep things SIMPLE... in MY wants, "text" works well, because it minimizes the "things" the computer "does for (to?) me".)

THIS IS THE TRICK: If you click on the little box at the extreme upper left of the grid, above the row numbers, to the left of the column headings, you can set the column type of ALL of the columns in a single click.

Notice how the combo-box becomes enabled when you click the box I indicated? Now use the combo-box to specify "Text" for the column type. Notice that ALL the columns were set to "text" in one click? (Well, two if you count clicking the corner box!)

Moving on....

Click "OK", and you should see the data you had in the text file nicely arrayed in the spreadsheet. If you had a comma WITHIN one of your fields, where it just meant "comma", not "field separator", you will have a nuisance on your hands. The easy solution: Don't use commas! There is another... putting quotes around the field. In many circumstances that will "work" for you... but in some, it won't. I don't use commas because life is "full" enough already. If you want to use the "quotes" answer, you aren't being desperately reckless, but I am not going to add any further notes for your case.

So! Records which are making their way to the database table are now in a spreadsheet. This would be a great moment to scan them, brush up any little typos... unless the originating text file is going to be held as the ultimate "backup". (If that's the case, edit the text file, repeat the export- to- spreadsheet.)

From Spreadsheet to Database...

Highlight the cells holding the records.

Open the ooBase main project manager window.

Go to the list of tables.

Right click on the... right! (correct)... table.

Choose "Paste". (Not, as you might imagine, "Paste Special". Append a whole batch of records to a table, without even opening it?? Seems pretty "special" to me!)

A wizard will fire up, asking sensible questions...

   What table do you want
       to paste TO (this will be pre-filled)
   Options...
      (Paste) Definition AND Data? (No)
      (Paste) Definition? (No)
      (Paste) As Table View? (No)
      (Execute) Append Data<<< YES!
      (Tickbox) Use first line as
           column names? (Yes, if you put them
           in the spreadsheet, first line.
           Otherwise, no.)
      (Tickbox) Create Primary Key (Will be
           grayed out, as you are appending data)

Once you've got those settings made, click "Next".

The next window will have a list of the fields in the batch file of records (left hand list) and the table's fields (right hand column).

If you entered the field data into your batch file in the same order as the appear in table's DESCRIPTION, your work is much easier. But it isn't "hard" if you didn't.

Use the chevrons to move things up or down in the lists... you can move items in EITHER list... until the things that correspond are all on the same lines as each other.

Click "Create"...

... and that will APPEND the records to the previously "created" table!

What could possibly go wrong?

The great weakness of this approach is that Base must in some cases reject records. The most obvious cause would be if contents of the primary key field in a record you are trying to append is the same as the contents of that field in one of the records already in the table.

If this situation arises, Base is not, as far as I've been able to interpret it, entirely consistent. I'm always much happier if I get NO complaints during the "append" process. I tend to make a note of how many records are in the table BEFORE the append, how many there are in the batch file, and looking to see that Before + In-Batch = Number-Of-Records-After, just as a check on "things".

Furthermore, I haven't discovered something that must be there somewhere: A list of what records failed the database's attempt to comply with my order to append them.

Two more common causes of records being rejected:

1) Length constraints. The table will have had lengths stipulated for the various fields. In our example, we had the "Name" field, length 10 characters. If we try to send a new record to the table with "Metcalfe-Gibson" in the name field, it won't be accepted. (I actually had a "day job" database many years ago which I had to re-work because that name was too big for the space I'd allocated in the original design of the database. We were very frugal with space, in those days.)

2) Trying to put a word into a number. All of the fields in the spreadsheet were declared to be TEXT. If we have "123" in one of those fields, and "send" it to the database as described above, all will be well. If, however, we put "abc" in that field, although the spreadsheet will be quite content, the database will reject the record.

That's it! (Please write in, if anything above... within the limits I set out for the exercise... didn't work for you, or was badly worded, and too effort to make sense of.

If you are into Lazarus or Delphi programming, you might be interested in some stuff I wrote about using Lazarus to create and validate CSV files with batches of records to be added to a database.


Ad from page's editor: Yes.. I do enjoy compiling these things for you... I hope they are helpful. However this work doesn't pay my bills!!! If you find this stuff useful, (and you run an MS-DOS or Windows PC) please visit my freeware and shareware page, download something, and circulate it for me? Links on your page to this page would also be appreciated!

Click here to visit editor's Sheepdog Software (tm) freeware, shareware pages.


Search across a selection of all the pages on various sites with the Google search button at the top of the page the link will take you to.
Or...

Search just this site without using forms,
Or... again to search just this site, use...

Powered by FreeFind

Site search Web search

The search engine merely looks for the words you type, so....
  *!  Spell them properly   !*
  Don't bother with "How do I get rich?" That will merely return pages with "how", "do", "I", "get" and "rich".



I have other sites...
   SheepdogSoftware site.
   My site at Arunet.



Here are some ways to contact this site's editor, Tom Boyd.



Ad from page's editor: Yes.. I do enjoy compiling these things for you... I hope they are helpful. However.. this doesn't pay my bills!!! If you find this stuff useful, (and you run an MS-DOS or Windows PC) please visit my freeware and shareware page, download something, and circulate it for me? Links on your page to this page would also be appreciated!

--Click here to visit editor's freeware, shareware page.--




Valid HTML 4.01 Transitional Page tested for compliance with INDUSTRY (not MS-only) standards, using the free, publicly accessible validator at validator.w3.org. Mostly passes. There were misc "unknown attributes" in Google+ button code and the Google Translate code. Sigh.

-- Page ends --