Import Table Structure from Excel

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.

--
Brendan Reynolds
Access MVP

"Les H" wrote in message
...
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,







"Brendan Reynolds" wrote:


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.

--
Brendan Reynolds
Access MVP

"Les H" wrote in message
...
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" wrote:

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/Other...p#Hookom,Duane

--
Regards

Jeff Boyce
Microsoft Office/Access MVP
http://mvp.support.microsoft.com/

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

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


"Les H" wrote in message
...
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.

"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" wrote in message
...

"David Cox" wrote in message
...
snip
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
Access MVP

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

"David Cox" wrote in message
...
"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" wrote in message
...

"David Cox" wrote in message
...
snip
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
Access MVP

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,


"Brendan Reynolds" wrote:


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.

--
Brendan Reynolds
Access MVP

"Les H" wrote in message
...
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,







"Brendan Reynolds" wrote:


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.

--
Brendan Reynolds
Access MVP

"Les H" wrote in message
...
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" wrote:

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/Other...p#Hookom,Duane

--
Regards

Jeff Boyce
Microsoft Office/Access MVP
http://mvp.support.microsoft.com/

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

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


"Les H" wrote in message
...
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.

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/ex...tributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

"Les H" wrote:

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,

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" wrote:

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/ex...tributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

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/ex...tributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

"Les H" wrote:

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,