469,643 Members | 1,486 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,643 developers. It's quick & easy.

Missed the Boat on Import Logic

I guess I missed the boat on the logic for this one. Immediately upon
hitting "Import" in an
attempt to import an Excel file containing 7 columns of 'txt' formatted data
into AXP, I got a "Type mismatch" error. A search of previous posts
indicated that Access did not 'like' most data types being imported except
for 'txt' files, so I saved the 'xls' as 'txt' and that didn't work either,
receiving the same "mismatch" error message. (Crudely, I can always 'cut &
paste' my data into a table I guess).

My question is if there is a "Type mismatch" on a truly text
data type, converted 'xls' file, what is Access 'matching' the data type
against since
no table (and therefore no data & structure) was ever presented as the
import destination? How can something be 'mismatched' if there is nothing
(presented) to 'match' it against?

Thx...
Earl

Jul 28 '07 #1
4 1679
i don't have an answer for you, Earl, but i'm interested in the outcome,
too. if you don't get a useful response (or get a thread going) by the end
of the day, suggest you repost your question to
microsoft.public.access.externaldata newsgroup. folks with more expertise
lurk there, including MVP John Nurick; so you may get the help you need.

hth
"Earl Anderson" <is*****@optonline.netwrote in message
news:ez*************@newsfe12.lga...
I guess I missed the boat on the logic for this one. Immediately upon
hitting "Import" in an
attempt to import an Excel file containing 7 columns of 'txt' formatted
data
into AXP, I got a "Type mismatch" error. A search of previous posts
indicated that Access did not 'like' most data types being imported except
for 'txt' files, so I saved the 'xls' as 'txt' and that didn't work
either,
receiving the same "mismatch" error message. (Crudely, I can always 'cut
&
paste' my data into a table I guess).

My question is if there is a "Type mismatch" on a truly text
data type, converted 'xls' file, what is Access 'matching' the data type
against since
no table (and therefore no data & structure) was ever presented as the
import destination? How can something be 'mismatched' if there is nothing
(presented) to 'match' it against?

Thx...
Earl

Jul 28 '07 #2
Hey,

I'm no expert, but I think that if the data is getting "misunderstood"
by Access, you probably have a literal string (probably composed of
alpha-numeric characters) and the string has a leading number (a
number is the first character). I've had a similar issue and I
noticed that if Access is expecting a number, there will be issues
with anything that is not numeric data.

I noticed this when I'd be loading a spreadsheet into Access to
compare. The spreadsheet had a list of clients, but some were new
participants (via the Net) and some were existing clients. The new
clients would have a temporary client ID that would be converted.
However, their temporary ID would begin with a "t" to indicate the
type of client and what we can expect. Here's an example (albeit a
short one) of a list:

102340
134567
t3375

Above are the client IDs. Now since Access is expecting a number, it
will have some funky stuff happen when it gets to the "t3375" and I
believe (I've forgotten, to be honest) that it will chop off the data
and then put it into an "import errors" table within the database.
One way to get around this is to have two separate tables for your
data (one table is numbers only and one table is alpha-numeric) as
Access will interpret one table as having all numbers in that column
and the other table will have a string of alpha-numeric characters.

Well, I know I haven't given too much help, but I think this will
point you in the right direction and you can further research this.
Let me know what turns up.

Cheers!

The Beantown Dude

On Jul 28, 11:59 am, "tina" <nos...@address.comwrote:
i don't have an answer for you, Earl, but i'm interested in the outcome,
too. if you don't get a useful response (or get a thread going) by the end
of the day, suggest you repost your question to
microsoft.public.access.externaldata newsgroup. folks with more expertise
lurk there, including MVP John Nurick; so you may get the help you need.

hth

"Earl Anderson" <isob...@optonline.netwrote in message

news:ez*************@newsfe12.lga...
I guess I missed the boat on the logic for this one. Immediately upon
hitting "Import" in an
attempt to import an Excel file containing 7 columns of 'txt' formatted
data
into AXP, I got a "Type mismatch" error. A search of previous posts
indicated that Access did not 'like' most data types being imported except
for 'txt' files, so I saved the 'xls' as 'txt' and that didn't work
either,
receiving the same "mismatch" error message. (Crudely, I can always 'cut
&
paste' my data into a table I guess).
My question is if there is a "Type mismatch" on a truly text
data type, converted 'xls' file, what is Access 'matching' the data type
against since
no table (and therefore no data & structure) was ever presented as the
import destination? How can something be 'mismatched' if there is nothing
(presented) to 'match' it against?
Thx...
Earl- Hide quoted text -

- Show quoted text -

Jul 30 '07 #3
One work around would be, insert a row of text in the 1st row of date from
Excel. This will cause Access to import all columns as text.

"boston_dude" <ia********@gmail.comwrote in message
news:11********************@w3g2000hsg.googlegroup s.com...
Hey,

I'm no expert, but I think that if the data is getting "misunderstood"
by Access, you probably have a literal string (probably composed of
alpha-numeric characters) and the string has a leading number (a
number is the first character). I've had a similar issue and I
noticed that if Access is expecting a number, there will be issues
with anything that is not numeric data.

I noticed this when I'd be loading a spreadsheet into Access to
compare. The spreadsheet had a list of clients, but some were new
participants (via the Net) and some were existing clients. The new
clients would have a temporary client ID that would be converted.
However, their temporary ID would begin with a "t" to indicate the
type of client and what we can expect. Here's an example (albeit a
short one) of a list:

102340
134567
t3375

Above are the client IDs. Now since Access is expecting a number, it
will have some funky stuff happen when it gets to the "t3375" and I
believe (I've forgotten, to be honest) that it will chop off the data
and then put it into an "import errors" table within the database.
One way to get around this is to have two separate tables for your
data (one table is numbers only and one table is alpha-numeric) as
Access will interpret one table as having all numbers in that column
and the other table will have a string of alpha-numeric characters.

Well, I know I haven't given too much help, but I think this will
point you in the right direction and you can further research this.
Let me know what turns up.

Cheers!

The Beantown Dude

On Jul 28, 11:59 am, "tina" <nos...@address.comwrote:
i don't have an answer for you, Earl, but i'm interested in the outcome,
too. if you don't get a useful response (or get a thread going) by the
end
of the day, suggest you repost your question to
microsoft.public.access.externaldata newsgroup. folks with more
expertise
lurk there, including MVP John Nurick; so you may get the help you need.

hth

"Earl Anderson" <isob...@optonline.netwrote in message

news:ez*************@newsfe12.lga...
I guess I missed the boat on the logic for this one. Immediately upon
hitting "Import" in an
attempt to import an Excel file containing 7 columns of 'txt'
formatted
data
into AXP, I got a "Type mismatch" error. A search of previous posts
indicated that Access did not 'like' most data types being imported
except
for 'txt' files, so I saved the 'xls' as 'txt' and that didn't work
either,
receiving the same "mismatch" error message. (Crudely, I can always
'cut
&
paste' my data into a table I guess).
My question is if there is a "Type mismatch" on a truly text
data type, converted 'xls' file, what is Access 'matching' the data
type
against since
no table (and therefore no data & structure) was ever presented as the
import destination? How can something be 'mismatched' if there is
nothing
(presented) to 'match' it against?
Thx...
Earl- Hide quoted text -
- Show quoted text -


Jul 30 '07 #4
What?? And miss out on the excessive work that I've
recommended? ;o)

Seriously, that sounds like a much easier solution.

Cheers!

The Boston Dude
On Jul 30, 4:15 pm, "paii, Ron" <n...@no.comwrote:
One work around would be, insert a row of text in the 1st row of date from
Excel. This will cause Access to import all columns as text.

"boston_dude" <ian.gan...@gmail.comwrote in message

news:11********************@w3g2000hsg.googlegroup s.com...
Hey,
I'm no expert, but I think that if the data is getting "misunderstood"
by Access, you probably have a literal string (probably composed of
alpha-numeric characters) and the string has a leading number (a
number is the first character). I've had a similar issue and I
noticed that if Access is expecting a number, there will be issues
with anything that is not numeric data.
I noticed this when I'd be loading a spreadsheet into Access to
compare. The spreadsheet had a list of clients, but some were new
participants (via the Net) and some were existing clients. The new
clients would have a temporary client ID that would be converted.
However, their temporary ID would begin with a "t" to indicate the
type of client and what we can expect. Here's an example (albeit a
short one) of a list:
102340
134567
t3375
Above are the client IDs. Now since Access is expecting a number, it
will have some funky stuff happen when it gets to the "t3375" and I
believe (I've forgotten, to be honest) that it will chop off the data
and then put it into an "import errors" table within the database.
One way to get around this is to have two separate tables for your
data (one table is numbers only and one table is alpha-numeric) as
Access will interpret one table as having all numbers in that column
and the other table will have a string of alpha-numeric characters.
Well, I know I haven't given too much help, but I think this will
point you in the right direction and you can further research this.
Let me know what turns up.
Cheers!
The Beantown Dude
On Jul 28, 11:59 am, "tina" <nos...@address.comwrote:
i don't have an answer for you, Earl, but i'm interested in the outcome,
too. if you don't get a useful response (or get a thread going) by the
end
of the day, suggest you repost your question to
microsoft.public.access.externaldata newsgroup. folks with more
expertise
lurk there, including MVP John Nurick; so you may get the help you need.
hth
"Earl Anderson" <isob...@optonline.netwrote in message
>news:ez*************@newsfe12.lga...
I guess I missed the boat on the logic for this one. Immediately upon
hitting "Import" in an
attempt to import an Excel file containing 7 columns of 'txt'
formatted
data
into AXP, I got a "Type mismatch" error. A search of previous posts
indicated that Access did not 'like' most data types being imported
except
for 'txt' files, so I saved the 'xls' as 'txt' and that didn't work
either,
receiving the same "mismatch" error message. (Crudely, I can always
'cut
&
paste' my data into a table I guess).
My question is if there is a "Type mismatch" on a truly text
data type, converted 'xls' file, what is Access 'matching' the data
type
against since
no table (and therefore no data & structure) was ever presented as the
import destination? How can something be 'mismatched' if there is
nothing
(presented) to 'match' it against?
Thx...
Earl- Hide quoted text -
- Show quoted text -- Hide quoted text -

- Show quoted text -

Jul 30 '07 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Stian Søiland | last post: by
97 posts views Thread by Kjetil Torgrim Homme | last post: by
2 posts views Thread by David Berry | last post: by
10 posts views Thread by shumaker | last post: by
reply views Thread by Anders J. Munch | last post: by
4 posts views Thread by Brian Blais | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.