By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,851 Members | 1,131 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,851 IT Pros & Developers. It's quick & easy.

Type conversion problems with Access Import Wizard

P: n/a
I've been trying to use the Access Import Wizard to expedite importing data
into my mdb. The nice thing about the wizard is that I can import from
different file formats - txt, xls, even Outlook - and dump everything into a
table.

The problem is once I have the data imported into a new table, I can't do
much with it. If I try to run an Append query and insert data from the new
table into an existing table, the query fails - "Error Number 3464: Data
type mismatch in criteria expression." - because the Wizard does not have
any type info when importing form flat files.

How can I correct the datatypes? Can I run a DDL query against the new
table? Is there an easy way to identify what table has just been imported?
Or do I have to loop through the QueryDefs collection and compare to
existing tables?

Thanks in advance.
Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a

deko wrote:
I've been trying to use the Access Import Wizard to expedite importing data into my mdb. The nice thing about the wizard is that I can import from different file formats - txt, xls, even Outlook - and dump everything into a table.

The problem is once I have the data imported into a new table, I can't do much with it. If I try to run an Append query and insert data from the new table into an existing table, the query fails - "Error Number 3464: Data type mismatch in criteria expression." - because the Wizard does not have any type info when importing form flat files.

How can I correct the datatypes? Can I run a DDL query against the new table? Is there an easy way to identify what table has just been imported? Or do I have to loop through the QueryDefs collection and compare to
existing tables?

Thanks in advance.


What if you create a few import specs and then use those to import your
data? I think they're listed as IMEX in the system objects table.
then you could just create a simple form that does most of the work for
you and reuses the specs you've created. Any particular reason you
need all the data in separate tables?

Nov 13 '05 #2

P: n/a
> What if you create a few import specs and then use those to import your
data? I think they're listed as IMEX in the system objects table.
then you could just create a simple form that does most of the work for
you and reuses the specs you've created.
Well, yes, If I import a text file I can click the Advanced button in the
Import Text Wizard dialog to define and save import specs. Thanks for
pointing this out (I didn't realize I could do that). But the problem is
mainly with Excel spreadsheets. AFAIK, there is no way to define import
specs for Excel (using the Import Spreadsheet Wizard).

The Excel files in question are actually built from text files. But the
text files vary in format and need massaging into Excel before they can be
imported. So the spreadsheets are in good shape and have the proper field
names as header rows. It's just the data types that's the problem. One
possible solution might be to Link to the file (rather than Import), then
run an Insert query to dump everything from the linked table into an Access
table. This seems to convert the data types as needed.
Any particular reason you need all the data in separate tables?


Because the tables are normalized, it's a challenge to import data. The
Entity_ID (Autonumber pk) must first be created in the main table, then
stuff like Address, Phone Number and Transactions can be inserted in their
corresponding tables with the Entity_ID as a fk. But the flat file with the
data to be imported has everything in one row - Entity, Address,
Transaction, etc. - so I create a recordset from the imported file and
insert one record at a time saving the MAX(Entity_ID) back to the imported
table after each insert so additional queries can be run against it to
populate the other tables. So if I have a comma delimited text file I could
set up some import specs, but with an Excel spreadsheet I have type
conversion problems.
Nov 13 '05 #3

P: n/a
> Generally, if you use code, rather then "wizard generated" imports,
you won't even need Excel or human intervention to import the file.
This method is also generally a lot faster and produces more
consistent results, however it can take a few days to set up properly
and requires that the files are somehow or another "detectable" to the
changes in format and "style". I can't think of a time when that
hasn't been possible in the past could of decades I've been doing
that.
It would take some serious regex and text processing get any kind of
consistency in these files. With an Excel template, it's not too difficult
to get the data into Excel. It would be nice to just suck it in from raw
text, but for the frequency this stuff comes along the extra step is
tolerable for now.
Stop thinking in terms of how you can use "Wizard Generated" code to
do things. Think in terms of "How I would do this if I was typing in
things", and duplicate *that* in VBA code.


Yeah, sometimes it is easier to just code it, but I'll take whatever Access
gives me for free. From what I've seen so far, the Import Wizard might be
helpful if you have text files in a consistent format and can use Import
Specs, otherwise not good for much.
Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.