We help IT Professionals succeed at work.

Cannot import csv/txt file into access db, as one column has to long number characters ?!

dejandejanovic
dejandejanovic used Ask the Experts™
on
Hello,
I cannot figure out why txt/cvs file, which has in one column this number 20110805070542000, can not be imported into Access DB as Number data type

I have test it to cut a few last numbers, and to have only this size 2011080507. This size of data has been imported without errors.

I have also change type with alter query, but then get a field result like this 2.01108E+16.

Dim fieldsize As String

fieldsize = " ALTER TABLE test " & _
            " ALTER COLUMN field1 Number; "
CurrentDb.Execute fieldsize

Open in new window


Thank I have also try as Double, and get this result 2.0110805070542E+16.

Any idea? Can someone explain rules about data types, as I would like to have numbers size like this one 20110805070542000

Thank you in advance for help.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Owner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010
Commented:
This value is too large for the long datatype, which is limited to: 2,147,483,647

What possible purpose could you have for a numeric value of that magnitude?  I suggest that you:

1.  make this a Text datatype

or

2.  Upgrade to a 64 bit operating system and use the 64 bit version of Office (not generally recommended).
Top Expert 2010
Commented:
Even if you use the Double data type, you will lose precision, because while Double can hold truly large values, it keeps a maximum of 15 significant digits.

Typically such large numbers correspond to account numbers or the like, in which case the usual practice is to store them in the database as text.
Furthermore, numerics will strip the trailing "000" in your field as written.

It appears you're trying to store a date/time conversion field with microseconds included? If so, you may want to store it as an actual date/time field or, as fyed recommended, move it to a text field.

If you're trying to import to a new table each time, that'll make things tougher on you. Instead, create a table structure with the necessary field types into which you'll be importing over and over, either appending or emptying it before import with a delete query and re-filling it. If you're not creating a new table, adjust the one you've got to the different field type.

To your specific request for details about field types, Microsoft has a fairly thorough page about it:

http://office.microsoft.com/en-us/access-help/introduction-to-data-types-and-field-properties-HA010233292.aspx

Hope that helps!
-Rachel

Author

Commented:
Thanks, and understand.