Import from SPreadsheet has strange behavior

  • Thread starter Thread starter Corey-g via AccessMonster.com
  • Start date Start date
C

Corey-g via AccessMonster.com

Hi All,

I have written a VBA proc to load data from a xls, but I seem to have a
problem. Some (4) of the fields are text fields that contain either TRUE or
FALSE. I have the table design set to TEXT for these columns, but the values
inserted are -1 and 0.

Is there any way to get around Microsoft converting my text values into their
numeric equivalent?

Thanks in advance,

Corey
 
Corey,

When Access imports Excel data, it tries to guess the appropriate data
type based on the data in the N top rows (where N is a number defined in
a registry key). Finding only True and False, it guesses Boolean and
imports as such. Now, Access stores false as 0, true as -1, thus what
you see. Unless there is some compelling reason for which you need those
fields to be text, it would be best to define them as Yes/No in the
first place, so they take up far less storage space; in that case they
would show as a ticked/unticked checkbox (but you can use formatting to
show them as True/False if you prefer that. In my experience, the
checkbox representation makes datasheets more readable.
If you still insist on importing text, you could use a trick, for
example add an extra character at the beginning in each Excel column,
e.g. ATRUE, AFALSE (through a spreadsheet function), so as to fool
Access to import text, then once imported, use an update query on the
table to truncate the excess character; doable, but not recommended.

HTH,
Nikos
 
Thanks NIkos.

The reason for using text was because the backend db is Oracle, and there is
no boolean datatype in Oracle. I can just change the structure to numeric,
and remember to use the T = -1, F = 0.

I guess the other option would be to use a trigger to replace the number with
the appropriate text again. Too bad seeing how the data is already in the
format I was going to use...

Thanks for your help,

Corey
 
Hi again,

I have to say, I'm pretty stumped!!! I don't know why, but I can get one (of
4) columns to insert "TRUE" or "FALSE", while the others still insert -1 & 0..
..

I had thought that maybe if I added the apostrophe ( ' ) to the front of the
data, it might cause the import to see it as text (Used a quick concatenate
statement). Well, that didn't work because the values inserted were 'TRUE
and 'FALS (the column is set to 5 characters). So I went back into the S/S
and did a little formula to remove the apostrophe (in adjacent cell) - did:

"=if(D2= " ' TRUE ", right(D2,4),right(D2,5)) --> spaces added between
quotes for clarity

Then copied and paste-special-values back into original column. When I
imported the file again, this time the column (D) inserted the "TRUE" &
"FALSE" correctly... So I went back and did this too all of the True/False
columns, and now they import correctly (at least the way I thought was
correctly)...

Any idea why this would happen?

Corey
 
Corey,

Just guessing here, but I think what happened may be this: in the
original spreadsheet, the cells are formatted General, so True / False
are assumed boolean values; on the other hand, function Right() - or any
other text function, for that matter - returns a text string by
definition, so that may be carried across to Access.

On the Oracle datatype issue, I'm not very familiar with the subject,
but it seems strange that Oracle shouldn't offer a boolean type... even
so, you could use a (small) integer type and store 0's and -1's, so you
maintain the compatibility between Access (FE) and Oracle (BE). Wherever
required (forms, reports etc) you can just convert the numeric value to
True/False or Yes/No or whatever.

HTH,
Nikos
 
Back
Top