UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Copy And Paste Loses First Column, Access 2016    
 
   
TimTDP
post Feb 19 2019, 05:15 AM
Post#1



Posts: 1,082
Joined: 16-June 05



Attached I have a very simple Excel spreadsheet consisting of 3 columns
All I want to do is highlight the data and copy / paste into the table in the attached database.
I highlight the data
Copy it to the clipboard
Open the table
Highlight a new record
Paste data

But the data from the first column is ignored! The data in the second column is entered into the 1st field, third column into 2nd field.......

Why is this?

Thanks in advance
Attached File(s)
Attached File  Book1.zip ( 23.48K )Number of downloads: 2
 
Go to the top of the page
 
dashiellx2000
post Feb 19 2019, 08:50 AM
Post#2


UtterAccess VIP
Posts: 9,829
Joined: 11-March 05
From: Maryland


Rather than copy and paste, Import the spreadsheet via the External Data-->New Data Source-->From File-->Excel:

Attached File  ImportExcel.png ( 9.78K )Number of downloads: 0




--------------------
William
“We're run by the Pentagon, we're run by Madison Avenue, we're run by television, and as long as we accept those things and don't revolt we'll have to go along with the stream to the eventual avalanche"
Go to the top of the page
 
GroverParkGeorge
post Feb 19 2019, 08:57 AM
Post#3


UA Admin
Posts: 35,172
Joined: 20-June 02
From: Newcastle, WA


Agreed, copy-paste is not the most effective way to move data from Excel into a table.

That said, I once had a client who absolutely insisted on doing it (for valid reasons which I no longer recall exactly). We discovered that there is a difference in behavior in the "paste" step, depending on how you select the target range in Access into which you do the paste. Again, I'd have to go back 4 or 6 years to resurrect the exact steps, but it is fairly easy to experiment yourself.

Try selecting all of the destination columns at once, prior to the paste. See if that works as required. If not, try other methods of selecting & pasting the new records. i.e. selecting the entire row.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
GroverParkGeorge
post Feb 19 2019, 08:58 AM
Post#4


UA Admin
Posts: 35,172
Joined: 20-June 02
From: Newcastle, WA


I assume there is one column in the Excel worksheet for each field in the table? And that there is no primary key to be dealt with in the destination table?

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
TimTDP
post Feb 20 2019, 01:20 AM
Post#5



Posts: 1,082
Joined: 16-June 05



Thanks for advice
Pity can't simply copy & paste. Would have been so much simpler!
Go to the top of the page
 
GroverParkGeorge
post Feb 20 2019, 12:30 PM
Post#6


UA Admin
Posts: 35,172
Joined: 20-June 02
From: Newcastle, WA


Did you try all of the alternative methods for selecting columns in the datasheet view, selecting one or all, etc.?

Did you identify whether there is a Primary Key field in the destination table that doesn't exist in the source table?


--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
TimTDP
post Feb 21 2019, 02:14 AM
Post#7



Posts: 1,082
Joined: 16-June 05



QUOTE
Did you identify whether there is a Primary Key field in the destination table that doesn't exist in the source table?


I removed the Primary Key field in the destination table and the copy & paste works works
I then reinserted a Primary Key field in the destination table and moved it to the last last field in the table. Copy & paste works works

Thanks very much

Go to the top of the page
 
GroverParkGeorge
post Feb 21 2019, 09:07 AM
Post#8


UA Admin
Posts: 35,172
Joined: 20-June 02
From: Newcastle, WA


Glad to hear you found a solution.

Continued success with the project.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    24th June 2019 - 05:09 PM