Error importing Excel spreadsheet into an existing Access table.

[StargateFanFromWork]

Well, I'm stumped. I've spent a long time googling the archives looking for
solutions to the import error problem and I've tried everything that I've
come across in the posts re this and still getting the darned error.

The table to import the Excel spreadsheet column's data into has a primary
key. I've tried to import the Excel sheet both with an identically-named
column of "ID" and without so A2K knows which records we're dealing with. I
earlier took out all the periods and commas in the Excel spreadsheet. It's
just the column info that is in the print area also along with a header cell
(tried with _and_ without a header. No difference.) No periods or commas
in the tab sheet name, either. ETC., ETC., ETC.

I'm stumped. When I import to a separate table, fine. No problems. But
even though header in field in Access the same as header in Excel sheet
column, no can do re importing into the existing table.

I then tried importing to a separate table and trying to figure out the
"append query", but no good. I'm already working here tonight with a lot of
unpaid overtime, so hope to get out soon <g>.

What else can I try? I made a mistake in an input mask for this field in
the table and the data was subsequently deleted. Yeah, newbies, eh! I am
really hoping I don't have to start from scratch again with the Excel
spreadsheet as this has been a lot of work so far. And this import into a
table technique will be extremely useful to know how to do. I can't tell
you how many times in the past I've started "developing" an Excel
spreadsheet database only to have my boss come along and give me _another_
Excel spreadsheet with another dept's data to add to the first. Yet I've
already started <g>. No problem, adjust the columns and add to the one I'm
creating. I'll need to know how to do the same for any Access db's I'm
working in.

Any help would be greatly appreciated.

