Data Field Size and Format for Input

[Tom]

I have a Text (Tab) Delimited file (saved from Excel) that gives me problems
with data types = "number" when importing into an Access table.

The text file contains values representing "currency" as well as "decimals".
For instance, their values are "$1,250.00" and "0.1".

When I import the text file, all text gets imported properly. However, the
numbers don't make it (records contain the default value of "0").

In the table, I have changed the field size to "long integer", "double",
"single",
"decimal", etc.. I also modified the format to "General", "Standard",
"Currency"
& adjusted the decimal places = 1 or 2.

What am I doing wrong? I can't imagine the text file couldn't contains
e.g. "0.1".

Any suggestions?

 

[NAR]

Where numbers can also be entered in as text set the Allow
Zero length to YES in the field properties of the table
design.
With currency don't use commas as value separators eg
$1,250.00, use $1250.00. If possible don't use $ either.

Hope this helps

 

[Tom]

Thanks for your reply...

Both fields are data type = number in the tables.

I deleted the commas and $s for the 1st one and they now import w/o
problems... great!

However, there is no option "Allow Zero Length" for a number field in the
table design properties. I only see this field for e.g. a data type =
text... which again, this "problem field" is already a number field.

Am I misunderstanding something??? If not, what's the best "combination"
for the "field size" and "format"? Currently, I'm dealing with either
single-decimals (0.1 to 3.0).


Again, thanks for your help on this. Any additional advice is truly
appreciated.

 

[Tim Ferguson]

The text file contains values representing "currency" as well as
"decimals". For instance, their values are "$1,250.00" and "0.1".

I strongly suspect that the first example would be parsed as text since the
dollar character is not a valid digit. The second example should be a valid
number (assuming that the quote marks are for our benefit and do not appear
in the actual import file).

When I import the text file, all text gets imported properly.
However, the numbers don't make it (records contain the default value
of "0").

You don't have to have a default value of zero -- this is a particularly
hostile quirk of the Access GUI to put zero as the (default) default value
for numeric fields. You can put it back to null (and probably should in
most situations). Unfortunately no-one here knows anyway of resetting the
default.

In the table, I have changed the field size to "long integer",
"double", "single", "decimal", etc..

Well, any integer type will import 0.1 as zero, so perhaps that was a
correct answer. For myself, I very rarely use any numeric type other than
Long Int and Double.

I also modified the format to "General",
"Standard", "Currency"
& adjusted the decimal places = 1 or 2.

These only affect the display in table datasheets, and will not affect the
data themselves.

In the end, if you are really stuck, the most robust answer may be to
import into a temp table and then use that as the basis for an append query
to parse and covert the values into the real table. Something like

INSERT INTO ProperTable (TotalPrice, MeasuredWidth)
SELECT CINT(MID(TextDollarValue,2)) AS NumericDollarValue,
CDBL(SomethingElse) AS FloatingPointValue
FROM TempTable

and so on. It's a bit more work, but at least you get the right answers in
the right places.

PS You are right: Allow Zero Length is only applicable to Text type
columns...

Hope that helps


Tim F