Import Table Structure from Excel

[Les H]

I have been a long time user of Borland Visual Dbase (because of its
convenience and simplicity of use) and would like to migrate to Access but
have found it unwieldy.

I frequently receive data from surveys with a few hundred fields. The data
arrives as a .dat (text) file plus a data map (Excel). It has always been an
easy task to convert the data map into a table specification, which I can
export as a Dbase file. Dbase has a simple CREATE USING command, which
creates a new table from an existing "structure" table.

My question: Is there a simple(!) way that this can be achieved in Access?

I have some experience of writing macros with VBA (Excel and Powerpoint) but
would prefer to avoid the hassle.

Thanks.

 

[Jeff Boyce]

Creating a well-normalized relational database table (e.g., in Access) "with
a few hundred fields" is something of a contradiction in terms. A scan of
this newsgroup (tablesdbdesign) will show a strong consensus that a
well-normalized table will only rarely have more than 30 fields.

The fact that the survey was originally conducted using Excel is a clue ...

Take a look at work that Duane H. has done on building well-normalized
survey databases. Perhaps there's a way to "parse" your raw data into
something that Access can more fully utilize. See:

Duane Hookom has a sample survey database at
http://rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[Les H]

Jeff,

Thanks for your response but it doesn't seem to address my question.

I am not really interested in "well normalized" - all I want to do is get
data, that arrives in a text file (with an Excel spreadsheet that tells me
what columns are what), into an Access table quickly and efficiently.

No amount of worrying over the fine details of database/table design will
matter until that first simple task is achieved. With good old Dbase, this
was a matter of 10-20 minutes work (and very little typing/clicking) for a
few hundred columns of data.

My question is whether there is a similarly simple method of getting my data
into an Access table?

Regards,

 

[Jeff Boyce]

No. Access limits the number of columns to 255 (a theoretical limit).

As for why "the finer points of database design" matter, in Access at
least...

You will find that Access' features and functions work better (i.e., with
less work on your part) if you feed it normalized data, not spreadsheet-like
layouts of "a few hundred" columns.

If you haven't already, considering following the link to Duane's work -- it
could really save you a lot of headache, if you are determined to go forward
with using Access.

Just because you could do it in Dbase doesn't make it desirable or a good
idea in Access...

Could you just use Dbase?

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[Brendan Reynolds]

As Jeff says, the maximum number of fields in a JET table is 255, so if any
of your tables contain more fields than that you will not be able to import
them into a single JET table.

If none of your tables exceed 255 fields, then you could continue to use
dBase to create the tables from your Excel files, and your Access
application could import or link the resulting dBase tables.

If you can find a way to create an XML Schema from your Excel
specifications, you might be able to use that XML Schema to create the
tables. I can't say for sure, as I have not made much use of the XML
features in recent versions of Access, but I think that should be possible.

Other than that, you would need to write code to read the specification from
the Excel files and create the JET tables. Provided the Excel files stick to
a standardized format and layout, it would not be particularly difficult to
do so.

 

[Les H]

Thanks to Jeff and Brendan. I can tell you are trying to help but I think the
truth is that I'm not communicating well enough what I want to do. As a
consequence, you are not responding to the question I'm trying to ask.

By the by, the 255 columns thing is exactly the same in Dbase. I circumvent
this by breaking the data into two or more tables.

Here's WHY I'm asking the question - Borland no longer supports Dbase as a
product and, with operating system changes etc., it's only a matter of time
before I'll be unable to run Dbase on a PC (unless I keep a legacy system
just for the purpose). That means I MUST think about migrating to a different
piece of database software. (My first thought was just to use Excel but its
data import facilities are primitve, requiring maximum manual manipulation
and mouse clicking). It also means that, yes, I can continue to use Dbase in
the short term but not in the longer term.

Here's WHAT I need to achieve. I need to get the data from surveys - over
which I have no editorial or technical control - into tables. The data
arrives in text files with a data map, as I've said.