(I also don't want to have to go in an type the missing data <sigh>.)

Thanks! D

 

[Vincent Johns]

Well, I'm stumped. I've spent a long time googling the archives looking for
solutions to the import error problem and I've tried everything that I've
come across in the posts re this and still getting the darned error.

The table to import the Excel spreadsheet column's data into has a primary
key. I've tried to import the Excel sheet both with an identically-named
column of "ID" and without so A2K knows which records we're dealing with. I
earlier took out all the periods and commas in the Excel spreadsheet. It's
just the column info that is in the print area also along with a header cell
(tried with _and_ without a header. No difference.) No periods or commas
in the tab sheet name, either. ETC., ETC., ETC.

Don't worry about the primary key of your Access Table -- all of the
records that you're importing are new, anyway, so they are NOT already
linked to anything else in your Access database. You can update the
links later, maybe using an Update Query, after you have checked for
duplicate records and other mistakes.

I'm stumped. When I import to a separate table, fine. No problems. But
even though header in field in Access the same as header in Excel sheet
column, no can do re importing into the existing table.

I then tried importing to a separate table and trying to figure out the
"append query", but no good. I'm already working here tonight with a lot of
unpaid overtime, so hope to get out soon <g>.

That's not a bad thing to do; I often do it that way, so I can check the
data in the new (temporary) Table for mistakes before combining them
with the existing Access Table. And they can be combined either with an
Append Query or via the Copy and Paste functions.

What else can I try? I made a mistake in an input mask for this field in
the table and the data was subsequently deleted. Yeah, newbies, eh!

Umm.... you DID make a backup copy of your database file before you
embarked on this enterprise, didn't you??? You can re-import any lost
Tables into your current database from the old (backup) database,
without harming the old database file.

I am
really hoping I don't have to start from scratch again with the Excel
spreadsheet as this has been a lot of work so far. And this import into a
table technique will be extremely useful to know how to do.

You might find it easier to import these data if you select, in Excel,
only the Range you want to import, and give it a name. Then, in Access,
ask the Import Wizard to show you the Excel file's named Ranges. That
allows you to bypass Worksheet titles and other such flotsam.

I can't tell
you how many times in the past I've started "developing" an Excel
spreadsheet database only to have my boss come along and give me _another_
Excel spreadsheet with another dept's data to add to the first. Yet I've
already started <g>. No problem, adjust the columns and add to the one I'm
creating. I'll need to know how to do the same for any Access db's I'm
working in.

But there's a subtle difference in format. In Excel, you'll probably
either use a similarly-formatted Worksheet, or put the new data into new
columns to the right of the old ones.

In Access, it will likely work better for you to put the new data into
the same Table as the previous departments' data -- BUT to also include
a field in that Table that identifies the department. (This would serve
the same function as the label on the tab of the department's
Worksheet.) Such a field would have the same value (the department's
name, e.g.) in all the records in that Table that relate to that department.

Any help would be greatly appreciated.

(I also don't want to have to go in an type the missing data <sigh>.)

Thanks! D

No, you definitely don't want that!

But if you have lots of trouble importing from Excel, another tactic
might be to export from Excel into a tab-delimited text file, examine
and edit the text file (maybe in MS Word), and then to import the text
file into Access.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[StargateFanFromWork]

StargateFanFromWork wrote:
[snip]

The table to import the Excel spreadsheet column's data into has a primary
key. I've tried to import the Excel sheet both with an identically-named
column of "ID" and without so A2K knows which records we're dealing with. I
earlier took out all the periods and commas in the Excel spreadsheet. It's
just the column info that is in the print area also along with a header cell
(tried with _and_ without a header. No difference.) No periods or commas
in the tab sheet name, either. ETC., ETC., ETC.

Don't worry about the primary key of your Access Table -- all of the
records that you're importing are new, anyway, so they are NOT already
linked to anything else in your Access database. You can update the
links later, maybe using an Update Query, after you have checked for
duplicate records and other mistakes.

Darn, in this case, it would have made a difference. I finally got it to
work (I'll explain below), but the behaviour was _not_ what was needed
<sigh>. <g>

[snip]

That's not a bad thing to do; I often do it that way, so I can check the

Yeah, sounds like it, but the instructions for this could very well be in
Chinese for all I could make of them <lol>. I'll save that for further down
the road. I was here in Excel about 2 years ago and have come a long way;
just have to be patient to move out of this frustrating newbie phase in
Access, too.

[snip]

Umm.... you DID make a backup copy of your database file before you
embarked on this enterprise, didn't you??? You can re-import any lost
Tables into your current database from the old (backup) database,
without harming the old database file.

Oh, yes, I did! I remember yesterday looking for ways to re-import, but
couldn't find it. D'uh! The things you don't think of to pursue better out
of ignorance! <g> This would be ideal situation.

Oh, but wait, do I lose all the extras I built into the new db? Is it
possible just to import one field and its data? That would be the best.







[snip]

You might find it easier to import these data if you select, in Excel,
only the Range you want to import, and give it a name. Then, in Access,
ask the Import Wizard to show you the Excel file's named Ranges. That
allows you to bypass Worksheet titles and other such flotsam.

Well, nothing worked. The ranges didn't work, nothing. So obviously
something was in the way.

However, what did work, even if not correctly, was when I exported the
existing database into an XL2K spreadsheet like you mentioned we could do.
That was so neat. What I got looked like the original spreadsheet so it
became obvious at that point just to add the deleted data back in and
re-import into the table.

The problem was that it put that information into NEW records and didn't put
them back where they were before <that was too funny>. Looking back, I
don't know why I thought this type of magic would just automatically occur
<g>!

Well, at least I've learned what NOT to do with input masks so that data
isn't deleted! <g>

[snip]

But there's a subtle difference in format. In Excel, you'll probably
either use a similarly-formatted Worksheet, or put the new data into new
columns to the right of the old ones.

You're right. I figured that it would be differences in format I was not
accommodating.

In Access, it will likely work better for you to put the new data into
the same Table as the previous departments' data -- BUT to also include
a field in that Table that identifies the department. (This would serve
the same function as the label on the tab of the department's
Worksheet.) Such a field would have the same value (the department's
name, e.g.) in all the records in that Table that relate to that

department.

Hmmm, makes sense. I'll translate that to other situations. My supervisor
starts with a spreadsheet and gets me to work on it, then updates it and
requests that I append/integrate. That happens a lot. That's why it would
have been nice to figure out how to have replaced the deleted data as it
would mimic this.

I went to the library again last night. No good books on Access available.
I'll have to hunt down some again and request them. The few that I've
managed to get after longish waiting periods have not been what I need.

[snip]

But if you have lots of trouble importing from Excel, another tactic
might be to export from Excel into a tab-delimited text file, examine
and edit the text file (maybe in MS Word), and then to import the text
file into Access.

Thanks so much! It was great learning that export/re-import to/from Excel
trick!

Cheers! D

 

[Vincent Johns]

StargateFanFromWork wrote: [...]

Don't worry about the primary key of your Access Table -- all of the
records that you're importing are new, anyway, so they are NOT already
linked to anything else in your Access database. You can update the
links later, maybe using an Update Query, after you have checked for
duplicate records and other mistakes.

Darn, in this case, it would have made a difference. I finally got it to
work (I'll explain below), but the behaviour was _not_ what was needed
<sigh>. <g>
Woops.


[...]
Yeah, sounds like it, but the instructions for this could very well be in
Chinese for all I could make of them <lol>. I'll save that for further down
the road. I was here in Excel about 2 years ago and have come a long way;
just have to be patient to move out of this frustrating newbie phase in
Access, too.

OK, an Append Query may not be useful for a newbie, but you can also use
plain old Copy and Paste operations (if there are not too many records
and you do it carefully), sort of the same as if you were moving stuff
around in MS Word or Notepad.

Oh, but wait, do I lose all the extras I built into the new db?

No, if you open the new db and import records from other versions into
it. Yes, if you close the new db and open an older version instead.

Is it
possible just to import one field and its data? That would be the best.

Yes, you can, if that's all you need. But if your records (rows in the
Excel file) actually contain more than just the one field (column in
Excel), you'll incur headaches as you try to put the corresponding field
values back together again in Access.

And even if you CAN get away with importing just one field now, it won't
be long before you're stuck with another situation, the new Excel list
containing several fields that you have to import together. So I
suggest you become familiar now with how to do it. It'll pop up again
to byte you otherwise.

However, what did work, even if not correctly, was when I exported the
existing database into an XL2K spreadsheet like you mentioned we could do.
That was so neat. What I got looked like the original spreadsheet so it
became obvious at that point just to add the deleted data back in and
re-import into the table.

The problem was that it put that information into NEW records and didn't put
them back where they were before <that was too funny>. Looking back, I
don't know why I thought this type of magic would just automatically occur
<g>!

At this point, I would have either (depending on how many records were
involved) defined an Update Query to re-link the records, or else type
the linking key values in manually. (Well, not actually type them, but
display both Tables, and copy and paste the key values. But that's a
slow and error-prone way to do it, suitable only if you're trapped like
a wild animal in a cage and have only a short time to finish.)

Well, at least I've learned what NOT to do with input masks so that data
isn't deleted! <g>

Oh... input masks. Sorry, I didn't think of those, would have suggested
you turn them off before playing around with the Tables.

department.

Hmmm, makes sense. I'll translate that to other situations. My supervisor
starts with a spreadsheet and gets me to work on it, then updates it and
requests that I append/integrate. That happens a lot. That's why it would
have been nice to figure out how to have replaced the deleted data as it
would mimic this.

Oog. I was thinking of a one-time importation. If you have to maintain
synchronization with an Excel file, you might be lots better off by
linking to (not importing) a suitable named Range in the Excel file that
your boss keeps updating. The linked Table will look a lot like a real
Table in Access, except you can't change it, and it will stay up to date
nicely. You can run Queries based on that linked Table. But perhaps
that's something to think about for the future.

I went to the library again last night. No good books on Access available.
I'll have to hunt down some again and request them. The few that I've
managed to get after longish waiting periods have not been what I need.

Well, Microsoft does have some helpful Knowledge Base articles. For
that matter, there's a lot of useful stuff in the Access Help files, but
it's sometimes not easy to locate -- even with the kittycat (or other
Office Assistant) trying to show you the way.

Thanks so much! It was great learning that export/re-import to/from Excel
trick!

Cheers! D

This last is almost a universal basic interface trick (not just with
Excel). And it can often help to use Word (or Notepad or similar) to
massage those data, looking for stuff like phone numbers or postal codes
that are missing a character or are otherwise malformed, before trying
to re-import them.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[StargateFanFromWork]

StargateFanFromWork wrote:

[...]

[snip]

Darn, in this case, it would have made a difference. I finally got it to
work (I'll explain below), but the behaviour was _not_ what was needed
<sigh>. <g>
Woops.

[...]
Yeah, sounds like it, but the instructions for this could very well be in
Chinese for all I could make of them <lol>. I'll save that for further down
the road. I was here in Excel about 2 years ago and have come a long way;
just have to be patient to move out of this frustrating newbie phase in
Access, too.

OK, an Append Query may not be useful for a newbie, but you can also use
plain old Copy and Paste operations (if there are not too many records
and you do it carefully), sort of the same as if you were moving stuff
around in MS Word or Notepad.

I didn't find out how to make this work. As I learned yesterday,
copy/pasting an Access "column" is not the same as in Excel. Guess we all
learn that sooner or later, eh? <g> I'll figure it out eventually.

[snip]

And even if you CAN get away with importing just one field now, it won't
be long before you're stuck with another situation, the new Excel list
containing several fields that you have to import together. So I
suggest you become familiar now with how to do it. It'll pop up again
to byte you otherwise.

Byte? <g> Nice play on words.

You're right. And though I ran into endless difficulties yesterday with the
append query with lots of error messages, etc., I know I'll get it
eventually. I just have to do it once correctly, I know from experience,
and it'll be okay.

[snip]

At this point, I would have either (depending on how many records were
involved) defined an Update Query to re-link the records, or else type
the linking key values in manually. (Well, not actually type them, but
display both Tables, and copy and paste the key values. But that's a
slow and error-prone way to do it, suitable only if you're trapped like
a wild animal in a cage and have only a short time to finish.)

Understood and thanks for info.

Oh... input masks. Sorry, I didn't think of those, would have suggested
you turn them off before playing around with the Tables.

The classic "now he tells me" ... <g>

[snip]

Oog. I was thinking of a one-time importation. If you have to maintain
synchronization with an Excel file, you might be lots better off by
linking to (not importing) a suitable named Range in the Excel file that
your boss keeps updating. The linked Table will look a lot like a real
Table in Access, except you can't change it, and it will stay up to date
nicely. You can run Queries based on that linked Table. But perhaps
that's something to think about for the future.

Actually, despite the impression I gave above, it's only been recently and
only during initial setup phase of electronic files. I've had cases of
having to integrate up to 3 versions but it isn't always the case. But I
think that until I learn how to work better in Access, I'll merge the info
in Excel _first_, then convert to Access.

Well, Microsoft does have some helpful Knowledge Base articles. For
that matter, there's a lot of useful stuff in the Access Help files, but
it's sometimes not easy to locate -- even with the kittycat (or other
Office Assistant) trying to show you the way.

Yes, and I've used both. (Frankly, the MS KB rarely helps me out.
Terminology is almost always beyond me.) I'm actually finding the googling
the archives has been the best resource lately and it's now the very first
thing I do. I _hate_ bugging the groups for every darn little thing so much
though you've all been great about it! <g> I know I'm posting quite a bit
lately, but I'm getting over the extreme newbie stage and starting to enter
the "not-quite-so-thickheaded" newbie phase so that'll ease up <g>. Thank
goodness for the archives! Just this morning I found a solution to
something I never thought I'd find, yet didn't take too many mins. via
googling the group's archives. (I'll just have to make the time up tonight
after work <vbg>.)

