?
Solved

Persistant Import errors

Posted on 2011-09-22
19
Medium Priority
?
213 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
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 750 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 750 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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

770 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