Pasting Text to a table

[Meinthecorner]

Hopefully somebody can shed some light onto this, it's driving me nuts! I'm using Access 2007

I need to paste a column of data into a table. The table field is a standard text field.

The data is a mixture of numbers and numbers with letters, i.e.

442513
442390
442473
442542
437284
260H
72H
261H
442500
164H

However when I paste this to the table any data that has a Letter at the end results in a blank space where the entry should be. If i manually type the data in its fine. Or if I only paste data that has numbers&letters it pastes fine.

Any suggestions? I've created a new database to test and it does the same thing. :banghead:

 

[KenHigg]

Hum....

My guess is something thinks it's working with numeric data. Where are you copying the data from? What happens if you past to something like notepad?

 

[Meinthecorner]

Its coming from an Excel spreadsheet. When I paste to Notepad its fine.

If I sort the Excel Data into order so that the mixed data appears at the top of the list it pastes fine i.e.

260H
164H
72H
261H
442513
442390
442473
442542
437284
442500

If the data order is mixed, the paste fails.

 

[KenHigg]

So I think what happens here is Access is looking at the top x number of rows and everything it see's is telling it you are work with numbers. So when you have a value in this top x number of rows with an alpha character, it assumes you are working with text. Are you trying to automate this? What is the end state process?

 

[Meinthecorner]

I'm not going to Automate the import. The end process links this data to other tables, runs a few queries that are pushed to another spreadsheet.

It's been working fine for months as the original data was numeric only. We've since updated our main system that uses Alpha\Numeric job numbers therefore this minor issue has cropped up.

As it isn't a setting I've selected in Access, just a quirk, I'll leave it as it as and pre-sort in Excel.

Many thanks for your responses.

 

[spikepl]

Set the entire column in Excel to format Text. That should take care of it.

 

[KenHigg]

So you link to the excel file. Is the excel file manually updated or does the entire spreadsheet get replaced every day? Setting the excel format should fix it but you may need to tweek the table link so that it sees the column as text.

 

[KenHigg]

Except you'll have to admin the spreadsheet.