When importing from a csv file, I have a field that is a raw phone number.  10 digits no spaces or special characters.  No mater what I do this will not import.  If I only have 9 digits, it imports fine.  Add the extra digit, and error returns!  Very frustrated!!!  What is the issue.  Can anyone help with this?

Rocky

Hi,

When I tried in my PC, I didnt meet this issue. And could you please give me what the error message is?

And what version of Office you have?

Try to open the Access in safe mode to check the issue.

http://social.technet.microsoft.com/Forums/en-US/excel/thread/cd010692-aac1-4ced-9bfa-37ff85dc3e2f

Jaynet,

Office 2010

Access version 14.0.6112.5000 (32-bit)

I tried the import in safe mode and received the same error;

My data set:

Purchase Order Number,PO Date,Vendor #,Pupose Code,Currency,BillName,BillAddress ,BillCity,BillState,BillZip,Name,ShipAddress ,ShipCity,ShipState,ShipZip,FOB,SCAC Code,Routing,Terms Type code,Terms basic date code,Terms net days,3P,Divison Number,Information contact,Telephone,Information contact,Telephone,Email Address,Reseller/Customer name,ResellerAddress,ResellerCity,ResellerState,ResellerZip,ResellerCountry,Information contact,Telephone,Information contact,Telephone,Email Address,Requested Ship date,Message field,Line item,Quanity,Unit of measure,Ingram part number,Manufacturer part #,Product Description,Media,Unit cost,Extended cost,Message Text
60N3821D,20120119,U567,Original,U.S. Dollars,Ingram Micro,1600 E. St Andrew Place,Santa Ana,CA,92799,Lela,1759 Wehrle Drive,Willamsville,NY,14221,TP Destination,ABFS,ABF Freight,14,,45,Collect,U567,Angelina Gutierrez,7145661001,Ian Olsen,7145661001,ian.olsen@ingrammicro.com,,,,,,,Ian Olsen,7145661001,Ian Olsen,7145661001,ian.olsen@ingrammicro.com,20100220,Message,LineItem1,1,EA,CH3521,ManfProd1,ProdDesc1,,1.55,1.55,Message1
60N3821D,20120119,U567,Original,U.S. Dollars,Ingram Micro,1600 E. St Andrew Place,Santa Ana,CA,92799,Lela,1759 Wehrle Drive,Willamsville,NY,14221,TP Destination,ABFS,ABF Freight,14,,45,Collect,U567,Angelina Gutierrez,7145661000,Ian Olsen,7145661000,ian.olsen@ingrammicro.com,,,,,,,Ian Olsen,7145661000,Ian Olsen,7145661000,ian.olsen@ingrammicro.com,20100220,Message,LineItem2,1,EA,CH3522,ManfProd2,ProdDesc2,,2.56,2.56,Message2

My error results:

<tfoot></tfoot>

EDI 850 sample file in drop box_ImportErrors

Error	Field	Row
Type Conversion Failure	Telephone	1
Type Conversion Failure	Field27	1
Type Conversion Failure	Field36	1
Type Conversion Failure	Field38	1
Type Conversion Failure	Telephone	2
Type Conversion Failure	Field27	2
Type Conversion Failure	Field36	2
Type Conversion Failure	Field38	2

Okay.....  I have figured out what Access is doing, now I just need to know how to stop it!!

When running the wizard and importing into an existing data base, Access is "automatically" choosing the field types for me, and then converting to my designated file type.  In the fields that I am having issues with, it is choosing "Long Integer," which will not convert correctly.  I need Access to import this field as "text".  How do I get Access to import the fields with my field type designation?

Also, if I convert this file to Excel and then import it, it works like a charm.  

What is the issue with import from csv?  HELP!

Hi,

We can change the data type when we import the .csv file.

See the picture below:

Select the field, then choose the data type above. The default data type is Long Integer, change it to text to check the issue.

You only get this screen if you do an import to a new db.  I need to update and append to an existing db.



Once you go past you get the next screen where you do not have these options to change the "type"

The advanced button also does not give you any usable options.  At this point this looks like a bug in Microsoft's software.

Anyone out there have a solution or a workaround?  PLEASE!

Hi,

