Pasting from Excel to Access --Data Loss

N

nieszhw

When pasting a column of data from Excel to Access, one of my users is
experiencing data loss. For instance, they may have 100 rows to paste, but
when the paste occurs, it says "You are about to paste 2 rows". The data in
Excel is formatted as General. They are all text entries (LastName fields).
It is not exceeding the maximum field width in Access.

The user says they've tried this on multiple workstations with the same
result. However on my personal Laptop it does not happen, --all the data
pastes just fine.

Does anyone know why this may be occuring?
 
S

SacCourt

Try Paste Append. Paste Append will add new records to the table when paste
will only copy over the existing recreods. This is also data destructive, if
this is what was happening. Not only did you not get the other 48 recrods,
but you copied over the first two records you did have. You have lost data in
two ways by a simple paste.

If you are trying to paste the entire xls data to a table. I recommend
deleting all redords and then using pase append. Then you can have a line
count to confirim that you have them all.

Sometimes, you have other reasons that a recroed is rejected or that data is
lost. It is unclear whether or not this is happening. For example, you could
have 255 characters of data and a table field length of 50. So you can loose
data by truncation. It may not ve a valid date as you copy into a date
field. You can have carriage returns imbedded in a xls sheet and get only the
first line.

You might try to import the xls instad.
 
N

nieszhw

Initially the destination table is empty. The data is being pasted column by
column, so only the first paste creates new records. Any successive paste
only fills fields in the records created by the first paste. There are no
data validation issues (truncation, date fields), it is strictly a text to
text paste.

I will have the user try the paste append function and see if this makes a
difference.

Thanks in advance,
hwn
 
S

SacCourt

Thanks, colum by colom pastes are not reliable. If the order changes you
scramble your data. I prefer to put the data into a static table and use an
update query to update the data based on a matching key, for example,
personnel number.
 
J

John Vinson

When pasting a column of data from Excel to Access, one of my users is
experiencing data loss. For instance, they may have 100 rows to paste, but
when the paste occurs, it says "You are about to paste 2 rows". The data in
Excel is formatted as General. They are all text entries (LastName fields).
It is not exceeding the maximum field width in Access.

The user says they've tried this on multiple workstations with the same
result. However on my personal Laptop it does not happen, --all the data
pastes just fine.

Does anyone know why this may be occuring?
Click to expand...

As SacCourt says, copy and paste is really less than ideal. I'd
suggest using File... Get External Data... Link to connect Access to
the spreadsheets, and then run Append queries to add new records, and
Update queries (joining on an appropriate unique field in the table
being updated) to update existing ones.

John W. Vinson[MVP]
 
N

nieszhw

I would agree with you and SacCourt most of the time. However this user has
approx. 100+ spreadsheets, all non-standardized, that gets sent to her from
various sources. The database will be used to standardize the data.
Reformatting all of the data in the Excel spreadsheets is out of the
question--(too many variables to account for. Differences in fields as well
as formats). Even if I built import specs for every spreadsheet, any new
spreadsheet would still be an issue. I tried building an Excel "Template
spreadsheet" for her to paste the data into, but Excel keeps the formatting
of a field when you copy and paste, so it overwrites the template fields.
The next best solution is to paste directly into Access which does not
inherit field formatting. That is when the issue of not pasting all the data
reared its ugly head.

I hope this clarifies why I'm using such an archaic way of importing the
data. However, I will consider any other possible solutions for importing
the data with less effort.

Thanks,
hwn
 
J

John Vinson

I would agree with you and SacCourt most of the time. However this user has
approx. 100+ spreadsheets, all non-standardized, that gets sent to her from
various sources. The database will be used to standardize the data.
Click to expand...

:-{(

My sympathies! Sounds like you're making the best possible of a very
bad situation.

John W. Vinson[MVP]
 
Top