P
p.numminen
How I convert an Excel spreadsheet into an Access database?
This is Office 2003.
This is Office 2003.
J
Jason Lepack
How does one fit an elephant in a Safeway?
A spreadsheet doesn't just magically change into a database. It all
depends on what data you're trying to store. The one thing that you
don't want to do is create a table that mimics your spreadsheet.
Check out:
http://www.troubleshooters.com/littstip/ltnorm.html
If that isn't enough then you could post back with the structure of
your spreadsheet and an explanation of the data and what expected
changes there are in the data, then we could really help you.
Cheers,
Jason Lepack
A spreadsheet doesn't just magically change into a database. It all
depends on what data you're trying to store. The one thing that you
don't want to do is create a table that mimics your spreadsheet.
Check out:
http://www.troubleshooters.com/littstip/ltnorm.html
If that isn't enough then you could post back with the structure of
your spreadsheet and an explanation of the data and what expected
changes there are in the data, then we could really help you.
Cheers,
Jason Lepack
P
pete johnson
from office 2000
1. open your excel spreadsheet
2. select all (control A) (will select all)
3. (control c) to copy it into the clipboard. you are done with excel
here, but leave it open.
4. open ms access
5. click on tables. click on "create table by entering data"
6. the cursor should now be in the upper left corner.
7. (control a) to select all rows in the new table. or click on that block
to the upper left of the table.
8. (control V) will paste the excel data into your new table. save the
table with an appropiate name.
9. open the table in design and name the columns (fields) with appropiate
names and assign proper data types.
10. that's it!! i just did it on one of my excel spreadsheets.
pete
1. open your excel spreadsheet
2. select all (control A) (will select all)
3. (control c) to copy it into the clipboard. you are done with excel
here, but leave it open.
4. open ms access
5. click on tables. click on "create table by entering data"
6. the cursor should now be in the upper left corner.
7. (control a) to select all rows in the new table. or click on that block
to the upper left of the table.
8. (control V) will paste the excel data into your new table. save the
table with an appropiate name.
9. open the table in design and name the columns (fields) with appropiate
names and assign proper data types.
10. that's it!! i just did it on one of my excel spreadsheets.
pete
P
pete johnson
my answer will transfer your data if it is fairly clean. any formulas, etc
are another story. (manual work).
are another story. (manual work).
P
pete johnson
macros are quite limited, you should use vba or the access basic language.
J
Jason Lepack
But if that's all you're going to do, then why even use a database?
Why not just spend your time learning to write macros, it'd be a much
more efficient use of your time than trying to learn to use a database
like a spreadsheet.
Why not just spend your time learning to write macros, it'd be a much
more efficient use of your time than trying to learn to use a database
like a spreadsheet.
J
Jeff Boyce
As Jason points out, sticking an Excel spreadsheet in Access does not create
an Access database.
The first step in building an Access database probably needs to be spent
with the computer off, using paper and pencil to draw out the things about
which you will want to store data ("entities") and the ways each thing is
related to the others ("relationships").
Access can't use many of its features/functions if you feed it 'sheet data.
--
Regards
Jeff Boyce
Microsoft Office/Access MVP
Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/
Microsoft Registered Partner
https://partner.microsoft.com/
an Access database.
The first step in building an Access database probably needs to be spent
with the computer off, using paper and pencil to draw out the things about
which you will want to store data ("entities") and the ways each thing is
related to the others ("relationships").
Access can't use many of its features/functions if you feed it 'sheet data.
--
Regards
Jeff Boyce
Microsoft Office/Access MVP
Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/
Microsoft Registered Partner
https://partner.microsoft.com/
J
Jason Lepack
Macros "are" VBA...
L
Larry Linson
Jason Lepack said:
No, that's the terminology used in Word and Excel, but not in Access.
Macros are a separate entity, not Visual Basic for Applications code. In
your Database Window, you'll see a separate tab for Macros, and another for
Modules -- the VBA code lives in Modules (either the standard modules or
class modules in this tab, or the Form/Report class modules associated with
Forms and Reports).
Larry Linson
Microsoft Access MVP
L
Larry Linson
Some seem to have misread the question. It is not "how do I convert an Excel
spreadsheet into a single Access table", but "into an Access database".
Spreadsheets tend to be big unnormalized flat files, if suitable at all. Of
course, a spreadsheet can have almost every cell as the result of a
calculation, with only a few inputs, and that may not be suitable for
conversion to a database.
As Jeff rightly points out, even if the spreadsheet has rows and columns of
data, and IS suitable for conversion, the first step is to analyze and
decide the structure of your database, tables, etc.
Once that is done, it is perfectly acceptable to import the entire datasheet
as one huge table -- in my experience, it will be easier to work from that
unnormalized table to extract the data for a normalized database, than to
try to get each piece from the original spreadsheet (but I'm willing to
admit, that may be more a matter of preference than 'fact').
Unfortunately for the p.numminen, there's no real "generic" answer on
creating a database from a spreadsheet that works for all, or most, or even
many situations. It is very data-specific.
Larry Linson
Microsoft Access MVP
P
Pete
I find excel is a good go between when using data from many platforms but I
deal in vehicle fleets so I'm talking a few thousand records compaired to a
million orders and usually working to clean out much dirty data from older
programs. If you in excel, select the data you want, shuffle the columns
around as needed and then name the range you want to import you should find
the Access get external data quite up the the job. Be careful to take the
time to set each columns type in the import screen to prevent dates from
becoming text and text with leading zeros from becoming the wrong number and
my favorite with registration numbers and VIN numbers, an E turning it in to
scientific notatation.
deal in vehicle fleets so I'm talking a few thousand records compaired to a
million orders and usually working to clean out much dirty data from older
programs. If you in excel, select the data you want, shuffle the columns
around as needed and then name the range you want to import you should find
the Access get external data quite up the the job. Be careful to take the
time to set each columns type in the import screen to prevent dates from
becoming text and text with leading zeros from becoming the wrong number and
my favorite with registration numbers and VIN numbers, an E turning it in to
scientific notatation.
M
MH
Excel is the worst program in the known universe to use for this purpose!
Try entering a bank sort code such as "21-08-97" - it is transformed into a
date of 21/08/1997!
Any numeris string is converted to a number but Excel only stores numbers to
15 significant figures so that 123456789123456789 becomes
123456789123456000!
At work we use codes consisting of 4 alpha characters followed by 2 numeric
e.g. "MARC90" Excel helpfully changes this into a date of 01 March 1990!
Oh, and in case you are wondering - there is no way to turn this off, there
is no setting you can change that will prevent Excel from making these
changes to your data!
MH
Try entering a bank sort code such as "21-08-97" - it is transformed into a
date of 21/08/1997!
Any numeris string is converted to a number but Excel only stores numbers to
15 significant figures so that 123456789123456789 becomes
123456789123456000!
At work we use codes consisting of 4 alpha characters followed by 2 numeric
e.g. "MARC90" Excel helpfully changes this into a date of 01 March 1990!
Oh, and in case you are wondering - there is no way to turn this off, there
is no setting you can change that will prevent Excel from making these
changes to your data!
MH
J
Jeff Boyce
If you give a program, any program, a series of characters, including digit
characters, you are asking the program to interpret what those characters
might represent. Some programs refuse entirely.
In Excel, prefacing a series of characters with the "'" apostrophe character
tells Excel to not interpret, but to treat the following data as text/string
data. You could use this to enter an East Coast USA zip code (some start
with one or two 0, but leading zeros in a numeric format are dropped).
Good luck
--
Regards
Jeff Boyce
Microsoft Office/Access MVP
Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/
Microsoft Registered Partner
https://partner.microsoft.com/
characters, you are asking the program to interpret what those characters
might represent. Some programs refuse entirely.
In Excel, prefacing a series of characters with the "'" apostrophe character
tells Excel to not interpret, but to treat the following data as text/string
data. You could use this to enter an East Coast USA zip code (some start
with one or two 0, but leading zeros in a numeric format are dropped).
Good luck
--
Regards
Jeff Boyce
Microsoft Office/Access MVP
Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/
Microsoft Registered Partner
https://partner.microsoft.com/
M
MH
Unfortunately (for me at least) I get these files from external customers,
so the damage is already done. We do have ways around these issues, except
for the loss of data in exponentials and 15 significant figures, but some
are hard to spot, like the MARC90 code being transformed into 10 March 1990.
We import everything as a string and manipulate it using string functions
before uploading it to SQL Server and the largest file we worked with this
time around was 2.7 million rows, so any debugging of data can take a while.
Thanks for the advice though, any ideas are better than no ideas!
)
MH
so the damage is already done. We do have ways around these issues, except
for the loss of data in exponentials and 15 significant figures, but some
are hard to spot, like the MARC90 code being transformed into 10 March 1990.
We import everything as a string and manipulate it using string functions
before uploading it to SQL Server and the largest file we worked with this
time around was 2.7 million rows, so any debugging of data can take a while.
Thanks for the advice though, any ideas are better than no ideas!
)MH
D
David W. Fenton
He was talking about doing it *in* Excel. The point was that if
you're not going to use Access properly, then leave it in Excel and
spend your time learning Excel macros (which is VBA).
J
Jason Lepack
Thanks for clarifying my point David. That's exactly what I meant.