[snip]

This last is almost a universal basic interface trick (not just with
Excel). And it can often help to use Word (or Notepad or similar) to
massage those data, looking for stuff like phone numbers or postal codes
that are missing a character or are otherwise malformed, before trying
to re-import them.

Great tip. Thanks for info.

Cheers!

 

[Vincent Johns]

[...]

OK, an Append Query may not be useful for a newbie, but you can also use
plain old Copy and Paste operations (if there are not too many records
and you do it carefully), sort of the same as if you were moving stuff
around in MS Word or Notepad.

I didn't find out how to make this work. As I learned yesterday,
copy/pasting an Access "column" is not the same as in Excel. Guess we all
learn that sooner or later, eh? <g> I'll figure it out eventually.

I'm not sure it's wise to recommend this, as it's easy to goof up your
data if you're not very careful, but you can paste data, sometimes, into
Table Datasheet View windows. The types and numbers of data have to
match, but it can be done. Ruining your Table can also easily be done,
so back up your file beforehand and carefully check your results afterward.

Another use of Copy and Paste (again, if the fields being pasted are a
subset of the Table to which you are pasting) is to copy an entire
Table, and then Paste, specifying that you want to append the contents
to an existing Table.

But both of these are techniques I use rarely. Append Queries are
easier to audit and to reuse, though they take a bit more time to set up
the first time.

