importing flat to relational

[alekm]

Hi,
I've got a huge flat table (SPSS) to import to a relational database in
Access 2003 where huge flat table shall be spread all over 20 tables. How do
I start?
Thanx

alekmil

 

[Jerry Whittle]

The first question to consider is if this is a one time thing or something
that you will be doing often. If often, you will need to keep create a
checklist so that you can repeat the steps. You'll also want to create
queries, macros, and maybe code modules to help in the future.

The first thing that I would do is to import the file into an empty Access
database. See if there are any problems such as too many rows, fields, or
problem datatypes.

Next I'd go to Tools, Analyze, Table and let Access take a swipe at moving
the data into various tables and normalizing the data. This will probably not
be the final answer buy may give you some ideas on what tables are needs.

 

[Peter Hibbs]

alekmil,

If this is a one-off operation, i.e. you are trying to create a
properly normalised database from a single table database, then you
could have a look at the Excel to Access Converter Utility at :-

http://www.rogersaccesslibrary.com/...?TID=183&SID=e81a164e2314187z3e8587231e28cb7d

First export the relevant fields in your big table as a .csv file (use
a query to do this) and then use the above utility to import the data
into a series of related tables. Of course you will have to decide
which tables you need and create them first but you should be able to
do the whole operation in a few minutes.

HTH

Peter Hibbs.

 

[aardvick]

Hi Jerry,
Sorry for jumping in here, but I'm trying to do the same thing as alekm.

In your reply you say: "The first thing that I would do is to import the
file into an empty Access database."

Do I need an ODBC connection to do this? I've installed the SPSS driver,
but haven't succeeded in setting up the file DSN. Am I on the right track,
or is there an easier way?

THANKS!

 

[Jeff Boyce]

As others have already pointed out, how depends on what (and how often...).

Without more specific description, it's hard to determine whether you have
20 tables that are essentially copies of each other (e.g., one per year
covering 20 years) or 20 separate, well-normalized tables.

More info, please...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.