Import Table Structure from Excel

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.

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.

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.

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
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
...
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.

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.

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.

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-
--

"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.

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

HTH
--
-Larry-
--

"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.

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.







"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.

"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