[...]

Actually, despite the impression I gave above, it's only been recently and
only during initial setup phase of electronic files. I've had cases of
having to integrate up to 3 versions but it isn't always the case. But I
think that until I learn how to work better in Access, I'll merge the info
in Excel _first_, then convert to Access.

Even _after_ you're comfortable importing into Access, you may still
choose to do some of the work in Excel first. Do whatever makes sense.

Good luck!

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[StargateFanFromWork]

[...]

OK, an Append Query may not be useful for a newbie, but you can also use
plain old Copy and Paste operations (if there are not too many records
and you do it carefully), sort of the same as if you were moving stuff
around in MS Word or Notepad.

I didn't find out how to make this work. As I learned yesterday,
copy/pasting an Access "column" is not the same as in Excel. Guess we all
learn that sooner or later, eh? <g> I'll figure it out eventually.

I'm not sure it's wise to recommend this, as it's easy to goof up your
data if you're not very careful, but you can paste data, sometimes, into
Table Datasheet View windows. The types and numbers of data have to
match, but it can be done. Ruining your Table can also easily be done,
so back up your file beforehand and carefully check your results afterward.

Another use of Copy and Paste (again, if the fields being pasted are a
subset of the Table to which you are pasting) is to copy an entire
Table, and then Paste, specifying that you want to append the contents
to an existing Table.

But both of these are techniques I use rarely. Append Queries are
easier to audit and to reuse, though they take a bit more time to set up
the first time.

[...]

Actually, despite the impression I gave above, it's only been recently and
only during initial setup phase of electronic files. I've had cases of
having to integrate up to 3 versions but it isn't always the case. But I
think that until I learn how to work better in Access, I'll merge the info
in Excel _first_, then convert to Access.

Even _after_ you're comfortable importing into Access, you may still
choose to do some of the work in Excel first. Do whatever makes sense.

Good luck!

Thank you. Great advice all of this. It's neat learning how each ticks and
what works best for which situation.

Cheers. D