IFS 106 Prof Bill's Notes

Creating and Importing DB's

Some insight into create databases from a variety of sources

The First Step

So you want to create a new database? The first step each and every time (not most of the time, not some of the time, but every time) is to get a piece of paper and plan out your tables and fields. There is no magic in that computer screen until you know what you're doing. Now once you know what you're doing, then the magic begins.

So, for each and every database problem you do... answer the following questions:

  1. What are my tables?
  2. What are the names of the fields in my tables?
  3. What is the data type of each field?

Now answering these questions is sometimes difficult, but doing this is what we'll be learning over the course of the, well, course.

Importing data? Why?

I am focusing on importing data from a variety of sources because I think that it is a common problem that you may run into out there in the cold, cruel world. Data is rarely stored all nice and tidy in Access files. Rather, it's in web pages or text files or whatever. So, I want you to get some experience tackling these issues.

Also, don't be fooled by our small file sizes. You may think, "Ah, I could just type this data in and be done with it." Our file sizes are small for "educational" purposes. Many real databases will be much too large to just type the data in. This is where your (nascent) import skills become valuable.

The typical case

Typically, importing data into Access will require two steps. First, you'll have to somehow get the data into Access. That's covered for a number of different cases in the following sections. Once your data is in, then you will usually have to do some repair work to complete the task:

  • Check that all your field names are good... appropriate, descriptive, consistent, etc.
  • For each table, make one of your fields the primary key, if one hasn't yet been designated.
  • For each field, inspect the field's data type and descriptions... make changes as necessary. For that matter, make sure you want the field there in the first place!

Importing a web page

Access has the really cool and smart ability to find tables in a web page and then try to interpret them as databases.

  1. Start with the File/Get External Data/Import menu
  2. Set the File Type as "HTML documents *.htm" files
  3. Access will show you all the potential tables it has found in the web page, so select one that you like

If a web page has multiple tables in it, then you'll have to import each one separately.

Importing a text file

There are two cases here:

  • A nicely formed text file will have your raw data in rows, separated by either tabs, commas, or spaces. You can proceed directly to the import process
  • If your text file needs to be edited a little but, then go ahead. You can use a program like NotePad or WordPad, just make sure you leave the file type as simple text. Access can only handle one table per text file, so you may need to separate multi-table files into separate text files.

Once your text file is nicely formatted:

  1. Start with the File/Get External Data/Import menu
  2. Select a File Type of "Text Files *.txt" files
  3. Only one table is allowed, so you should check each field for its data type and whether you want it included in your database.

Importing a spreadsheet

This case is very similar to importing from a text file. You're only allowed one table per file, though you may be able to import multiple tables if they reside on separate worksheets. In any case, the process is very similar to that for text files:

  1. Start with the File/Get External Data/Import menu
  2. Select a File Type of "Microsoft Excel *.xls" files
  3. Only one table is allowed, so you should check each field for its data type and whether you want it included in your database.

Importing an XML file

XML is a standard file format on the web, sort of like HTML, but more general. Usually a database stored in XML will actually have two files related to it:

  • *.xsd - a schema file defining all the fields in the database and their type
  • *.xml - the actual data in the database defined by the above schema

Our text talks about importing XML files on pages 187-190. If your XML is well-defined (with its accompanying XSD schema file, primarily), then things are easy:

  1. Start with the File/Get External Data/Import menu
  2. Select a File Type of "XML *.xls; *.xsd" files
  3. Import the *.xml file that you have, not just the *.xsd.

Now, if you XML is not well-formed, then you'll have to get in there and pop the hood. It's not hard, but that's probably beyond the scope of our class. If you're interested in something like this, then email me and we'll chat.

Importing a Word file

I don't believe that there is a direct path from Word to Access. So what are we to do? You can cut and paste the relevant data from Word to any number of forms that Access does accept, like:

  • A text file... use NotePad or WordPad
  • A spreadsheet... use Excel
  • A web page... use FrontPage or Nvu

Web pages are probably the least desirable option here.

Exporting databases

We'll talk less about exporting database information because that's usually a pretty automatic thing to do. Our text covers some of this on pages 181-187.

We will talk in more depth about creating web pages from Access databases. That is coming soon.

thanks... yow, bill

wtkrieger@noctrl.edu ... william.krieger.faculty.noctrl.edu/