importing Excel sheet difficulty

[rewdee]

I'm manually importing an Excel spreadsheet into a new Access table. I have made all the columns in the spreadsheet text. Yet when I do the File->Get External Data->Import ... the columns that have serial numbers are imported as scientific notation as 1.05036e+007.

Can someone explain why this is happening and how do I change it back to simple text, PLEASE!!! (before I commit Hari-Kari)

Thanks,
Rewdee

 

[bjdobs]

I believe if you format the col and or row that contains the data in excel (prior to importing into access) as text that it may then import properly. The other thought would be to attempt to change the field properties in Access from number to text for the ser# field

Excel accepts a ' prior to a number to tell it the cell is text so another method would be to create a new col/row with a formula =&quot;'&quot;&<relative cell ref> ... copy this for each instance of ser# then when it has populated the fields you can then replace the formula by doing a copy and paste special (value) ... this should hopefully provide you a text version.

 

[oharab]

Give up now! The way I do it is to convert each xl file to a temporary text file, then import that using import specs, then delete the temp file!

Much better that chewing my own arm off!

B ----------------------------------------
Ben O'Hara
Home: bpo@SickOfSpam.RobotParade.co.uk
Work: bo104@SickOfSpam.westyorkshire.pnn.police.uk
(in case you've not worked it out get rid of Sick Of Spam to mail me!)
Web:

http://www.RobotParade.co.uk

----------------------------------------

 

[rewdee]

Thanks Oharab, so much easier