473,706 Members | 2,441 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Type conversion problems with Access Import Wizard

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
3 3712

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
> 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
> 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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

11
3656
by: RWC | last post by:
Hello, I'm having trouble converting code in Access XP / 2002. I have some code that declares an variable "as database" in Access 97, which is not recognized in Access XP. I've tried to find a refrence to the items that have changed between 97 and XP, and haven't been able to find anything yet. I will continue looking, but if anyone can shed some light on this fairly quickly, I would appreciate it. Thanks In Advance!
2
2223
by: Maurice | last post by:
Folks once again I look forward to your invaluable assistance. When converting an Access 2002 mdb back to Access 1997 using the Access 2002 inbuilt tools I receive the following error message "Some errors happened during the conversion. No converted database was generated". This message is not of great assistance to me in sorting out the error!! I have not been able to find any helpful support on where to start looking
4
6897
by: Matt Sawyer | last post by:
I am attempting to use an API (CxApiOem.dll) that has a large number of defines and complicated structs. It's just too much hassle to attempt to use DLLImport to make the desired API calls. Instead, I created a managed C++ DLL (OneBoxAPI.dll) that wraps the desired API calls in a manner which is easy to call from C#. This way I can use the header files that are part of the API for all the defines and structs. I am having a problem...
1
2284
by: bala | last post by:
Hi Access Gurus The MS Access DB (MS Access 2002 Database) which is currently serving as the backend Database to MS Access (MS Access 2002/XP) frontend needs to be converted to Oracle 8i Backend (With the frontend still being MS Access 2002). The Client is very particular about the conversion. Would appreciate if any suggestion can be given as to how to start and go ahead with conversion. Whether the Upsize Wizard used by MS Access...
2
2353
by: jason.teen | last post by:
Hi, I am having trouble importing a spreadsheet from Excel into an Access Database. I have noticed that even specifying the Cell Type of the Excel Data Cell, When I import it, still get Import Errors. Like Type Mismatch. The funny thing is that its all done automatically via the Wizard, so I dont even specify what the field type should be in my database, but it
16
5038
by: thefritz_j | last post by:
We just converted our VS2003 1.1 VB web project (which was working fine) to VS2005 2.0 and now I get: Parser Error Message: Could not load type '<Namespace>.'. Source Error: Line 1: <%@ Application Codebehind="Global.asax.vb" Inherits="<Namespace>." %> I've done a lot of things I've found on the web to no avial, but here are some unique things about what is happening to me.
4
8654
by: Mark | last post by:
Bad header on my first post. Sorry. When I import a file using the Import Spreadsheet Wizard and I get to the Field Options screen where it allows me to choose the fields and the Indexed option, my Data Type option is grayed out. Shouldn't I be able to change data types at that stage for each field as opposed to waiting until the data is uploaded and changing in the table properties? Can someone tell me how to turn that option on. Save...
6
2795
isben22
by: isben22 | last post by:
I am a beginning developer using ACCESS 03 and Share Point Portal 2003. I have a list of events with start and stop dates in SharePoint that I want to pass to and Access Data Base. Ultimatly I want to create an itinerary program that has an event and many subEvents as in a relational database. But I have not been able to find information to show me how to turn a SPS List into a relational table. I have now linked to the SPS List, and...
6
2331
by: teser3 | last post by:
I am trying to import an Excel 2003 sheet into my Access 2003 database. I get error message saying sheet could not be imported and it referred me to an Import error table which showed type conversion error for one of the columns. Please advise what that means and how I can make it work?
0
8773
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9276
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9145
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
8983
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7897
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6613
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
4442
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
2
2495
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2084
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.