Import 'Choice' Data into Access From Excel

[Fletcher]

Okay, I believe that I'm in over my head on this one, and I'll do my
best to explain.

I have two databases, one in access, and one in excel. The excel
database has been used for years in my facility and is in dire nead of
replaceing. That's where my access database comes in. My boss wants
me to bring in some of the data from the old datbase into the new one
to create an overlap of data mainly to demonstrate the powers of Access
vs. Excel to some upper level management before implimenting it to the
facility. Now you're probably wondering where my problem is right?
Well here it goes:

The excel database has too much information in it to be blunt. We
would like to filter out older data and some columns. It has over 5000
rows of information in it and we would only like to import a few on
that scale. Perhaps 50 entries.

I've tried the import wizard and it won't let me import to the table
that I want and I can't ask it to filter out the first 4950 entries.

I've tried copy and paste, but that did not work at all.

I would imagine that I could delete the rows and columns in excel that
I don't want, but that would cause unheard of damage to the way the
system works now.

Can anyone help me?

Thanks,
Fletcher

 

[Klatuu]

First order of business. Excel is not a database.
If you plan to have one flat table, like the spreadsheet in Excel, in the
Access database, you need to talk to someone with experience in designing
relational databases; otherwise, you would be just as well off staying with
Excel.

Now, if you just want a subset of the data from the Excel spreadsheet, you
can Link the spreadsheet as if it were a table and use append queries to
populate your Access tables.

Seriously, do review your data structure. Get a book on database
normalization, and/or ask some questions in the news groups. If you are
coming from an Excel background and have not developed a relation database
before, there are a lot of traps to avoid and some good things to learn so
you don't make things harder than they need to be.

Good Luck with it.

 

[Fletcher]

First order of business. Excel is not a database.

Right, we absolutely know this. That is why we're trying to get away
from it and to Access. We've been having trouble with the operators
getting into the script for our macros and causing hell.

If you plan to have one flat table, like the spreadsheet in Excel, in the
Access database, you need to talk to someone with experience in designing
relational databases; otherwise, you would be just as well off staying with
Excel.

We don't really want to create any relationships. We are building this
database simply to collect data and link up with an outside Statistical
Process Control (SPC) application. There is no real reason to build a
complex database for this. We only want a table for each machine in
it. I've heard that this is not the best type of database to build,
but we REALLY have no reason to do anything different.

Now, if you just want a subset of the data from the Excel spreadsheet, you
can Link the spreadsheet as if it were a table and use append queries to
populate your Access tables.