Here's what I DO when I have survey data to load. The data map (spreadsheet)
lists the variable names, column number in the record, and column width. I
rapidly convert this (with a few simple Excel formulae) to a data table
structure(s), treating every field as text. Each structure is saved from
Excel as a Dbase table. I then use Dbase's CREATE USING command to create a
new table from the structure table. Finally, I populate the new table with
Dbase's APPEND FROM command.

Here is my criterion for a satisfactory solution. I need to get from raw
data to populated data tables in the shortest time and with the least
possible effort, given that every survey is unique in its structure. As I've
said, this normally takes me less than half an hour with Dbase. I have to do
very little typing and most of the mouse operations involve holding the left
button down and using the scroll wheel.

My suspicion is that Access's data import facilties are just as primitive as
those of Excel and that it's facilities for table definition/construction are
equally primitive, albeit that they are wrapped up in fancy clothes. That, at
least, is my perception based on my attempts so far to use Access to do this
seemingly simple task.

I don't doubt that I COULD use VBA or, if I had 2003 stuff, .NET programming
to do the job - I can certainly write the programs - but two things mitigate
against this. First, every situation I have to deal with is uniquely
structured and would have to be rewritten each time. Second, everything I've
ever done with VBA and .NET has been like pulling teeth once you stray from
the fine line laid down in the help files. The simple truth is that I would
rather not go this route unless I have to.

I don't know whether this is any clearer. Or is it that I just need to read
your answers so far as "No, there is no easy way to do this simple task"?

Regards,

 

[Larry Daugherty]

Yes you can continue to use your version of dbase in the long term.
Microsoft has a nifty tool named Virtual PC. The current version runs
on Windows XP. There are earlier versions out in the wide world that
run on earlier OSs. Right now VPC costs $129 for a single license. I
believe that it will be an included part of Windows Vista.

With VPC you can create a virtual PC running an earlier OS. It's up
to you to install the OS and maintain and manage it just as you would
a "real" system.

If your need is only to support your own version of the application,
VPC is the way to to. I doubt that you can migrate your app to a
current platform for less than the cost of VPC.

It can be a bit of a pain to get started with VPC but, once you've
mastered the process of creating a new OS image, it's incredibly
useful.

HTH

 

[Larry Daugherty]

Oops! I forgot to mention that if you're a current subscriber to
TechNet you already have it.

HTH

 

[David Cox]

I made a dummy file with a string of characters with date, text and number
formats and commas and no spaces.

The world has moved on. It is still moving.

I am using Access 2007 Beta (free). 255 columns no longer applies.

Access normally accepts text data in two standard formats. The first is
Variable length fields with a common delimiter value, usually a comma. This
is the standard CSV or comma seperated variable format, although the comma
can be replaced with other delimiter characters.

The other format is fixed width columns with a space separating the columns.

My data was input as fixed width with no separating characters. i.e.
non-standard.

In the next screen Access asked me where to put the divisions between
fields,and I could put them anywhere, just point and click. I could recover
errors.
I could name each field
I could specify its data type, including Text, Date, Number, currency, OLE
object, memo, hyperlink. Actually Access guessed all the ones I had
correctly.
I could specify to index on that field.
I could specify to ignore bits of the data

I will not go into all of the options available if i pressed the advanced
button.

Access then asked me if I wanted to specify a key, or if I wanted it to add
one, or leave the table without one.

The whole operation took me six minutes, mostly time taken to enter field
names.

You have been wasting part of your life attached to obsolete technology.

 

[Granny Spitz via AccessMonster.com]

you can continue to use your version of dbase in the long term.
Microsoft has a nifty tool named Virtual PC.

Great idea! Les can still use the DBase app until he's comfortable with
Access or some other data import tool.

Right now VPC costs $129 for a single license.

Great news! Right now we can download Virtual PC 2004 for *free*!

