Solved

Persistant Import errors

Posted on 2011-09-22
19
206 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
 
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 119

Expert Comment

by:Rey Obrero
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
ID: 36589463
0
 
LVL 119

Assisted Solution

by:Rey Obrero
Rey Obrero 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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
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…
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…

706 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now