Export Excel data to Access

J

Jim H.

I have an Excel wookbook template that I use to create new
project files to track and manipulate data on individual
projects. I also have an Access database where I record
some common data as the spreadsheet resulting in duplicate
data entry. How can I export data easily (i.e. novice
programming experience)on a regular basis from these
separate Excel files to a common Access table to eliminate
this duplicate data entry?
Thanks,
Jim H.
 
D

DDM

Jim, initiate the process from Access. Open your Access database and File >
Get External Data > Import. Set Files of type to Microsoft Excel, then
navigate to the Excel file and Import. That will activate the Import
Spreadsheet Wizard.

Assuming your Excel workbook is properly set up (data entered in contiguous
columns and rows; first row contains column headings; column headings match
field names in Access table) it should be an easy process to import the
data, appending it to your existing Access table.

No programming required.
 
P

pobuckley

I have all my data in a single excel column and I want to convert i
into an access record. Is there a quick way of doing this?

I would transpose in excel and then export exceopt that there is 40
rows of data)

TI
 
D

Dave Peterson

I think you're going to have to give more info.

Is your data consistently laid out?

Is it grouped--each set of 5 cells makes a new row?
or is it separated by (say) blank cells--so one group could be 3 rows and
another group is 18?

And if each group is a variable sized list, is there a way to determine which
field is mapped where?
 
P

pobuckley

Every column is consistently laid out. The first cell will be name
second will be address etc. All 400 values will be read in for ever
person so it is not variable. Each set of 400 values to each perso
will constitute one row in the database.

Thanks again
 
D

Dave Peterson

First, I don't use Access, but it sounds like your data isn't in just one
column.

Do you mean that you have multiple columns--each laid out the same way--and you
want to bring in 400 rows/fields per record?

If you meant this, then I thought Access had a 256 column limit, too.

Way over my head...

Could you break it into smaller pieces (say 200 fields apiece). Then make some
kind of linked table within Access?

If that sounds like it would work, then if you say what field should be the key
and how many cells go into each row, I bet a little excel macro would work.

For instance, if your data is in A1:X400, you might say that row 1 will be the
index.

Then the first column could be "transposed" into two different worksheets.

The first would be: A1:A200, the second would be A1, then A201:a400 (201
columns).

If the CQC is monitoring this response, maybe they'll have a better idea. That
CQC rep is very smart and uses Access a lot!

(or maybe any other smart Access user (a bit redundant???) can jump in.)
 
D

Debra Dalgleish

Dave's right -- an Access table has a maximum of 255 fields, so 400 rows
of data won't fit in one record.

Assuming the Access table is already created, are there specific fields
that you're trying to update?
 
D

Dave Peterson

To the OP. This is _the_ CQC responding.

Debra said:
Dave's right -- an Access table has a maximum of 255 fields, so 400 rows
of data won't fit in one record.

Assuming the Access table is already created, are there specific fields
that you're trying to update?
Click to expand...
 
Top