Solved

Persistant Import errors

Posted on 2011-09-22
19
208 Views
Last Modified: 2012-05-12
I have several saved import/exports that are supposed to append email attachments (tab delimited text files) to database tables. The problem is that one of them consistantly gives me an ImportErrors file.  When I check the ImportErrors file I notice that the BillingPhone column does not seem to want to import into my table if the number contained in the text file for that column does not begin with a '2'.  I'm assuming that since that is the first number of the area code, and no area codes begin with a 1 that the upper limit (number range) must have been reached.  I've tried specifying a text field for the column but that doesn't work either:

I am uploading the text file...the design of the table into which I'm importing is such that all fields are text files...Oh yeah. the errors are all 'type conversion Failure' of rthe BillingPhone column, and they don't occur if the phone number begins with a '2' ...The phone numbers in the text file are a string of 10 digits with no punctuation. Orders.txt
0
Comment
Question by:cekendricks
  • 7
  • 6
  • 5
  • +1
19 Comments
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 36583826
I looked at your file and imported it without getting errors.

Try this ...

Open the table you are importing into and change or make sure the Billing Phone field and others are set to Text.  Delete the records and re-import.

It worked for me.

ET
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 36583840
Also ... Click the Advanced button on the Import Wizard to view the default Import Specs for your file.  You will notice the BillingPhone and a few others are defaulting to Long Integer.  Change those to Text and you will import successfully.

See picture below.

ET
ImportSpecs.png
0
 

Author Comment

by:cekendricks
ID: 36584075
I'm sorry if I didn't make myself clear.  I am having no problem in importing the attachemnt to initially build the table, it's when I use the wizard to Append to the table that I am having these problems.  I build the table from the attachment with the Wizard (I changed the data type for all fields to text during the initial import) with no problem.  Then I use the wizard and the same text file (no primary key, or indexed columns), and tell it to append the same file to the table that I just built, and that's when I get the Import/Export Error for the BillingPhone column, and only for phone numbers that begin with any number other than 2.
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 19

Expert Comment

by:Eric Sherman
ID: 36584108
Yes, I had the same problem.  Your table that you are importing into has the BillingPhone type set as Long Integer.  

Modify the field types in your destination table.

ET
0
 

Author Comment

by:cekendricks
ID: 36584140
Yes, changing the data type of the BillingPhone field for the destination table was one of the first things I tried but it didn't work...did it work for you?
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36584194
Make sure you don't have any validation rule, Format, Inputmask, ...etc., set in the table that only wants to see a "2"
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 36584315
<<<<<Yes, changing the data type of the BillingPhone field for the destination table was one of the first things I tried but it didn't work...did it work for you?>>>

Yes it did ...

Run the Import Wizard and click the Advanced Tab, create and save an Import Specification for your file and use that for importing.

Can you load up a sample of your db ....  (Office 2000/2003) format.

ET
0
 

Author Comment

by:cekendricks
ID: 36588088
Well I'm still not sure just why I am not able to Append to the table.  One thing to not is that I am running A2010.  Also when I choose to Apppend to an existing table in the External Data Wizard, I am not given a chance to change the data type of the imported fields, so that they match the existing field types in the existing table.

Apparently as far as I can see the data type of Long Integer is too short to provide for integers above 2,147,483,647 which would explain why phone numbers with area codes that start with any number other than 2 are causing the error.  I have tried several different data types for the existing table including Decimal (12 bytes, precision 16, 0 decimal places), and I get the same results.

I am attaching a copy of the database (2003 version).  Please not that the GetAttachments procedure pulls the email attachments that contains the text files to be appended to the database tables, so it won't be of much use to you.  It is the ImportTextFiles procedure that I am having the trouble with....Thanks  MFLS-v1.1.zip
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36588493
cekendricks,

for Fields used as Telephone numbers, Zip Codes are normally set as TEXT data type.

during your first import, set those fields as TEXT.

your next import / append to existing table will be executed correctly..
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36588535
this db imported the Orders.txt with no errors
MFLS-v1.1.mdb
0
 
LVL 19

Accepted Solution

by:
Eric Sherman earned 250 total points
ID: 36588612
cekendricks,

As mentioned earlier .... You need to change the BillingPhone (and other fields) to Text in your Orders destination table.

See pic below.

ET
Import1.PNG
0
 

Author Comment

by:cekendricks
ID: 36589180
I truly appreciate all of the suggestions that have been provided concerning this problem, but believe me when I say that all had already been tried before I opened the thread.

The last version of the database that I sent was only the latest incarnation of the tblOders table.  I have tried it with the BillingsPhone set to text, decimal, and double as well.

When I build the table via the Import wizard I do set the BillingPhone data type to text (as well as zipcodes and others that make sense).  However I can see by the resultant tables (see Orders_ImportErrors, and tblOrders), that when I try to Append the Text file to the same table that Access interprets the BillingPhone field as a Long Integer, and I still fail to see how to tell  the Wizard to change that field to a text field 'When I am using the Wizard to Append'.

I am attaching the database again with the correct data types for the BillingPhone field, and the data appended.

Perhaps it should be noted that I am deleting all of the data in the table after the table is created by the initial import and then Appending the same data to the empty table but I have tried it without deleting the data also...same results  MFLS-v1.1.mdb
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36589280
ok, just tried it, and imported the text file with no problem (5x)

if you can access another pc, try the importing there..


what access version are you using?

is it updated with the latest SP? , if it is, i will do an uninstall of office from the Control Panel

and do a complete install (run all from computer)


0
 

Author Comment

by:cekendricks
ID: 36589349
That's amazing Capricorn...when you say you imported successfully, are you referring to using the Wizard to Append the data to an already existing tblOrders?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36589401
yes...
0
 

Author Comment

by:cekendricks
ID: 36589440
Ok to answer your question regarding my version of Access, I using Access 2010, but I'm not sure about the Service Pack revision for Access (if there is one).  My OS is windows 7 Untimate N; Service Pack 1
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36589463
0
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 250 total points
ID: 36590854
cekendricks,

using A2010,
in the first import, set the BillingZip, BillingPhone and ShippingZip to Text from import wizard window

save the Import Step.

on your next import

External Data > Saved Imports , locate the saved Import, select then click Run


0
 

Author Closing Comment

by:cekendricks
ID: 36711928
Dispite all of the patient and well meaning assistance I received on this particular problem, I was never able to get the file imported without errors.  Time (or a lack thereof), and other pressures forced me to just have the client reformat the BillingPhone field in such a way that it would be interpreted as a text field  (xxx-xxx-xxxx)
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question