inputting phone number problem

[CD Tom]

I'm inputting from an .csv file that has a column for phone. the problem is that the phone number is entered two different ways. (555) 123-4567 and 5551234567 I'm loading the .csv file into a table that has the phone set as Short Text, field size 20 and input Mask !\(999") "000\-0000;0;_
When the input is finished the phone numbers with the (555) 123-4567 doesn't input but the 5551234567 does and is formatted like the mask.
I'm using the following code to load the table.

Docmd.transfertext acimportDelim, , TempFile, Appath, true

Where TempFile is name of the database file and Appath is the location and name of the .csv file.
I'm getting an ImportErrors file that says the phone number with the (555) 123-4567 Type Conversion Failure. Now I have a AltPhone in the same .csv file with the same two different phone formats and that loads just fine. I've checked the .csv file format and they are both the same. Also the database table that is loaded the two columns are the same. So why does one load and the other doesn't. I'm really confused.

Any help is appreciated.
Thanks

[twinnyfo]

You'll have to rip the pre-formetted string apart (as it is a string and NOT a number).

You could build a string checking each character if it is a numeric value and adding it to your final results, and then use that result to fill the final table.

[CD Tom]

I don't understand why it works with the AltPhone which has the same two types of numbers and it loads just fine. As I mentioned I've checked the two columns both in the .csv file and in the database table and they are both the same. So why one and not the other.

[twinnyfo]

I just reread your original post. So, BOTH receiving fields are set the same?

Have you tried a copy and paste of the one field that works for the field that does not?

Just trying to throw out possible ideas.

In the original source table for Alt Phone, before it is exported to CSV, is it set up identically to the other phone field?

[CD Tom]

I've check all fields in both the xlsx file and the converted .csv file and everything is the same.
I'll try doing the copy and paste and see if it makes any difference.

[twinnyfo]

Still very strange. You may have to resolve it my ripping apart the phone number string.....