http://www.microsoft.com/downloads/...9D-DFA8-40BF-AFAF-20BCB7F01CD1&displaylang=en


Supported Operating Systems: Windows 2000 Service Pack 4; Windows Server
2003, Standard Edition (32-bit x86); Windows XP Service Pack 2.

 

[Brendan Reynolds]

I am using Access 2007 Beta (free). 255 columns no longer applies.

<snip>

Are you sure about that, David? I had not heard of any change in that area,
but I don't have anything with more than 255 columns with which to test.

 

[Brendan Reynolds]

I had assumed that your 'data map' was doing more than that. From this
description, I'm now wondering why you don't just use the built-in text
import wizard, as described by David elsewhere in this thread.

 

[David Cox]

"Are you sure .." I was, and I was wrong. We will still have to get by with
a measly 255 columns in Access 2007. How will we cope? :-< :->

 

[Brendan Reynolds]

Phew! You had me worried there for a minute!

There's always SQL Server. 1,024 columns per base table, 4,096 columns per
SELECT statement. Scary!

--
Brendan Reynolds
Access MVP

"Are you sure .." I was, and I was wrong. We will still have to get by
with a measly 255 columns in Access 2007. How will we cope? :-< :->

 

[Les H]

Unless I am much mistaken, the text import wizard is exactly what I was
referring to as Excel's "primitive" import system. If you know where the
fields are, how big they are, etc. then I'd agree. My situation is that the
only way I know where to find anything is via the data map. By converting the
data map into a table structure, I save huge amounts of time. I can assure
you all that, in general, it is NOT possible to achieve the text import in 6
minutes!

To tell the truth, I am disappointed (but not surprised) to find that the
"technology" has lost the ability to do some of the basic tasks simply. It is
a sad but almost universal trend in commercial technology development - the
imperative to justify upgrading. The thought of having to spend EXTRA money
to do SIMPLER tasks is, frankly, a red rag to a bull as far as I'm concerned.

Thanks for your efforts. I think I probably have the answer I feared most.

Regards,

 

[Tom Wickerath]

Hi Les,

You may not be out of luck just yet. I'm not sure what your data map looks
like in Excel, but you might be able to use some VBA code to programmatically
create a schema.ini file. Here is a KB article that demonstrates doing this
for tables in Access:

How to programmatically create a Schema.ini file in Access 2000
http://support.microsoft.com/kb/210001

I tested this code and it seems to work fine with local and linked tables.
It even worked with an ODBC linked table in a SQL Server database. Try the
sample out in Northwind, and examine the contents of the resulting schema.ini
text file. The point I'm trying to make is that you might be able to easily
convert your data map in Excel into a schema.ini file.

The schema.ini file is used to help with importing text files, using the
TransferText method. Here is a KB article that discusses how to do this:

ACC2000: How to Use Schema.ini to Access Text Data
http://support.microsoft.com/kb/210073


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Les H]

Tom,

Thank you for the leads to the articles. Schema.ini is indeed what I've been
looking for. I could be picky and say that it's a heck of a lot more
complicated than it needs to be (lots of "unnecessary" overhead content in
the Schema.ini file) but that would be churlish.

With a bit of programmatic leger demain (and four or five extra steps in my
process chain) I may now be able to migrate to Access if and when my Dbase
becomes unusuable.

Regards,

 

[Tom Wickerath]

Hi Les,

Although I haven't seen the structure of a typical data map in your Excel
file, I'm guessing that with some minor VBA programming that you might be
able to reduce the time your current process takes from approx. 6 minutes to
just a few seconds. The development of the code required would take some
time, but once it was tested, debugged, and working properly, I think you
would be very happy you made the switch.

If you care to send me a few Excel data map files, along with their
corresponding text files, I can take a look to see how easy or difficult this
might be. My e-mail address is available at the bottom of the contributors
page shown in my signature. Please do not post your e-mail address, or mine,
to a newsgroup message.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________