Having mastered connecting site content by using Web Part Connections, I’d run into a new problem: how to update a Sharepoint List with more data. I initially tried selecting “Edit in a datasheet” and just pasting my new rows in. But my data wasn’t in the right format so I got an error message that Sharepoint couldn’t paste the data. I then tried selecting “Open with Access”, thinking that perhaps working with the data in a database format might be easier.
I tried pasting the data and then importing the data to append the table, but neither worked. It seemed that the column settings were very specific, so I had to somehow make my Excel spreadsheet data be exactly the same as the table in Access in order for the two sets of data to recognize each other as “the same”.
This is the point where I suspect a lot of people get frustrated. Data can look exactly the same to the eye but in fact have subtle differences in the column settings – things like if it’s text or a number. To a person, it looks like 1234 either way, but to a computer it’s either 1234 (a character that can be counted and concatenated but not summed) or 1,234 (a number that can be used in any calculation). If you don’t know a lot about data types and other column settings, it can prove to be a very frustrating and time-consuming task to figure out how to make them work together.
I decided to try a different approach altogether. I exported the List from Access into Excel.
I opened the Excel file and I pasted my data into the spreadsheet using the “paste special” option from the right click menu and selecting “values” to make certain that I didn’t change any of the column formatting that had come in from Access.
Now I had all my new data in the Excel file so I deleted the old data from the spreadsheet – I only wanted to import new data! I then renamed the Excel file and went back to Access and imported it, setting it to append my existing List.
Voila! It worked! The whole process took only a few minutes and all the data played very nicely together.