[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 216
  • Last Modified:

Persistant Import errors

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
cekendricks
Asked:
cekendricks
  • 7
  • 6
  • 5
  • +1
2 Solutions
 
Eric ShermanAccountant/DeveloperCommented:
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
 
Eric ShermanAccountant/DeveloperCommented:
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
 
cekendricksAuthor Commented:
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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
Eric ShermanAccountant/DeveloperCommented:
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
 
cekendricksAuthor Commented:
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
 
Jeffrey CoachmanCommented:
Make sure you don't have any validation rule, Format, Inputmask, ...etc., set in the table that only wants to see a "2"
0
 
Eric ShermanAccountant/DeveloperCommented:
<<<<<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
 
cekendricksAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
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
 
Rey Obrero (Capricorn1)Commented:
this db imported the Orders.txt with no errors
MFLS-v1.1.mdb
0
 
Eric ShermanAccountant/DeveloperCommented:
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
 
cekendricksAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
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
 
cekendricksAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
yes...
0
 
cekendricksAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
0
 
Rey Obrero (Capricorn1)Commented:
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
 
cekendricksAuthor Commented:
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

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

  • 7
  • 6
  • 5
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now