Pasting Excel into DataSheet (1 Viewer)

CNorway

Registered User.
Local time
Today, 13:05
Joined
Feb 13, 2018
Messages
33
Background:

For many years, I have used datasheets as an easy way to paste right from Excel into Access (SQL Server). Though, I have to always remove "." and "/" from the header row each time to make this work.


Question 1.:
Is there a way to get around having to clean "." and "/" from the header row and get it to paste?



Question 2:

Is there a better way of handling excel imports than this?


Thanks!
Best Regards,
Chris
 

theDBguy

I’m here to help
Staff member
Local time
Today, 13:05
Joined
Oct 29, 2018
Messages
21,358
Hi Chris. I’m not sure I follow. If you’re viewing a datasheet, you must have a table already. If so, I don’t understand why you’re copying the header row from Excel. To answer question 1, I am guessing you had to delete those characters from the header row because you were trying to use them as field names in Access. If so, the only thing I could think of is to run some code in Excel to do the cleanup for you before importing the data. To answer question 2, there are many ways to import data from Excel, but I think which is the best will depend on individual taste.
 

CNorway

Registered User.
Local time
Today, 13:05
Joined
Feb 13, 2018
Messages
33
Hi theDBguy,


Thanks for the quick response.


Let me try to clarify...



1. I get sales data from Amazon in the form of an Excel file (which includes "." and "/" in the header row).


2. I clean this data by removing the "." and "/" from the header row.


3. I delete all data in my Access datasheet.


4. I copy header row and all rows from the Amazon Excel export.


5. I append this data to the Access datasheet.


I want to skip step #2 if possible. Or maybe there is a better way of importing this data into access that doesn't involve manually cleaning the header row in the Excel spreadsheet each time?


Thanks again for the help!


Best Regards,
Chris
 

theDBguy

I’m here to help
Staff member
Local time
Today, 13:05
Joined
Oct 29, 2018
Messages
21,358
Hi theDBguy,


Thanks for the quick response.


Let me try to clarify...



1. I get sales data from Amazon in the form of an Excel file (which includes "." and "/" in the header row).


2. I clean this data by removing the "." and "/" from the header row.


3. I delete all data in my Access datasheet.


4. I copy header row and all rows from the Amazon Excel export.


5. I append this data to the Access datasheet.


I want to skip step #2 if possible. Or maybe there is a better way of importing this data into access that doesn't involve manually cleaning the header row in the Excel spreadsheet each time?


Thanks again for the help!


Best Regards,
Chris
Hi Chris. If I understood it correctly (it would have been clearer if I could see some images), then to skip step 2, you could simply not copy and paste the header row from Excel. As I said earlier, if you're pasting data to an existing Access table, then I don't understand why you're also pasting the column headers from Excel. Don't you only need to transfer the data and not the headers anymore?
 

CNorway

Registered User.
Local time
Today, 13:05
Joined
Feb 13, 2018
Messages
33
Hi theDBguy,


Yes, I can attach a screen shot to show exactly what I am doing.



Here is the screenshot:
https://www.screencast.com/t/v3a5Hvvz


If you want to paste directly from Excel into an Access datasheet, you have to include the header row in order to let Access know which columns go where. The order of the columns in Excel doesn't matter. Though, the headers are required to ensure everything ends up in the correct location.



I have used this method for many years since it also informs you if there is a failure to import due to type conversion or non-matching columns.



Though, I am sure there is probably a better way that doesn't require me to modify the header row each time. Perhaps the solution is completely different than my solution.



Thanks again,
Chris
 

theDBguy

I’m here to help
Staff member
Local time
Today, 13:05
Joined
Oct 29, 2018
Messages
21,358
Hi theDBguy,


Yes, I can attach a screen shot to show exactly what I am doing.



Here is the screenshot:
https://www.screencast.com/t/v3a5Hvvz


If you want to paste directly from Excel into an Access datasheet, you have to include the header row in order to let Access know which columns go where. The order of the columns in Excel doesn't matter. Though, the headers are required to ensure everything ends up in the correct location.



I have used this method for many years since it also informs you if there is a failure to import due to type conversion or non-matching columns.



Though, I am sure there is probably a better way that doesn't require me to modify the header row each time. Perhaps the solution is completely different than my solution.



Thanks again,
Chris
Hi Chris. Thanks for sharing an image. It helped a lot to explain what you're trying to do. I must admit, I haven't used this method, so I gave it a quick try and was able to understand what's going on. So, as I said earlier, since Access doesn't accept a dot "." for a field name, you can't match a header with a dot to a valid field. Interestingly though, Access allows a slash "/" in a field's name, so in my test, I didn't have to remove "/" from the header row, and it paste appended fine. So, you really only need to worry about the dots "." in your headers.

Now, here's the other part of my discovery. When I copied the Excel data without the headers, I was still able to paste append them into the Access table, with some caveats. I had to make sure I copied the same number of columns to match the number of table fields from the left. What I mean by that is if I copy only the columns fname and lname from Excel, I only get the lname appended because my table has an ID field to the left. Essentially, the fname column got pasted to the ID column but got rejected because ID is an Autonumber field. So, all I had to do was copy an extra column to the left, and it worked fine. What this could mean for you is make sure the Excel columns match the order of the fields in your table (not the names), and you'll be able to copy the data without including the header row and paste them into Access. I don't know where you get your data, but if they are consistent with the column order, then this would be one way to avoid the issue with the headers containing invalid characters. Otherwise, you will not be able to avoid cleaning up the header row either manually or using code.
 

CNorway

Registered User.
Local time
Today, 13:05
Joined
Feb 13, 2018
Messages
33
Hi theDBguy,


Yes, this worked perfectly!


In addition, I simply moved the autonumbering field to the last column and the paste went fine without any modifications of the Excel spreadsheet.


The column order is consistent, so this competely solves this issue for me.


Thanks for the help!
Best Regards,
Chris
 

theDBguy

I’m here to help
Staff member
Local time
Today, 13:05
Joined
Oct 29, 2018
Messages
21,358
Hi theDBguy,


Yes, this worked perfectly!


In addition, I simply moved the autonumbering field to the last column and the paste went fine without any modifications of the Excel spreadsheet.


The column order is consistent, so this competely solves this issue for me.


Thanks for the help!
Best Regards,
Chris

Hi Chris. You’re welcome. Good luck with your project.
 

Users who are viewing this thread

Top Bottom