Since you choose Append a copy of the records to the table, this option means that the file data will be imported to a exists table.  So the data type has defined in the table. Find the table you import into, change the data type then check the issue.

If the table is not exists, then please try to choose the first option Import the source data into a new table in the current database, in this option, there is Data type settings in my last reply.

I am not sure how to explain so that you understand.  I am importing data from a .csv file to an existing database.  I have all the fields in the table set correctly.   When I import this, Excel decides to use "long integer" as the type on several of the fields.  This does not import into the table.  It returns the type file conversion error that I have listed earlier.  I have duplicated this issue on no less than 4 computers all with the same result.  How can we fix this, or who can I contact to find out why it is happening?

Rocky

Hi,

Based on my research, Access is having trouble putting the data into the fields because the field type does not match.

For example, if you have a Number or Date field, and the data you are importing contains:
   -
   Unknown
   N/A
these are not valid numbers or dates, so produce a "type conversion" error.

So check the data in the csv file which you want to import. Second, you mean before import, the table of phone column has already set as Text data type? And then after import, the data type changed into Long Integer? When I tried this in my Access 2010, this issue cannot be reproduced.

Then try to import the data in a mew table, use Import the source data into a new table in the current database to do that, if this method doesnt meet the issue, then copy the data which in your original table and paste into the table you import.

I am sorry that I am not being clear.  Maybe there is someone else that can help with the issue.  I will do this step by step.  If you follow with me, you will see the same issues on your computer.  I have reproduced this on 6 different computers now.  This is an issue with Access.

1. import csv file to new Access blank database letting the file create the table.

Please use this file so we are using the same data. csvfile  (right click - save link as)

2. Import as text file "Delimited" - click next

3. Check "First Row Contains Field Names and choose OK to the dialog box - Click next

4. Choose fields "Telephone, Field27, Field 36, Field38" and set the data type to "Text" - Click next twice

5. click Finish

You will see this action works correctly.

View the table you just imported in design view, and you will see that the data types for the above fields are also correct.

NOW!!!

1. Delete the two rows that were imported

2. Choose "External Data" & "Text file"

3. Choose the same file BUT choose "Append a copy of the records to the table" - Click OK

4. Choose "Delimited" - Click next

5. Check "First Row Contains Field Names and choose OK to the dialog box - Click next

6. You have no other options here but to choose finish

7. You will now see the errors from the attempted import.

This is because the import is "auto" selecting "long integer"

Long integer will not import anything longer than 9 digits.  The fields need to import as text!

Please, Please - follow these directions, and you will see that the import from a text file has a bug.  I need it fixed and I need a work around.  I am trying to automate a process and I cannot do it without this working correctly.

Rocky

Hi,

Follow the steps you give, I can reproduce the issue, then heres a method to try to avoid this issue:

Do the same above the steps Choose the same file BUT choose "Append a copy of the records to the table" - Click OK you listed in your last reply.

Choose "Delimited" - Click next

Check "First Row Contains Field Names and choose OK to the dialog box

Click Advanced button in the left bottom corner.

In the Code Page: part, choose Western European (Windows) option.

Then do the steps left as usual.

Yea!!!  That works.  Now I can continue.  Thanks for hanging in there with me!

Hello,

I have got the same problem as described above, however setting encoding to Western Windows does not solve the problem. I also tried other encodings but withouth any success.

Thanks, Martin Kopecek

I have the same problem over and over and over.. and I cant find a solution for it.

My source file can be a *txt or a *csv - doesnt matter.

My target table is set up with all fields as "text" and Column Heading.

I tried to set Unicode UTF8, Wester European or whatever .. doesnt make a difference.. I just get these annoying ""Type Conversion Failure"

The problem seems to come from the fact that even though my target is clearly set as "txt" Access sees the first 1000 records in the source file and sees "1", "2", etc.. which make up 90% of all the records and decides on its own to convert to number.
Then when some records have "A" in them.. it fails..

it is so annoying and i would be so happy if someone could propose a fix or workaround for that!

Hi,

I followed the instruction described above with Advaced... option, but I changed field type directly in the Advanced window to Double. Than my 12digid numbers were imported correctly (import form txt).

Regards, Viktor.