import from excel help

[Tom]

I am importing part numbers from an excel spreadsheet. For some reason the
part numbers which are generally 12 digit numbers, sometimes import in
scientific notation format as exponents. It seems very random. It obviously
happens in the excel spreadsheet also prior to import. If I click on the
field and look at the number in the formula bar however it appears normal.

Here is the question:
If I import a set of part numbers and 90% of them are 12 digits can I
control the formatting during the import process some way so that all numbers
end up as 12 digits? Also, if a number should only have 11 digits for what
ever reason, is there a way to make access add a zero to the first digit to
make it a 12 digit string?

Any help will be very appreciated

 

[Magius96]

For the import part, since this is a part number, and you probably won't be
performing any mathematical functions upon it, import it as TEXT. That by
itself should prevent it from being converted into scientific notation. You
may also want to convert them into text format on the excel spreadsheet, but
that's not necessary.

As for adding the 0, yes, that's possible. In the table design view, enter
*0 as the format, and set the field size to 12. This tells access that the
field should be 12 characters long, and blank spaces should be filled with 0s.

 

[Magius96]

Aw, shucks! I just tried it, and it appears the format rule doesn't work.
Importing it as text does work however.

I guess one option would be to have a process that reads through your
database and uses the following function

Function AddZero(byval PartNum as String) as String
Dim NewString as String
NewString = PartNum
Do While Len(PartNum) < 12
NewString = "0" & NewString
Loop
AddZero = NewString
End Function

A final alternative is to run an update query after import with this as the
filter:

 

[John W. Vinson]

I guess one option would be to have a process that reads through your
database and uses the following function

Function AddZero(byval PartNum as String) as String
Dim NewString as String
NewString = PartNum
Do While Len(PartNum) < 12
NewString = "0" & NewString
Loop
AddZero = NewString
End Function

There's actually a simpler expression that requires no code: just run an
Update query updating PartNum to

Right("000000000000" & [PartNum], 12)

You can use a criterion of

Len([PartNum]) < 12

to avoid wasting time updating records where Partnum is already filled.

 

[Ken Snell \(MVP\)]

See
Avoid DataType Mismatch Errors when Importing Data from an EXCEL File or
when Linking to an EXCEL File
http://www.accessmvp.com/KDSnell/EXCEL_ImpExp.htm#DataTypeErr