Weird Paste (append) behaviour

[HGMonaro]

Hi,

I'm having issues with a paste append I'm trying to perform from Excel 2007 to Access 2013.

I have various columns of data, a mixture of text and numbers, which all have data in them, and a few columns that have numbers or text. When I copy'n'paste-append into the Access table, the text in the sparsely populated columns doesn't copy across but the numbers do.

So after some experimentation I've found... It works if I import the spreadsheet from scratch (don't want to do that however, that data comes from somewhere else and would require modification to suit), or if I sort the data so that the text is at the top of the column, it works. Don't really want to do that either as there are 5 columns that this applies to and performing a 5 level sort shouldn't be necessary IMO. It seems that the paste is analysing the data and deeming that it should paste as a number even though the column contains text. There are only 120 rows of data, not a lot! I've tried redefining the field as Long Text (currently Short Text) but it doesn't make any difference.

I can work around it, but if there's something that's causing this and can be rectified, I'd prefer that. Any thoughts?

 

[spikepl]

The first thing to try is to make all cells in a given colum the same data type. Select a column and format it to the relevant type.

access analysies the first rows - can't recall how mny - and decides on what type the enire column is based on that. Sometimes putting some text in some column row 1 makes access decide the entire column is text .

 

[HGMonaro]

thankyou spikepl! The Excel cells without text data were formatted as Number. Formatting them as General has worked Now to get the originator to fix their spreadsheet!

Cheers, HG