If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
import from excel help
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 |
#2
|
|||
|
|||
import from excel help
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. -- "Why live in the real world adhering to thier rules, when you can live in code and write your own" "Tom" wrote: 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 |
#3
|
|||
|
|||
import from excel help
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: -- "Why live in the real world adhering to thier rules, when you can live in code and write your own" "Magius96" wrote: 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. -- "Why live in the real world adhering to thier rules, when you can live in code and write your own" "Tom" wrote: 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 |
#4
|
|||
|
|||
import from excel help
On Fri, 19 Dec 2008 12:36:02 -0800, Magius96
wrote: 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. -- John W. Vinson [MVP] |
#5
|
|||
|
|||
import from excel help
See
Avoid DataType Mismatch Errors when Importing Data from an EXCEL File or when Linking to an EXCEL File http://www.accessmvp.com/KDSnell/EXC...tm#DataTypeErr -- Ken Snell MS ACCESS MVP http://www.accessmvp.com/KDSnell/ "Tom" wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|