All we really want to do is bring in some of the recent data to be
viewed with this new SPC application with the link through access and
show management how it works before implimentation. And since we're
building this new data collection database we have decided that we've
been collecting some irrelevant data and would like to filter it out.
I was wondering if there was a way to do this without copying and
pasting the desired data into a new spreadsheet, then importing (which
is what I'm doing now). It works, but I was hoping for an easier, more
automated way.

Good Luck with it.

Thank you.

 

[Klatuu]

Understand. I have only a few comments.
First, If you have had problems with users messing with the code in Excel,
then unless you implement security or at least deliver only an MDE file, they
will still be able to do that.

Why do you need a different table for each machine? If there will be
differences in the field among the tables, then it is reasonable. If all
data elements are the same for all machines, then add a field that identifies
the machine and use one table.

Since you have multiple operators, it will be important that you split your
database, put the back end on a share folder that all operators have
permissions to and install a copy of the front end on each operators computer.

As to getting the data from Excel, If you don't want to use queries to
manipulate the data, then cut and paste will probably do.

 

[Fletcher]

First, If you have had problems with users messing with the code in Excel,

then unless you implement security or at least deliver only an MDE file, they
will still be able to do that.

The security that we are putting in place for this database is having
the forms that take up the whole screen and allowing only the engineers
to get into the system. It's not that the operators would purposfully
mess up the code, it is just that they would accidentally get into the
system and delete a cell here or hit a button there and it could be
detrimental to the way the whole thing worked.

Why do you need a different table for each machine? If there will be
differences in the field among the tables, then it is reasonable. If all
data elements are the same for all machines, then add a field that identifies
the machine and use one table.

You are the first to respond in this way. Thank you. We have
different data for each machine so a single table would be unreasonably
difficult. Everyone else who responded to a message asking for help
told me that I was going about it wrong not using relationships and the
like. I hope you respond to more of my posts.

I have done as indicated with a field identifying different machines in
tables where I can log machines that have similiar data associated with
each.

Since you have multiple operators, it will be important that you split your
database, put the back end on a share folder that all operators have
permissions to and install a copy of the front end on each operators computer.

I'm not familiar with this idea of the front end and back end, but I
don't believe that it will be an issue for our company. I don't know
if you are familiar with a citrix environment, but that is what our
company uses for its networking. Essentially it replaces computers for
each users with what we call "ding-boxes." Ding-boxes are essentially
very small computers without any drives. They have ports in the back
for monitor, mouse, keyboard, power and network cable. Users log
directly into a server from these using a name and password. This has
been especially useful in a production facility where space is minimal.

So what we have to do is publish our database onto the servers in
shared folders (as you indicated) that the operators can access if
their user name is given permission.

As to getting the data from Excel, If you don't want to use queries to
manipulate the data, then cut and paste will probably do.

I have tried cutting and pasting from excel to Access and access gives
me the error that the data is too long. So what I've been doing is
going through the current spreadsheets and cutting chunks of the
desired data and pasting it to a new sheet, then importing it to
access. This works, but is alot of work for little output.

Thank you again for your help.

 

[Klatuu]

See comments below:

The security that we are putting in place for this database is having
the forms that take up the whole screen and allowing only the engineers
to get into the system. It's not that the operators would purposfully
mess up the code, it is just that they would accidentally get into the
system and delete a cell here or hit a button there and it could be
detrimental to the way the whole thing worked.


You are the first to respond in this way. Thank you. We have
different data for each machine so a single table would be unreasonably
difficult. Everyone else who responded to a message asking for help
told me that I was going about it wrong not using relationships and the
like. I hope you respond to more of my posts.

I have done as indicated with a field identifying different machines in
tables where I can log machines that have similiar data associated with
each.


I'm not familiar with this idea of the front end and back end, but I
don't believe that it will be an issue for our company. I don't know
if you are familiar with a citrix environment, but that is what our
company uses for its networking. Essentially it replaces computers for
each users with what we call "ding-boxes." Ding-boxes are essentially
very small computers without any drives. They have ports in the back
for monitor, mouse, keyboard, power and network cable. Users log
directly into a server from these using a name and password. This has
been especially useful in a production facility where space is minimal.

So what we have to do is publish our database onto the servers in
shared folders (as you indicated) that the operators can access if
their user name is given permission.

Splitting the database is very important in an environment like yours. I
would suggest you do some research. Basically, what happens in the split
process (Tools, Database Utilities, Database Splitter) is that all tables are
put into a different mdb with _be added to the name. For example if your mdb
is named Foobah.mdb, it will create two mdbs. Foobah_be.mdb which will
contain all your tables. Foobah.mdb will contain all other objects (forms,
reports, queries, etc). It will automatically link Foobah to the tables in
Foobah_be.

This may seem to be overkill, but it is not. It will make your life easier
for development. With a non split mdb, every time you need to add
functionality or correct an error, you have to get everybody out of the
database and move all your objects to the production database before it can
be used again. With a split database, all changes are made to a copy of the
front end. Once that is done, you just copy the new front end over the old
one.

I have tried cutting and pasting from excel to Access and access gives
me the error that the data is too long. So what I've been doing is
going through the current spreadsheets and cutting chunks of the
desired data and pasting it to a new sheet, then importing it to
access. This works, but is alot of work for little output.

The problem you are having is that Access will accept a maximum of 255
characters in a text field, but Excel does not have that restraint.

Post back if you have more questions as you move forward.

 

[Fletcher]

Thanks for your help. I guess I'll have to do it the way that I'm
doing it now. I'll look into splitting the database as you said.
Thanks for the advice as well. I'll let you know how everything turns
out if possible.

 

[DavidAtCaspian]

What I would do, and have been this morning (UK) is
Structure your access table the way you want it. (ie design it to have the
fields you want)

Open a module and add excel as a reference.

You can then open your spreadsheet with
excel.application.workbooks.open("your file")

You can navigate round the sheet using 'range' by a column letter, and a
variable for row number.
If you set for example rNum to your first row with data, you can pull in one
at a time the cells you want into access.
When you have all the fields full for that record, add 1 to rNum and go
round the loop.

For a one off, you can see from excel what the biggest row number is.

Once you have everything in access, run a make table query just to give
another table showing what you do want. (or just bring in the whole lot and
reduce with a make table query)
You can show it all off to your managers, and if you decide more columns are
wanted from the spreadsheet, it is easy enough to tweak your code and produce
another clean copy.

Here is abit of code from mine earlier. - In my case someone has tarted up
the top of the spreadsheet and I just work down column A till I find a data
record (where position 5 is a hyphen (-). (Left out all the Dims for brevity)
(BUT F must be a variant !!)

The first part pops up a dialog to select the spreadsheet.
The password is stored in a table and used to open the spreadsheet.
Then I select the right worksheet from it and work down till I find the data.
My work loop simply adds 1 to rNum till I lose the hyphen in position 5.

It may look crude, but it works, and you can choose which columns to move to
which fields. (eg within an edit/addnew/update: )

![destinationfield] = ws.range("G" & rNum)


F = False

Do While F = False

F = Excel.Application.GetOpenFilename("Excel Files (*.xls), *.xls")

If F = False Then Exit Sub


Loop

Set Parms = CurrentDb.OpenRecordset("Parms")
vPW = Parms![sspassword]

Set wb = Excel.Application.Workbooks.Open(F, False, True, , vPW)
Set ws = wb.Worksheets("SheetName")
rNum = 1

Do Until InStr(1, ws.Range("A" & rNum), "-") = 5
rNum = rNum + 1

Loop

 

[DavidAtCaspian]

Nearly forgot: Be very careful of null, as it seems to have a mind of its (
or is someone's idea of a joke)

This one which is supposed only to update a date if newer

If ws.Range("H" & rNum) > ![lastactivity]

does not work if ![lastactiivity] is null. - So a null date is greater than
any actual date.

And this one

if ![ptnamelong] <> ws.Range("G" & rNum)

returns false if ![ptnamelong] is null. So although null is clearly not the
same as the value in the spreadsheet, it is nevertheless neither equal to it,
nor not equal to it.

No doubt someone at Micrososft will tell you that's the way it's supposed to
be.

I have also done some running totals in the past where 63 + null actually
equalled null (although an MVP assured me that it didn't)

What I would do, and have been this morning (UK) is
Structure your access table the way you want it. (ie design it to have the
fields you want)

Open a module and add excel as a reference.

You can then open your spreadsheet with
excel.application.workbooks.open("your file")

You can navigate round the sheet using 'range' by a column letter, and a
variable for row number.
If you set for example rNum to your first row with data, you can pull in one
at a time the cells you want into access.
When you have all the fields full for that record, add 1 to rNum and go
round the loop.

For a one off, you can see from excel what the biggest row number is.

Once you have everything in access, run a make table query just to give
another table showing what you do want. (or just bring in the whole lot and
reduce with a make table query)
You can show it all off to your managers, and if you decide more columns are
wanted from the spreadsheet, it is easy enough to tweak your code and produce
another clean copy.

Here is abit of code from mine earlier. - In my case someone has tarted up
the top of the spreadsheet and I just work down column A till I find a data
record (where position 5 is a hyphen (-). (Left out all the Dims for brevity)
(BUT F must be a variant !!)

The first part pops up a dialog to select the spreadsheet.
The password is stored in a table and used to open the spreadsheet.
Then I select the right worksheet from it and work down till I find the data.
My work loop simply adds 1 to rNum till I lose the hyphen in position 5.

It may look crude, but it works, and you can choose which columns to move to
which fields. (eg within an edit/addnew/update: )

![destinationfield] = ws.range("G" & rNum)


F = False

Do While F = False

F = Excel.Application.GetOpenFilename("Excel Files (*.xls), *.xls")

If F = False Then Exit Sub


Loop

Set Parms = CurrentDb.OpenRecordset("Parms")
vPW = Parms![sspassword]

Set wb = Excel.Application.Workbooks.Open(F, False, True, , vPW)
Set ws = wb.Worksheets("SheetName")
rNum = 1

Do Until InStr(1, ws.Range("A" & rNum), "-") = 5
rNum = rNum + 1

Loop

Okay, I believe that I'm in over my head on this one, and I'll do my
best to explain.

I have two databases, one in access, and one in excel. The excel
database has been used for years in my facility and is in dire nead of
replaceing. That's where my access database comes in. My boss wants
me to bring in some of the data from the old datbase into the new one
to create an overlap of data mainly to demonstrate the powers of Access
vs. Excel to some upper level management before implimenting it to the
facility. Now you're probably wondering where my problem is right?
Well here it goes:

The excel database has too much information in it to be blunt. We
would like to filter out older data and some columns. It has over 5000
rows of information in it and we would only like to import a few on
that scale. Perhaps 50 entries.

I've tried the import wizard and it won't let me import to the table
that I want and I can't ask it to filter out the first 4950 entries.

I've tried copy and paste, but that did not work at all.

I would imagine that I could delete the rows and columns in excel that
I don't want, but that would cause unheard of damage to the way the
system works now.

Can anyone help me?

Thanks,
Fletcher

 

[Fletcher]

Well I'm doing okay with my importing of data (I'm using the wizard).
Except when I try to do some of the tables I get this error:

Method 'Columns' of object 'IImexGrid' failed

I have no idea what it means. If I click ok on that error and go on to
click the finish button it tells me that it can't import because of an
error. No idea what's going on.

 

[Douglas J. Steele]

Null essentially means "Unknown". What would you expect 63 + Unknown to be?

Whenever there's a chance that the value in the recordset might be null, use
the Nz function to provide a default value:

If ws.Range("H" & rNum) > Nz(![lastactivity], "")

or

if Nz(![ptnamelong], "") <> ws.Range("G" & rNum)


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Nearly forgot: Be very careful of null, as it seems to have a mind of its
(
or is someone's idea of a joke)

This one which is supposed only to update a date if newer

If ws.Range("H" & rNum) > ![lastactivity]

does not work if ![lastactiivity] is null. - So a null date is greater
than
any actual date.

And this one

if ![ptnamelong] <> ws.Range("G" & rNum)

returns false if ![ptnamelong] is null. So although null is clearly not
the
same as the value in the spreadsheet, it is nevertheless neither equal to
it,
nor not equal to it.

No doubt someone at Micrososft will tell you that's the way it's supposed
to
be.

I have also done some running totals in the past where 63 + null actually
equalled null (although an MVP assured me that it didn't)

What I would do, and have been this morning (UK) is
Structure your access table the way you want it. (ie design it to have
the
fields you want)

Open a module and add excel as a reference.

You can then open your spreadsheet with
excel.application.workbooks.open("your file")

You can navigate round the sheet using 'range' by a column letter, and a
variable for row number.
If you set for example rNum to your first row with data, you can pull in
one
at a time the cells you want into access.
When you have all the fields full for that record, add 1 to rNum and go
round the loop.

For a one off, you can see from excel what the biggest row number is.

Once you have everything in access, run a make table query just to give
another table showing what you do want. (or just bring in the whole lot
and
reduce with a make table query)
You can show it all off to your managers, and if you decide more columns
are
wanted from the spreadsheet, it is easy enough to tweak your code and
produce
another clean copy.

Here is abit of code from mine earlier. - In my case someone has tarted
up
the top of the spreadsheet and I just work down column A till I find a
data
record (where position 5 is a hyphen (-). (Left out all the Dims for
brevity)
(BUT F must be a variant !!)

The first part pops up a dialog to select the spreadsheet.
The password is stored in a table and used to open the spreadsheet.
Then I select the right worksheet from it and work down till I find the
data.
My work loop simply adds 1 to rNum till I lose the hyphen in position 5.

It may look crude, but it works, and you can choose which columns to move
to
which fields. (eg within an edit/addnew/update: )

![destinationfield] = ws.range("G" & rNum)


F = False

Do While F = False

F = Excel.Application.GetOpenFilename("Excel Files (*.xls), *.xls")

If F = False Then Exit Sub


Loop

Set Parms = CurrentDb.OpenRecordset("Parms")
vPW = Parms![sspassword]

Set wb = Excel.Application.Workbooks.Open(F, False, True, , vPW)
Set ws = wb.Worksheets("SheetName")
rNum = 1

Do Until InStr(1, ws.Range("A" & rNum), "-") = 5
rNum = rNum + 1

Loop

Okay, I believe that I'm in over my head on this one, and I'll do my
best to explain.

I have two databases, one in access, and one in excel. The excel
database has been used for years in my facility and is in dire nead of
replaceing. That's where my access database comes in. My boss wants
me to bring in some of the data from the old datbase into the new one
to create an overlap of data mainly to demonstrate the powers of Access
vs. Excel to some upper level management before implimenting it to the
facility. Now you're probably wondering where my problem is right?
Well here it goes:

The excel database has too much information in it to be blunt. We
would like to filter out older data and some columns. It has over 5000
rows of information in it and we would only like to import a few on
that scale. Perhaps 50 entries.

I've tried the import wizard and it won't let me import to the table
that I want and I can't ask it to filter out the first 4950 entries.

I've tried copy and paste, but that did not work at all.

I would imagine that I could delete the rows and columns in excel that
I don't want, but that would cause unheard of damage to the way the
system works now.

Can anyone help me?

Thanks,
Fletcher

 

[DavidAtCaspian]

I don't see why 63 + null can't be 63 like it always used to be. (And indeed
like another MVP assured me it still was not long ago)

And as for having to pass every database field through NZ in coding. In the
words of the tennis player, you cannot be serious.

Null essentially means "Unknown". What would you expect 63 + Unknown to be?

Whenever there's a chance that the value in the recordset might be null, use
the Nz function to provide a default value:

If ws.Range("H" & rNum) > Nz(![lastactivity], "")

or

if Nz(![ptnamelong], "") <> ws.Range("G" & rNum)


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Nearly forgot: Be very careful of null, as it seems to have a mind of its
(
or is someone's idea of a joke)

This one which is supposed only to update a date if newer

If ws.Range("H" & rNum) > ![lastactivity]

does not work if ![lastactiivity] is null. - So a null date is greater
than
any actual date.

And this one

if ![ptnamelong] <> ws.Range("G" & rNum)

returns false if ![ptnamelong] is null. So although null is clearly not
the
same as the value in the spreadsheet, it is nevertheless neither equal to
it,
nor not equal to it.

No doubt someone at Micrososft will tell you that's the way it's supposed
to
be.

I have also done some running totals in the past where 63 + null actually
equalled null (although an MVP assured me that it didn't)

What I would do, and have been this morning (UK) is
Structure your access table the way you want it. (ie design it to have
the
fields you want)

Open a module and add excel as a reference.

You can then open your spreadsheet with
excel.application.workbooks.open("your file")

You can navigate round the sheet using 'range' by a column letter, and a
variable for row number.
If you set for example rNum to your first row with data, you can pull in
one
at a time the cells you want into access.
When you have all the fields full for that record, add 1 to rNum and go
round the loop.

For a one off, you can see from excel what the biggest row number is.

Once you have everything in access, run a make table query just to give
another table showing what you do want. (or just bring in the whole lot
and
reduce with a make table query)
You can show it all off to your managers, and if you decide more columns
are
wanted from the spreadsheet, it is easy enough to tweak your code and
produce
another clean copy.

Here is abit of code from mine earlier. - In my case someone has tarted
up
the top of the spreadsheet and I just work down column A till I find a
data
record (where position 5 is a hyphen (-). (Left out all the Dims for
brevity)
(BUT F must be a variant !!)

The first part pops up a dialog to select the spreadsheet.
The password is stored in a table and used to open the spreadsheet.
Then I select the right worksheet from it and work down till I find the
data.
My work loop simply adds 1 to rNum till I lose the hyphen in position 5.

It may look crude, but it works, and you can choose which columns to move
to
which fields. (eg within an edit/addnew/update: )

![destinationfield] = ws.range("G" & rNum)


F = False

Do While F = False

F = Excel.Application.GetOpenFilename("Excel Files (*.xls), *.xls")

If F = False Then Exit Sub


Loop

Set Parms = CurrentDb.OpenRecordset("Parms")
vPW = Parms![sspassword]

Set wb = Excel.Application.Workbooks.Open(F, False, True, , vPW)
Set ws = wb.Worksheets("SheetName")
rNum = 1

Do Until InStr(1, ws.Range("A" & rNum), "-") = 5
rNum = rNum + 1

Loop









:

Okay, I believe that I'm in over my head on this one, and I'll do my
best to explain.

I have two databases, one in access, and one in excel. The excel
database has been used for years in my facility and is in dire nead of
replaceing. That's where my access database comes in. My boss wants
me to bring in some of the data from the old datbase into the new one
to create an overlap of data mainly to demonstrate the powers of Access
vs. Excel to some upper level management before implimenting it to the
facility. Now you're probably wondering where my problem is right?
Well here it goes:

The excel database has too much information in it to be blunt. We
would like to filter out older data and some columns. It has over 5000
rows of information in it and we would only like to import a few on
that scale. Perhaps 50 entries.

I've tried the import wizard and it won't let me import to the table
that I want and I can't ask it to filter out the first 4950 entries.

I've tried copy and paste, but that did not work at all.

I would imagine that I could delete the rows and columns in excel that
I don't want, but that would cause unheard of damage to the way the
system works now.

Can anyone help me?

Thanks,
Fletcher