how do i format scientific numbers?

[zetino]

i have this tab delimited text file. it has a little over 1,000,000 [one million] records. when i import this into access, it converts the "uniqueid" to a scientific number (i.e. 1.0025E+11). if i convert it to number, i lose some data. how do i convert this to a non-scientific number? thank you very much for any assistance on this. i have been struggling with this for a day now.

note: the version of access that i'm using is 2003.

 

[Joe4]

Welcome to the Board!

How about importing the field as Text instead of Numeric?

 

[zetino]

thank you so very much for your advice. i did try that, and it still gave me the same result. it still imports that field in the same manner, and leaves it as a scientific numeration. i also tried memo, and it did the same thing. thank you again for your sound advice. i hope there is a solution to this, thank you!

 

[alansidman]

In the table you are importing to, do you have the field set as text? Or are you importing to a new table?

 

[zetino]

alansidman: the table that i'm importing to is a new table. this is a new database. the file that i'm also importing does not have header rows. so far, the only way that i don't lose data when i do the import is to import this as a memo field, or text field. the only problem is that it makes it a scientific number, and not the normal one that i need to then be able to match it with another table. again, thank you for any advice!<SCRIPT type=text/javascript> vbmenu_register("postmenu_1724423", true); </SCRIPT>

 

[Joe4]

Probably a stupid question, but I need to ask it anyway just to rule this out.

Are you sure that the data isn't already in scientific notation in the text file?
If you view the text file, are the numbers as they should appear?

 

[zetino]

Joe4<SCRIPT type=text/javascript> vbmenu_register("postmenu_1724734", true); </SCRIPT> : this is actually a really great question. when the tab delimeted file gets imported into excel 2007, it has the numbers the way that they need to be (standard). however, the person does not have access. what i do know is that the file originally is in a tab delimeted format. when that file gets imported into excel 2007 it recognizes the fields. the person does not have access. i have excel 2003. when i get the excel file that is in 2007, if i try opening it, it gets converted to excel 2003 and i can only see about 65,000 records, and the field is in standard format there. in excel 2007 i see the "million plus" that are on the tab file. i can't import the excel 2007 file into access 2003. it does not recognize that file. however, to answer your question, no, the numbers are not in scientific notation in the tab file...great question!

 

[xenou]

Sometimes this works:
1) import the data,
2) go to table design view and change the datatype to text
3) Delete the data but save the table.
4) Now import the data again but not to a new table, rather to the table you've just saved.

If you're not sure about how to delete the data, you can also right click Copy right click Paste the table in the database window, choosing "Structure only".

Really, have a text field on the table you are importing to should be able to handle this as you require.

Alex