Importing numbers as strings

[EckyThump]

I have a database which stores part numbers. They are brought in from a spreadsheet and the column is formated as text.
Some part numbers are like 7-675F and some are like 1345673.
When they are imported, the 1345673 appears like 1.34+E (or similar).
How can I get Access to leave them as strings?
Thanks

 

[Remou]

The easiest way, I think, is to import into an existing table.

 

[EckyThump]

Remou
Thanks for your reply.
I am importing into an existing table, the part number field in the table is set to data type Text.
The spreadsheet column is formated as Text, but I am still getting the above results. Hence the reason for the posting.
I spent a fair amount of time to see if I could resolve this first, but to no avail!

 

[Remou]

That is indeed quite odd. I found that if I formatted the cells as text and then entered a long number it imported properly. I also found that I could format cells as Zip and get them to import.

 

[lameid]

This is just one of the reasons I hate Excel, albeit the top 1...

Excel is a pain when it comes to importing 'numbers' as text. I have seen people do about a dozen things to a spreadsheet before they get it to work, often involving formating the cells and copying and pasting.

However there is one thing that always seems to work for me... put a value that contains letters on the first row for every column that should be text. I sometimes put the literal word text in the cell as a reminder to myself. The catch here is that I sometimes find people wanting to put "number" above numbers. Since the word number is text, it would force the column to text.

And remember Excel is Evil except for doing quick analysis that never has to be put back in a database. Using it to store or worse transmit data? EEK! <Cringe>

 

[Remou]

lameid
I was aware of the text import problem when I tested, so I included plain text on the first row, however, scientific notation seems to be treated as text too, hence long numbers were imported as, say, 1.34+E. I found that long numbers could be entered and imported as text as long as the format of the Excel cells was changed to text before the numbers were entered. If the numbers were entered before the text format was applied, the numbers were imported in scientific notation. Applying a zip format allowed the numbers to be imported as text, that is, to a text field, even when the numbers had been input in unformatted Excel cells.

 

[JA3395]

In the Import wizard, keep clicking "Next" until you reach the section that allows you to define the format of the field. Scroll across until you reach your "Number" fields. By default Excel sets all fields/columns to "General", change the format to "Text" and Excel will leave the contents alone.

Hope this helps.

J.

 

[Remou]

JA3395
My version of Access (2K) does not allow you to change the column type when importing from Excel, only when importing from text, csv etc.

 

[JA3395]

In that case export your Excel to .txt and import that one.

J.

 

[EckyThump]

Thanks for the tip lameid.
Its always good to get other peoples views on some of the issues in the software.
I have heard of a bloke that wrote a vba routine that checks spreadsheets and validates them before import to access to alieviate any problems.
Still trying to track that rumour down though...

It would be nice though!